how to pass a query hint with pg_hintplan to a query originating from hibernate

The article shows how to pass a query hint using the hintplan extension when the query is getting generated by hibernate. The query hints are passed to the optimiser for choosing a more optimised plan for the query.

Hibernate doesn’t provide a direct method to resolve the issue. As a solution, we can use the Hibernate StatementInspector to intercept and modify auto-generated SQL statements i.e.

public class HintStatementInspector implements StatementInspector {
    @Override
    public String inspect(String sql) {
        // check if pg_plan_hint hint is available
        if (sql.startsWith("/* /\\*+")) {
            // fix first part of the hint comment
            sql = sql.replaceFirst(Pattern.quote("/* /\\*+"), Matcher.quoteReplacement("/*+"));
            // fix last part of the hint comment
            sql = sql.replaceFirst(Pattern.quote("*\\/ */"), Matcher.quoteReplacement("*/"));
        }
        return sql;
    }
}

hibernate.cfg.xml

 <property name="hibernate.use_sql_comments">true</property>
 <property name="hibernate.session_factory.statement_inspector">com.pgedge.example.HintStatementInspector</property>

Try an example query

    String hql = "select id, name from City where id = 170505378935607296";
    // add pg_plan_hint hint, you may adopt different marks as well and process them in `HintStatementInspector` as per convenience 
    Query query = session.createQuery(hql).setComment("/*+ SeqScan(c1_0) */");
    List results = query.getResultList();

You can see that hint is being passed to PostgreSQL query optimiser i.e.

PostgreSQL log

2024-06-27 10:44:56.722 PKT [PostgreSQL JDBC Driver, asif, postgres , 3397633] LOG:  execute <unnamed>: /*+ SeqScan(c1_0) */ select c1_0.id,c1_0.name from public.City c1_0 where c1_0.id=170505378935607296