Optimize PostgreSQL Query with pg_hint_plan

 Query optimization in PostgreSQL can sometimes benefit from manual intervention. The pg_hint_plan extension provides developers with the ability to influence the query planner's decisions. Let's explore this powerful tool through a practical example.

Setting Up the Test Environment

First, let's create a simple test scenario with a table containing an integer primary key and a text column:

create table t(i int primary key, c text);
insert into t select x,rpad('x',100,'x') from generate_series(1,10) x;
vacuum analyze t;

Default Query Behavior

Without any hints, let's examine how PostgreSQL handles a simple query:

explain analyze select * from t where i=1;

postgres=# explain analyze select * from t where i=1;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1.12 rows=1 width=105) (actual time=0.023..0.033 rows=1 loops=1)
   Filter: (i = 1)
   Rows Removed by Filter: 9
 Planning Time: 0.155 ms
 Execution Time: 0.065 ms
(5 rows)

The planner chose a Sequential Scan despite having a primary key index available. This decision was made because:

The table is small (only 10 rows)

The cost of reading the entire table (cost=0.00..1.12) was deemed cheaper than using the index

Implementing pg_hint_plan

-- Create extension is not mandatory, load the library 'pg_hint_plan' is mandatory.
postgres=# create extension pg_hint_plan;
CREATE EXTENSION

After installing the pg_hint_plan extension, we can influence the query plan. There are two ways to specify hints with EXPLAIN:

1. Hint before EXPLAIN:

postgres=# explain analyze /*+ IndexScan(t) */ select * from t where i=1;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Index Scan using t_pkey on t  (cost=0.14..8.15 rows=1 width=105) (actual time=0.065..0.067 rows=1 loops=1)
   Index Cond: (i = 1)
 Planning Time: 0.311 ms
 Execution Time: 0.142 ms
(4 rows)

2. Hint after EXPLAIN:

postgres=# /*+ IndexScan(t) */ explain analyze select * from t where i=1;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Index Scan using t_pkey on t  (cost=0.14..8.15 rows=1 width=105) (actual time=0.017..0.018 rows=1 loops=1)
   Index Cond: (i = 1)
 Planning Time: 0.104 ms
 Execution Time: 0.032 ms
(4 rows)

Both methods successfully forced an Index Scan using the primary key (t_pkey), though with slightly different execution times.

Using the Hint Table

It works well for suitations you can't modify the SQL to add the hint.

1. Enable the hint table feature:

postgres=# set pg_hint_plan.enable_hint_table=on;
SET

2. Get query identifier, store the hint for it:

postgres=# explain (verbose) select * from t where i=1;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on public.t  (cost=0.00..1.12 rows=1 width=105)
   Output: i, c
   Filter: (t.i = 1)
 Query Identifier: -3099684296572641299
(4 rows)

postgres=# insert into hint_plan.hints(query_id, application_name, hints) values ('-3099684296572641299','','IndexScan(t)');
INSERT 0 1

postgres=# explain (verbose) select * from t where i=1;
                               QUERY PLAN
-------------------------------------------------------------------------
 Index Scan using t_pkey on public.t  (cost=0.14..8.15 rows=1 width=105)
   Output: i, c
   Index Cond: (t.i = 1)
 Query Identifier: -3099684296572641299
(4 rows)

After storing the hint, the planner consistently uses the index scan strategy for matching queries.

Debugging Hints

For troubleshooting, pg_hint_plan provides debugging capabilities:

postgres=# set pg_hint_plan.debug_print=on;

2025-01-29 23:28:34.223 +08 [2935] postgres@postgres LOG:  pg_hint_plan:
        used hint:
        IndexScan(t)
        not used hint:
        duplication hint:
        error hint:

2025-01-29 23:28:34.223 +08 [2935] postgres@postgres STATEMENT:  explain (verbose) select * from t where i=1;

Key Takeaways

• pg_hint_plan provides multiple methods to influence query planning
• Hints can be specified inline with queries or stored in a hint table
• Debug mode helps verify hint application