都2025了, PG数据库还在用PLAN HINT?

用户4035096

发布于 2026-07-09 19:36:35

150

pg_hint_plan 插件支持 PG 18

什么, 都2025了, 还要用HINT?

没办法啊, 数据库SQL优化器太难做了, 虽然PG已经算开源翘楚了, 但是估计某些硬骨头连Oracle都还在用HINT吧?

最常见的场景是每天的报表运算过程, 最容易出现SQL计划不准确, 因为基表数据经常大批量变化, 统计信息在中间过程被刷新. 或者是比较复杂的SQL计划不准确的问题(这个很多论文有探讨, 例如不同字段条件被认为是独立事件, 导致行数估算不准. 另外是多表JOIN的顺序问题、采用什么JOIN方法等!).

pg_hint_plan 插件支持 PG 18, 除了版本升级到18, 另外还增加了禁用指定表指定索引(全部、或少部分、或正则匹配到的索引名)的hint.

Using `DisableIndex` hint

A DisableIndex hint excludes the specified indexes from being considered during query planning. It takes precedence over other hints. A disabled index will not be used, even if explicitly requested by IndexScan.

=# /*+DisableIndex(t t_c1) IndexScan(t t_c1) */  
   EXPLAINSELECT * FROM t WHERE c1 = 1;  
LOG:  indexes disabled for DisableIndex(t): t_c1  
LOG:  available indexes for IndexScan(t):  
LOG:  pg_hint_plan:  
used hint:  
DisableIndex(t t_c1)  
not used hint:  
IndexScan(t t_c1)  
duplication hint:  
error hint:  

                           QUERY PLAN  
-----------------------------------------------------------------  
 Index Scan using t_pkey on t  (cost=0.15..8.17 rows=1 width=12)  
   Index Cond: (c1 = 1)  
(2 rows)

代码patch如下:

https://github.com/ossc-db/pg_hint_plan/commit/7ba3532a9245a12d8271e1909f3715c40df7c5d1

Add new hint DisableIndex  
This adds a new hint called "DisableIndex", which is able to prevent one  
or more specified indexes from being considered by the planner.  The  
hint's grammar is as follows:  
/*+ DisableIndex(table index...) */  
  
A table name and at least one index are required.  
  
This hint's implementation relies on the get_relation_info hook, which  
is processed before any other hint, meaning that it takes priority over  
other hint types, like scan methods or parallel.  During its processing,  
any index matched by the hint, whether through the base table or the  
parent table (as in a partitioned table), will be removed from the  
RelOptInfo's index list, making it unavailable to the planner.  
  
For example, if this hint is used alongside an `IndexScan` hint that  
refers to the same index, the `DisableIndex` hint will take precedence.  

Tests and documentation are added.  This feature builds upon the recent  
refactoring pieces done in ec55c0c, 1c62ca5, e046768,  
785023c and e2de313.  

A couple of extra things could be done with this new hint, which are  
left for future work, if these are asked for:  
- Possibility to use a regexp with the index list.  
- No indexes defined, meaning that all indexes of a relation are  
disabled.  

Per issue #226.  
Author: Sami Imseih <simseih@amazon.com>  
Backpatch-through: 18

Hint list

The available hints are listed below.

Group	Format	Description
Scan method	SeqScan(table)	Forces sequential scan on the table.
	TidScan(table)	Forces TID scan on the table.
	IndexScan(table[ index...])	Forces index scan on the table. Restricts to specified indexes if any.
	IndexOnlyScan(table[ index...])	Forces index-only scan on the table. Restricts to specified indexes if any. Index scan may be used if index-only scan is not available.
	BitmapScan(table[ index...])	Forces bitmap scan on the table. Restricts to specified indexes if any.
	IndexScanRegexp(table[ POSIX Regexp...])IndexOnlyScanRegexp(table[ POSIX Regexp...])BitmapScanRegexp(table[ POSIX Regexp...])	Forces index scan, index-only scan (For PostgreSQL 9.2 and later) or bitmap scan on the table. Restricts to indexes that matches the specified POSIX regular expression pattern.
	NoSeqScan(table)	Forces to not do sequential scan on the table.
	NoTidScan(table)	Forces to not do TID scan on the table.
	NoIndexScan(table)	Forces to not do index scan and index-only scan on the table.
	NoIndexOnlyScan(table)	Forces to not do index only scan on the table.
	NoBitmapScan(table)	Forces to not do bitmap scan on the table.
Disable indexes	DisableIndex(table index...)	Disables the specified indexes during query planning, taking precedence over any other hints.
Join method	NestLoop(table table[ table...])	Forces nested loop for the joins on the tables specified.
	HashJoin(table table[ table...])	Forces hash join for the joins on the tables specified.
	MergeJoin(table table[ table...])	Forces merge join for the joins on the tables specified.
	NoNestLoop(table table[ table...])	Forces to not do nested loop for the joins on the tables specified.
	NoHashJoin(table table[ table...])	Forces to not do hash join for the joins on the tables specified.
	NoMergeJoin(table table[ table...])	Forces to not do merge join for the joins on the tables specified.
Join order	Leading(table table[ table...])	Forces join order as specified.
	Leading(<join pair>)	Forces join order and directions as specified. A join pair is a pair of tables and/or other join pairs enclosed by parentheses, which can make a nested structure.
Behavior control on Join	Memoize(table table[ table...])	Allows the topmost join of a join among the specified tables to Memoize the inner result. Not enforced.
	NoMemoize(table table[ table...])	Inhibits the topmost join of a join among the specified tables from Memoizing the inner result.
Row number correction	Rows(table table[ table...] correction)	Corrects row number of a result of the joins on the tables specified. The available correction methods are absolute (#), addition (+), subtract (-) and multiplication (*).should be a string that strtod() can understand.
Parallel query configuration	Parallel(table <# of workers> [soft\|hard])	Enforces or inhibits parallel execution of the specified table. <# of workers> is the desired number of parallel workers, where zero means inhibiting parallel execution. If the third parameter is soft (default), it just changes max_parallel_workers_per_gather and leaves everything else to the planner. Hard enforces the specified number of workers.
GUC	Set(GUC-param value)	Sets GUC parameter to the value defined while planner is running.