都2025了, PG数据库还在用PLAN HINT?
P
digoal约 5 分钟
digoal约 5 分钟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 <[email protected]>
Backpatch-through: 18
Hint list
The available hints are listed below.
SeqScan(table) | ||
TidScan(table) | ||
IndexScan(table[ index...]) | ||
IndexOnlyScan(table[ index...]) | ||
BitmapScan(table[ index...]) | ||
IndexScanRegexp(table[ POSIX Regexp...])IndexOnlyScanRegexp(table[ POSIX Regexp...])BitmapScanRegexp(table[ POSIX Regexp...]) | ||
NoSeqScan(table) | ||
NoTidScan(table) | ||
NoIndexScan(table) | ||
NoIndexOnlyScan(table) | ||
NoBitmapScan(table) | ||
DisableIndex(table index...) | ||
NestLoop(table table[ table...]) | ||
HashJoin(table table[ table...]) | ||
MergeJoin(table table[ table...]) | ||
NoNestLoop(table table[ table...]) | ||
NoHashJoin(table table[ table...]) | ||
NoMergeJoin(table table[ table...]) | ||
Leading(table table[ table...]) | ||
Leading(<join pair>) | ||
Memoize(table table[ table...]) | ||
NoMemoize(table table[ table...]) | ||
Rows(table table[ table...] correction) | ||
Parallel(table <# of workers> [soft|hard]) | ||
Set(GUC-param value) |
参考
https://github.com/ossc-db/pg_hint_plan/tree/master/docs
https://github.com/ossc-db/pg_hint_plan/commit/7ba3532a9245a12d8271e1909f3715c40df7c5d1