Pg_hint_plan tweaks planner decisions with user's hints. PostgreSQL executes queries using cost-based optimizer, and sometimes fails to choose most efficient execution plan for technical limitations.

In such cases, pg_hint_plan helps users to guide the planner to choose the plans they want by putting some instructions - call them hints - in the comments prefixed to the SQL statement body. No need to rewrite statement itself nor change some GUC parameters elsewhere.

Functional limitations

Pg_hint_plan's major Functional limitations below.

Hints cannot be used also in ECPG because the comments are not passed to the server.
psql attaches DECLARE statement to the top of existing query string when the FETCH_COUNT variable is a positive value . So, the hints seems dissapearing for pg_hint_plan.



PostgreSQLはコストベースオプティマイザを採用しています。 オプティマイザは可能な限りよい実行計画を作成使用としますが、カラム間の相関関係などは考慮しないため、 複雑なクエリでは常に最適なプランが選択されるとは限りません。

pg_hint_planを用いると、ヒントを記述したブロックコメントをSQL文の前に加えることで、 SQL文やGUCパラメータを変更することなく実行計画を制御することができます。



  1. /*+
  2. HashJoin(a b)
  3. SeqScan(a)
  4. */
  6. FROM pgbench_branches b
  7. JOIN pgbench_accounts a ON =
  8. ORDER BY a.aid;
  10. ---------------------------------------------------------------------------------------
  11. Sort (cost=31465.84..31715.84 rows=100000 width=197)
  12. Sort Key: a.aid
  13. -> Hash Join (cost=1.02..4016.02 rows=100000 width=197)
  14. Hash Cond: ( =
  15. -> Seq Scan on pgbench_accounts a (cost=0.00..2640.00 rows=100000 width=97)
  16. -> Hash (cost=1.01..1.01 rows=1 width=100)
  17. -> Seq Scan on pgbench_branches b (cost=0.00..1.01 rows=1 width=100)
  18. (7 rows)

Recent Tickets

add rewrite to pg_hint_plan2023-01-31 18:20
Hi Team, pg_hint_plan is cool. thanks a lot. how much work is it to add/extend query rewrite to it please? thanks a...(None)
binary for version 14.22023-01-31 18:14
Please provide pg_hint_plan binary for PG version 14.2 . thanks(None)
Hints appear to be ignored when PostgreSQL chooses a generic plan when executed from JDBC PreparedStatements.2022-12-19 14:15
Hints appear to be ignored when PostgreSQL chooses a generic plan when executed from JDBC PreparedStatements. Check a...(None)
Crash from illegal parallel bitmap scan plan in REL10_1_3_3 when index name is incorrect2019-06-24 23:52
Query crashes with the following stack using pg_hint_plan with REL10_1_3_3 when an incorrect index name is supplied d...(None)
undefined symbol: RINFO_IS_PUSHED_DOWN2018-09-18 23:43
postgres=# load 'pg_hint_plan'; ERROR: could not load library "/usr/pgsql-10/lib/": /usr/pg...(None)