postgresq-自定义执行计划(custom plan)与generic plan(通用执行计划）

已于 2025-04-26 16:05:11 修改
阅读量711 收藏 9
点赞数 8
分类专栏： Postgresql 文章标签： 数据库 postgresql
于 2025-01-01 21:50:33 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_43230594/article/details/144870866
版权

文章目录


之前写过一篇关于 PostgreSQL prepare sql的文章，但当时没有提到generic plan(通用计划)和custom plan(自定义计划)这两个概念。现在将通过举例介绍这两个概念。

创建测试表：

postgres=# create database demo;
CREATE DATABASE
postgres=# \c demo
You are now connected to database "demo" as user "postgres".
demo=# create table t1(a int,b text);
CREATE TABLE
demo=# insert into t1 select i,'aaa' from generate_series(1,100) i;
INSERT 0 100
demo=# insert into t1 select i,'bbb' from generate_series(101,200) i;
INSERT 0 100
demo=# insert into t1 select i,'ccc' from generate_series(201,300) i;
INSERT 0 100

在有了数据之后，可以创建一个预处理语句（prepared statement），这个语句的结构是固定的，但允许在执行时插入不同的参数值。这样可以实现代码重用，并提高查询的执行效率。

demo=# prepare pre_stmt as select * from t1 where b=$1;
PREPARE

在 PostgreSQL 中，预处理语句（prepared statements）会在当前会话中注册。如果想查看当前会话中有哪些预处理语句已经被创建并可用，可以通过查询系统视图 pg_prepared_statements 来获取这些信息。

   name   |                    statement                     |         prepare_time          | parameter_types |  result_types  | from_sql | generic_plans | custom_plans
----------+--------------------------------------------------+-------------------------------+-----------------+----------------+----------+---------------+--------------
 pre_stmt | prepare pre_stmt as select * from t1 where b=$1; | 2025-01-01 18:32:43.697846+07 | {text}          | {integer,text} | t        |             0 |            0
(1 row)

当我们对一个语句运行 EXPLAIN (ANALYZE) 时，可以看到 PostgreSQL 为该语句生成的实际执行计划以及执行的统计信息。这包括查询是如何被优化和执行的，例如是否使用了索引、查询的执行时间、返回的行数等。

demo=# explain (analyze) execute pre_stmt ('aaa');
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.032..0.252 rows=100 loops=1)
   Filter: (b = 'aaa'::text)
   Rows Removed by Filter: 200
 Planning Time: 0.568 ms
 Execution Time: 0.287 ms
(5 rows)

在执行计划的“Filter”行中,第一次执行时，执行计划会显示你传入的实际参数值（比如 ‘aaa’），这时是 自定义计划（custom plan)。但是，当你多次执行这个预处理语句时，执行计划中filter行的参数值会变成显示占位符（如 $1），这时是 通用计划(generic plan)。

demo=# explain (analyze) execute pre_stmt ('aaa');
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.107..0.255 rows=100 loops=1)
   Filter: (b = 'aaa'::text)
   Rows Removed by Filter: 200
 Planning Time: 0.106 ms
 Execution Time: 0.308 ms
(5 rows)

demo=# explain (analyze) execute pre_stmt ('aaa');
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.045..0.255 rows=100 loops=1)
   Filter: (b = 'aaa'::text)
   Rows Removed by Filter: 200
 Planning Time: 0.144 ms
 Execution Time: 0.297 ms
(5 rows)

demo=# explain (analyze) execute pre_stmt ('aaa');
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.049..0.259 rows=100 loops=1)
   Filter: (b = 'aaa'::text)
   Rows Removed by Filter: 200
 Planning Time: 0.293 ms
 Execution Time: 0.309 ms
(5 rows)

demo=# explain (analyze) execute pre_stmt ('aaa');
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.041..0.248 rows=100 loops=1)
   Filter: (b = 'aaa'::text)
   Rows Removed by Filter: 200
 Planning Time: 0.279 ms
 Execution Time: 0.289 ms
(5 rows)

demo=# explain (analyze) execute pre_stmt ('aaa');
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.034..0.215 rows=100 loops=1)
   Filter: (b = $1)
   Rows Removed by Filter: 200
 Planning Time: 0.101 ms
 Execution Time: 0.245 ms
(5 rows)

在执行查询时，最开始会看到执行计划中显示实际传入的参数值，但随着执行次数的增加，执行计划会变成使用占位符（例如 $1）来表示参数。此时，查询使用的是通用计划。通用计划一旦生成后，无论传入什么不同的参数值，它的执行计划都不会再发生变化，直到该预处理语句结束。

demo=# explain (analyze) execute pre_stmt ( 'bbb' );
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.111..0.367 rows=100 loops=1)
   Filter: (b = $1)
   Rows Removed by Filter: 200
 Planning Time: 0.181 ms
 Execution Time: 0.412 ms
(5 rows)

demo=# explain (analyze) execute pre_stmt ( 'ccc' );
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.165..0.249 rows=100 loops=1)
   Filter: (b = $1)
   Rows Removed by Filter: 200
 Planning Time: 0.025 ms
 Execution Time: 0.289 ms
(5 rows)

demo=# explain (analyze) execute pre_stmt ( null );
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.232..0.233 rows=0 loops=1)
   Filter: (b = $1)
   Rows Removed by Filter: 300
 Planning Time: 0.022 ms
 Execution Time: 0.255 ms
(5 rows)

如果你查看 PostgreSQL 的源代码（特别是 src/backend/utils/cache/plancache.c 文件），你将能理解为什么在执行 5 次之后，执行计划会发生变化。
这个文件包含了与查询计划缓存相关的逻辑，解释了为什么预处理语句在执行多次后会从“自定义计划”变为“通用计划”。

/*
 * choose_custom_plan: choose whether to use custom or generic plan
 *
 * This defines the policy followed by GetCachedPlan.
 */
static bool
choose_custom_plan(CachedPlanSource *plansource, ParamListInfo boundParams)
{
        double          avg_custom_cost;
 
        /* One-shot plans will always be considered custom */
        if (plansource->is_oneshot)
                return true;
 
        /* Otherwise, never any point in a custom plan if there's no parameters */
        if (boundParams == NULL)
                return false;
        /* ... nor for transaction control statements */
        if (IsTransactionStmtPlan(plansource))
                return false;
 
        /* See if caller wants to force the decision */
        if (plansource->cursor_options & CURSOR_OPT_GENERIC_PLAN)
                return false;
        if (plansource->cursor_options & CURSOR_OPT_CUSTOM_PLAN)
                return true;
 
        /* Generate custom plans until we have done at least 5 (arbitrary) */
        if (plansource->num_custom_plans < 5)
                return true;

一旦 PostgreSQL 使用了通用计划，即使数据改变并重新分析表，执行计划也不会再改变。

demo=# insert into t1 select i,'ddd' from generate_series(201,210) i;
INSERT 0 10
demo=# insert into t1 select i,'eee' from generate_series(211,211) i;
INSERT 0 1
demo=# analyze t1;
ANALYZE
demo=# select b,count(*) from t1 group by b order by b;
  b  | count
-----+-------
 aaa |   100
 bbb |   100
 ccc |   100
 ddd |    10
 eee |     1
(5 rows)
demo=# explain (analyze) execute pre_stmt('ddd');
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..5.89 rows=62 width=8) (actual time=0.059..0.070 rows=10 loops=1)
   Filter: (b = $1)
   Rows Removed by Filter: 301
 Planning Time: 0.021 ms
 Execution Time: 0.184 ms
(5 rows)

当数据量增加、数据分布不均匀，并且在某一列（比如“b”列）上建立了索引时，查询的执行计划可能会发生变化，PostgreSQL 会根据这些因素选择不同的执行策略。

demo=# insert into t1 select i, 'aaa' from generate_series (1,2000000) i;
INSERT 0 1000000
demo=# insert into t1 select i, 'bbb' from generate_series (1000001,3000000) i;
INSERT 0 1000000
demo=# insert into t1 select i, 'ccc' from generate_series (2000001,3000000) i;
INSERT 0 1000000
demo=# insert into t1 select i, 'eee' from generate_series (3000001,3000010) i;
INSERT 0 10
demo=# create index idx_b on t1(b);
CREATE INDEX
demo=#  select b,count(*) from t1 group by b order by b;
  b  |  count
-----+---------
 aaa | 1000000
 bbb | 1000000
 ccc | 1000000
 eee |      10
 
(4 rows)

无论我们执行这个查询多少次（查询的是参数‘eee’），PostgreSQL 都不会使用通用计划，而是会持续使用自定义计划。

demo=# explain (analyze) execute pre_stmt('eee');
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Index Scan using idx_b on t1  (cost=0.43..4.45 rows=1 width=8) (actual time=0.039..0.048 rows=10 loops=1)
   Index Cond: (b = 'eee'::text)
 Planning Time: 0.287 ms
 Execution Time: 0.076 ms
(4 rows)
------>重复执行多次，但至少执行 10 次。
demo=# explain (analyze) execute pre_stmt('eee');
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Index Scan using idx_b on t1  (cost=0.43..4.45 rows=1 width=8) (actual time=0.037..0.045 rows=10 loops=1)
   Index Cond: (b = 'eee'::text)
 Planning Time: 0.137 ms
 Execution Time: 0.066 ms
(4 rows)

在某些情况下，当数据分布不均匀时（比如某列中有很多重复值，只有少数值是稀有的，比如上面例子中出现的b=‘eee’），即使考虑到重新生成执行计划的开销，自定义计划的执行成本仍然比通用计划低。这样，PostgreSQL 会优先选择使用自定义计划，而不会使用通用计划，甚至在重新规划的开销考虑进去之后，通用计划也可能永远不会被使用。

顺便提一下啊，在postgresql 15后，explain增加了generic_plan选型来供我们方便的查看通用执行计划

demo=# EXPLAIN (GENERIC_PLAN) SELECT * FROM t1 WHERE t1 = $1;
                               QUERY PLAN
------------------------------------------------------------------------
 Gather  (cost=1000.00..31400.05 rows=15000 width=8)
   Workers Planned: 2
   ->  Parallel Seq Scan on t1  (cost=0.00..28900.05 rows=6250 width=8)
         Filter: (t1.* = $1)
(4 rows)

总的来说，PostgreSQL 15 引入了 EXPLAIN (GENERIC_PLAN) 选项，使得开发者可以明确地查看通用计划，而不受参数变化的影响。

如果觉得文章有点价值，请帮忙点关注，谢谢

【PostgreSQL内核学习（十七）—— （AutoAnalyze）】
qq_43899283的博客
10-07 874
是一种自动统计信息收集和优化功能，用于数据库管理系统中。其主要目的是在数据库中定期收集表的统计信息，以便查询优化器能够生成更有效的执行计划。以下是有关自动统计信息收集会自动定期检查数据库中的表，并确定哪些表需要更新统计信息。这些统计信息包括行数唯一值数量最小值最大值等，这些信息对于查询优化非常重要。查询优化收集的统计信息可帮助查询优化器更好地估算查询成本，从而选择更优的执行计划。这可以提高查询性能减少查询的执行时间。避免过度分析会智能地选择哪些表需要进行统计信息收集，以避免不必要的分析。
PostgreSQL GPU 加速(HeteroDB pg_strom) (GPU计算, GPU-DIO-Nvme SSD, 列存, GPU内存缓存)
weixin_34209851的博客
07-28 3631
标签 PostgreSQL , GPU , heteroDB , pg_strom , CUDA , nvidia , DIO , Nvme , SSD , 列存 , GPU内存缓存 背景 HeteroDB pg_strom是PG的一个GPU加速插件，目前已支持到PG 11的版本。 在这些场景中可以加速查询，非常适合计算型场景（OLAP场景） 1、F...
Postgresql源码（106）Generic Plan与Custom Plan的区别（以分区表为例）
InternalsOf
06-15 1559
Generic Plan与Custom Plan的区别（以分区表为例）
postgresql执行计划
m0_61066945的博客
03-28 8058
用于测试分析、SQL调优
PBE协议
Carrot_L的博客
10-15 3269
pbe执行方案 我们已经了解了一个SQL的执行流程，经历了parse、analyze、rewrite、plan、execute的全流程。那么思考一下，加入我有一条sql语句，这条语句我会执行几千次几万次，每一次都需要完整的执行上述的所有流程么？很明显，不用。 对于parse部分，对于一条sql，我执行千万遍，每次的parsetree也都是相同的，因此完全可以只执行一次就够了，将parsetree保存下来，之后就可以直接用了。 对于analyze、rewrite部分，如果我没有执行ddl语句去修改SQL用到的
PostgreSQL plan cache 源码浅析 - 如何确保不会计划倾斜
weixin_33709364的博客
06-17 406
早上写了一篇文章《为什么用 PostgreSQL 绑定变量 没有 Oracle pin S 等待问题》，可以看到PostgreSQL为开发人员着想的，设计得非常人性化。 https://yq.aliyun.com/articles/55698 同时也收到了一些朋友发来的问题，有朋友问我PostgreSQL plan cache有没有计划倾斜的问...
PostgreSQL如何查看带有绑定变量SQL的通用执行计划
Focus on PostgreSQL
09-13 1048
当我们在PostgreSQL中分析一些历史的SQL问题时，往往看到的SQL都是带有绑定变量的。不仅如此，对于这些带有绑定变量的SQL，我们甚至没法像在Oracle中一样获取一个预估的执行计划。当然还有点需要注意的，如果估计成本高于先前执行的平均成本时就不会选择通用计划了，所以我们可以人为的控制前5次的平均成本，让其达到一个很高的值，这一点我们可以增加cpu_operator_cost的值来实现。我们似乎只能去通过带入值去获取相应的执行计划了，这对于那些绑定变量很多的SQL来说无疑是十分繁琐的。
PostgreSQL 商用版本EPAS(阿里云ppas) 自定义custom plan次数
weixin_34190136的博客
01-27 359
标签 PostgreSQL , 绑定变量 , custom plan , generic plan 背景 PostgreSQL 数据库与商用数据库Oracle一样，支持绑定变量，绑定变量的目的是降低SQL硬解析的性能开销。 使用绑定变量后，执行变成这样的流程，一次prepare，后面都只需要bind和execute： prepare ...
PostgreSQL 11 preview - 增加强制custom plan GUC开关(plancache_mode)，对付倾斜
weixin_33895604的博客
04-18 371
标签 PostgreSQL , plan cache , generic plan , custom plan , plancache_mode 背景 对于高并发的小事务，使用绑定变量（prepared statement）来缓存执行计划，可以降低简单SQL在sql parser, plan上的开销。 《PostgreSQL 11 preview -...
openGauss 查询规划 ：其他优化器选项
最新发布
weixin_53596073的博客
10-16 1343
开启enable_sonic_hashjoin，且查询达到约束条件使用基于面向列的hash表设计的Hash Join算子时，在Explain Analyze/Performance的执行计划和执行信息中，算子显示为“Sonic Hash Join”，而未达到该约束条件时，算子名称将显示为“Hash Join”，Explain详解请参见。当关闭该参数时，则默认收集openGauss第一个节点的统计信息，此时可能会影响生成查询计划的质量，但信息收集性能较优，建议客户谨慎考虑。该参数当前版本已废弃，请勿设置。
postgresql使用UUID函数gen_random_uuid()
liao411280543的博客
04-08 5618
在postgresql13中内置了函数gen_random_uuid() create extension “pgcrypto” select gen_random_uuid() select * from pg_extension; select * from pg_available_extensions; pgcrypto.control
关于PostgreSQL的绑定变量窥视的问题详解
魂醉的一亩二分地
03-23 1125
1. 绑定变量窥视的原理说明 Oracle DBA都知道，绑定变量窥视功能是Oracle数据库的一个特性，自ORACLE9i版本开始引入，是可以通过参数数“_optim_peek_user_binds”来控制是否开启，默认是开启，即为TRUE。这就意味着，第一次以变量的方式执行某类SQL时，会生成第一个执行计划，后续执行该类SQL语句，即使变量的传入值不同，但因变量窥视的作用，依然会沿用第一次SQL语句执行时生成的执行计划，这种特性非常适用于业务表数据分布比较均匀的场景，执行计划比较稳定。但对于数据分布严重
码农必备SQL调优（下）
Gauss松鼠会
05-18 4022
目录 1. Plan Hint调优概述 2. Join顺序的Hint 3. Join方式的Hint 4. 行数的Hint 5. Scan方式的Hint 6. 子链接块名的hint 7. Hint的错误、冲突及告警 8. 优化器GUC参数的Hint 9. Custom Plan和Generic Plan选择的Hint 10. 指定子查询不展开的Hint 11. 指定不使用全局计划缓存的Hint 12. 同层参数化路径的Hint 1. Plan Hint调优概述 Plan H
LightDB/PostgreSQL执行计划缓存机制
zhouhuajun的专栏
09-15 636
LightDB,postgreSQL执行计划缓存
PostgreSQL自定义执行计划缓存模式——plan_cache_mode
Focus on PostgreSQL
04-16 3569
PostgreSQL12中新增plan_cache_mode参数，用来改变执行计划的缓存模式。 详细介绍： plan_cache_mode (enum) Prepared statements (either explicitly prepared or implicitly generated, for example in PL/pgSQL) can be executed u...
PostgreSQL SQL OUTLINE插件sr_plan (保存、篡改、固定 执行计划)
weixin_34399060的博客
02-28 894
标签 PostgreSQL , sql plan outline , 执行计划篡改 , query rewrite , sr_plan , pg plan hint 背景 功能较为强大的数据库，通常都有query rewrite的功能，比如JOIN时提升或下推条件，调整JOIN顺序等。 例如 create table a(id int, in...
转载：数据库查询过程是怎么实现的？
晨风
12-25 4682
转载来自：http://www.newsmth.net/nForum/#!article/Database/122316 例如一条查询语句  select person.id from department;  数据库底层是如何实现的呢 ***********************************************************************
gprof 在postgresql中的使用
10-11 520
gprof 在postgresql中的使用 gprof简介： 1.gprof是GNU profiler工具。可以显示程序运行的“flat profile”，包括每个函数的调用次数，每个函数消耗的处理器时间。也可以显示“Callgraph (调用图)”，包括函数的调用关系，每个函数...
PostgreSQL查询当前执行中SQL的执行计划——pg_show_plans
Enmotech的博客
08-11 2115
点击上方"蓝字"关注我们，享更多干货！执行计划存储如果同样的SQL要执行很多遍，且每次都是同样的执行计划、每次都发生硬解析，则会消耗大量时间。类似于Oracle存放执行计划...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值