PostgreSQL SQL Patch功能介绍

原创彭冲 2024-10-29

1069

有Oracle背景的大佬都知道当生产系统数据库性能问题归一到SQL走错执行计划，而无法及时修改业务应用代码时可针对某类SQL进行执行计划绑定。

SQL Patch功能是指在发现查询语句的执行计划、执行方式未达预期的场景下，避免直接修改业务应用语句的前提下使用Hint对查询计划进行调优。

1.插件pg_hint_plan简介

PG里对执行计划进行干预有两种常见的方式，第一种是通过enable类优化器参数进行调节：

postgres=# \dconfig enable*
    List of configuration parameters
           Parameter            | Value 
--------------------------------+------
 enable_async_append            | on
 enable_bitmapscan              | on
 enable_gathermerge             | on
 enable_group_by_reordering     | on
 enable_hashagg                 | on
 enable_hashjoin                | on
 enable_incremental_sort        | on
 enable_indexonlyscan           | on
 enable_indexscan               | on
 enable_material                | on
 enable_memoize                 | on
 enable_mergejoin               | on
 enable_nestloop                | on
 enable_parallel_append         | on
 enable_parallel_hash           | on
 enable_partition_pruning       | on
 enable_partitionwise_aggregate | off
 enable_partitionwise_join      | off
 enable_presorted_aggregate     | on
 enable_seqscan                 | on
 enable_sort                    | on
 enable_tidscan                 | on
(22 rows)

截止当前共有22个，这些参数的默认行为如果发生变化，执行计划打开settings选项可清晰的定位，演示如下：

postgres=# set enable_seqscan = 'off';
SET

postgres=# explain (settings,costs off) 
           select * from tab where id=1;
               QUERY PLAN                
----------------------------------------
 Index Only Scan using tab_id_idx on tab
   Index Cond: (id = 1)
 Settings: enable_seqscan = 'off'
(3 rows)

第二种方式是使用pg_hint_plan插件的hint功能来实现固定SQL的执行计划。

不过PG社区一直认为使用hint机制干预优化器可能会带来维护麻烦、升级失效等问题。因此社区还未计划将hint功能加入到PG内核中。

pg_hint_plan支持如下七类hint：

Ⅰ.扫描方式

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…])

Ⅱ.连接方式

NestLoop(table table [table…])
HashJoin(table table [table…])
MergeJoin(table table [table…])

Ⅲ.连接顺序

Leading(table table [table…])

Ⅳ.行为控制

Memoize

Ⅴ.估算行修正

Rows

Ⅵ.并行查询

Parallel(table <# of workers> [soft|hard])

Ⅶ.GUC设置

Set(GUC-param value)

2.pg_hint_plan安装调试

pg_hint_plan插件与PG的版本参照如下：

pg_hint_plan10 1.3.7
pg_hint_plan11 1.3.9
pg_hint_plan12 1.3.10
pg_hint_plan13 1.3.10
pg_hint_plan14 1.4.3
pg_hint_plan15 1.5.2
pg_hint_plan16 1.6.1
pg_hint_plan17 1.7.0

例如本文测试环境使用PG 17，则下载pg_hint_plan17 1.7.0这个版本，源码编译过程如下：

$ tar xzvf pg_hint_plan-REL17_1_7_0.tar.gz 
$ cd pg_hint_plan-REL17_1_7_0
$ make 
$ make install

pg_hint_plan插件动态库建议配置为user或db级别：

alter user postgres in database postgres 
    set session_preload_libraries='pg_hint_plan';

alter database postgres 
    set session_preload_libraries='pg_hint_plan';

3.测试SQL Patch

使用SQL Patch功能需要设置pg_hint_plan.enable_hint_table为on，通过hints表把SQL语句和需要绑定的hint进行注册。

为了便于从log文件观测SQL语句的执行计划，同时使用auto_explain插件

LOAD 'auto_explain';
SET auto_explain.log_min_duration =0;
SET pg_hint_plan.enable_hint_table = on;

CREATE TABLE tab(id int);
CREATE INDEX on tab(id);
INSERT INTO TAB SELECT generate_series(1,100000);

下面使用EXPLAIN查看执行计划，并获得语句的查询ID值

EXPLAIN (verbose, costs false) 
    SELECT * FROM tab WHERE tab.id = 1;

                   QUERY PLAN                   
------------------------------------------------
 Index Only Scan using tab_id_idx on public.tab
   Output: id
   Index Cond: (tab.id = 1)
 Query Identifier: 1328995833345847842
(4 rows)

通过hints表创建Patch，绑定注册hint，走全表扫描

INSERT INTO hint_plan.hints(query_id, application_name, hints)
     VALUES (1328995833345847842, 'psql', 'SeqScan(tab)');

通过EXPLAIN进行验证，或者观测log日志里的执行计划，均可发现按预期走全表扫描

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM tab WHERE tab.id = 1;
              QUERY PLAN               
---------------------------------------
 Seq Scan on public.tab
   Output: id
   Filter: (tab.id = 1)
 Query Identifier: 1328995833345847842
(4 rows)

修改hints表，更新hint，走索引扫描

UPDATE hint_plan.hints
     SET hints = 'IndexScan(tab)'
     WHERE id = 1;

再次通过EXPLAIN进行观测，执行计划走索引扫描，符合预期

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM tab WHERE tab.id = 1;
 Index Scan using tab_id_idx on public.tab
   Output: id
   Index Cond: (tab.id = 1)
 Query Identifier: 1328995833345847842
(4 rows)

接下来是第二个测试场景

CREATE TABLE t1(name char(10),id int);
CREATE TABLE t2(name char(10),id int);

INSERT INTO t1 (name, id)
SELECT 'data_'|| generate_series(1, 1000), generate_series(1, 1000);

INSERT INTO t2 (name, id)
SELECT 'data_'|| generate_series(1, 1000), generate_series(1, 1000);

CREATE INDEX idx_t1 ON t1 (id);
CREATE INDEX idx_t2 ON t2 (id);

EXPLAIN (verbose, costs false) 
    SELECT * FROM t1 a, t2 b WHERE a.id = b.id;
               QUERY PLAN               
----------------------------------------
 Hash Join
   Output: a.name, a.id, b.name, b.id
   Hash Cond: (a.id = b.id)
   ->  Seq Scan on public.t1 a
         Output: a.name, a.id
   ->  Hash
         Output: b.name, b.id
         ->  Seq Scan on public.t2 b
               Output: b.name, b.id
 Query Identifier: -4764431155899173354
(10 rows)

走的全表扫描hash join执行计划

通过SQL Patch绑定执行计划

INSERT INTO hint_plan.hints(query_id, application_name, hints)
     VALUES (-4764431155899173354, 'psql', 'IndexScan(a)');

EXPLAIN (verbose, costs false) 
    SELECT * FROM t1 a, t2 b WHERE a.id = b.id;
                  QUERY PLAN                  
----------------------------------------------
 Hash Join
   Output: a.name, a.id, b.name, b.id
   Hash Cond: (a.id = b.id)
   ->  Index Scan using idx_t1 on public.t1 a
         Output: a.name, a.id
   ->  Hash
         Output: b.name, b.id
         ->  Seq Scan on public.t2 b
               Output: b.name, b.id
 Query Identifier: -4764431155899173354
(10 rows)

t1表可以看到走了索引扫描，Patch生效，通过log文件也可观测到相同的执行计划。

查看当前已定义的SQL Patch：

TABLE hint_plan.hints;

 id |       query_id       | application_name |     hints      
----+----------------------+------------------+---------------
  1 |  1328995833345847842 | psql             | IndexScan(tab)
  2 | -4764431155899173354 | psql             | IndexScan(a)
(2 rows)

关闭SQL Patch:

Ⅰ.根据查询ID删除hints表记录

DELETE FROM hint_plan.hints WHERE query_id = -4764431155899173354 ;

Ⅱ.关闭pg_hint_plan.enable_hint_table

SET pg_hint_plan.enable_hint_table = off;

4.知识总结

pg_hint_plan插件目前支持七类hint：例如表的扫描方式、连接方式、连接顺序、行为控制、COST修正、并行查询、GUC参数设置(22个优化器参数)，这些都可以通过SQL Patch方式绑定执行计划。

参考链接

https://github.com/ossc-db/pg_hint_plan

墨力计划 postgresql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者