pg_hint_plan: Control PostgreSQL Execution Plans

Introduction to pg_hint_plan

pg_hint_plan is an essential tool for PostgreSQL users who need more control over their database execution plans. By default, PostgreSQL’s query planner attempts to generate the most efficient execution plan based on statistics. However, it may not always make the best decision, especially when statistics are stale or unstable. In critical environments where query performance stability is vital, pg_hint_plan allows developers and DBAs to manually influence these plans by providing "hints" to the planner.

How to Install and Setup pg_hint_plan

Setting up pg_hint_plan is straightforward. Here’s how you can get started:

1. First, install the pg_hint_plan extension from in your PostgreSQL instance, enable the extension:

   CREATE EXTENSION pg_hint_plan;

2. Update your PostgreSQL configuration file (postgresql.conf) by adding pg_hint_plan to the shared_preload_libraries parameter:

   shared_preload_libraries = 'pg_hint_plan'

3. Restart your PostgreSQL server to apply the changes.

Using pg_hint_plan to Control Execution Plans

After installing pg_hint_plan, you can use it to influence the behavior of PostgreSQL’s planner. This is done by embedding hint phrases in SQL queries. Hint phrases direct the planner to choose specific scan methods, join orders, or even prohibit certain operations like parallel queries.

Types of Hint Phrases in pg_hint_plan

pg_hint_plan supports various types of hints. Here are the most commonly used hints:

• Scan Methods: Influence how tables are scanned.

  SeqScan(table), IndexScan(table [index…])

• Join Methods: Control the join strategy.

  NestLoop(table1, table2), HashJoin(table1, table2)

• Join Orders: Specify the order in which tables are joined.

  Leading(table1, table2)

• Parallel Queries: Enable or disable parallel query execution.

  Parallel(table, numberOfWorkers)

• Set Parameters: Override PostgreSQL parameters for a specific query.

  Set(parameter = value)

pg_hint_plan Examples

Here are some practical examples of how to use pg_hint_plan:

Example 1: Using Bitmap Index Scan

WITH /*+ BitmapIndexScan (emp emp_salary_index) */
                highSalary AS (SELECT * FROM emp WHERE salary > 80000)
             SELECT * FROM highSalary JOIN dept ON highSalary.deptno = dept.deptno;

            /* Execution Plan:
               Bitmap Heap Scan on highSalary  (cost=20.00..60.00 rows=500 width=200)
                 Recheck Cond: (deptno = dept.deptno)
                 ->  Bitmap Index Scan on emp_salary_index  (cost=0.00..20.00 rows=500)
               Seq Scan on dept  (cost=0.00..10.00 rows=500 width=100)
            */
            

In this query, we force PostgreSQL to use a Bitmap Index Scan on the emp_salary_index.

Example 2: Forcing Hash Join

WITH /*+ HashJoin(emp dept) */
               recentHires AS (SELECT * FROM emp WHERE hire_date >= '2022-01-01')
             SELECT * FROM recentHires, dept WHERE recentHires.deptno = dept.deptno;

            /* Execution Plan:
               Hash Join  (cost=30.00..70.00 rows=1000 width=200)
                 Hash  (cost=15.00..15.00 rows=300 width=100)
                   Seq Scan on dept  (cost=0.00..15.00 rows=300 width=100)
                 Hash  (cost=10.00..30.00 rows=700 width=100)
                   Seq Scan on recentHires  (cost=0.00..30.00 rows=700 width=100)
            */
            

This example forces PostgreSQL to perform a Hash Join between recent hires and the department table.

Notes and Considerations

Although pg_hint_plan gives you control over query execution, it’s essential to use it wisely. Overuse of hints can degrade performance if not carefully considered. Make sure to test your queries thoroughly when introducing hints to ensure they truly optimize performance.

Conclusion

pg_hint_plan is a powerful tool for managing PostgreSQL execution plans. It provides developers and database administrators the ability to guide the query planner when its default behavior does not yield the desired results. By using various hint phrases, you can optimize query performance and ensure stability in mission-critical environments.

Rate Your Experience