前言
在Oracle数据库中，我们可以通过v$sql_plan查看执行计划，但是在PostgreSQL中，等同于这个视图的功能竟然没有。在网上搜索了一下，发现pg_store_plans可以实现这个功能。
安装
我们从github上下载软件包来进行编译安装。
wget -c https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.5.zip--解压后，进入解压后的文件夹，执行make & make install。unzip 1.5.zipmake USE_PGXS=1make USE_PGXS=1 install
编译安装完成后，需要在shared_preload_libraries中设置参数pg_store_plans。
shared_preload_libraries='pg_store_plans'
最后再创建extension就可以用了
create extension pg_store_plans;
建完就会有一个新的视图pg_store_plans，并且已经开始存储执行计划的数据了。
test=# \d pg_store_plans View "public.pg_store_plans" Column | Type | Collation | Nullable | Default -------------------------+--------------------------+-----------+----------+--------- userid | oid | | | dbid | oid | | | queryid | bigint | | | planid | bigint | | | queryid_stat_statements | bigint | | | plan | text | | | calls | bigint | | | total_time | double precision | | | min_time | double precision | | | max_time | double precision | | | mean_time | double precision | | | stddev_time | double precision | | | rows | bigint | | | shared_blks_hit | bigint | | | shared_blks_read | bigint | | | shared_blks_dirtied | bigint | | | shared_blks_written | bigint | | | local_blks_hit | bigint | | | local_blks_read | bigint | | | local_blks_dirtied | bigint | | | local_blks_written | bigint | | | temp_blks_read | bigint | | | temp_blks_written | bigint | | | blk_read_time | double precision | | | blk_write_time | double precision | | | first_call | timestamp with time zone | | | last_call | timestamp with time zone | | |
这个视图有部分信息和pg_stat_statements重叠了。
user_id
: 执行语句的用户id。
dbid
: 执行语句的数据库的id。
queryid
：内部哈希码，根据语句的查询字符串计算。
planid
：执行计划的planid，类似oracle中的plan_hash_value。
queryid_stat_statements
：语句的queryid，类似oracle中的sql_id，可以和pg_stat_statements进行关联查看详细的sql语句。
plan
: 执行计划的文本。格式由参数pg_store_plans.plan_format
指定。
calls
：执行次数。
total_time
：语句使用的计划花费的总时间，以毫秒为单位
min_time
：语句使用的计划花费的最短时间，以毫秒为单位
max_time
：语句使用的计划花费的最长时间，以毫秒为单位
mean_time
：语句使用的计划平均时间，以毫秒为单位
stddev_time
：语句使用的计划花费时间的总体标准偏差，以毫秒为单位
rows
：语句使用的计划查询和影响的总行数。
shared_blks_hit
：语句使用的计划在共享块缓存命中总数。
shared_blks_read
：语句使用的计划读取的共享块总数。
shared_blks_dirtied
：语句使用的计划读取脏共享块总数。
shared_blks_written
：语句使用的计划写入的共享块总数。
local_blks_hit
：语句使用的计划命中的本地块缓存总数
local_blks_read
：语句使用的计划读取的本地块总数
local_blks_dirtied
：语句使用的计划弄脏的本地块总数
local_blks_written
：语句使用的计划写入的本地块总数
temp_blks_read
：语句使用计划读取的临时块总数
temp_blks_written
：语句使用计划写入的临时块总数
blk_read_time
：语句使用计划读取块所花费的总时间，以毫秒为单位（需要启用track_io_timing，否则为0）
blk_write_time
: 语句使用计划写入块所花费的总时间，以毫秒为单位 (需要启用track_io_timing，否则为0)
first_call
：语句使用计划首次调用的时间
last_call
: 语句使用计划最后一次调用的时间
例如：我们想查询某SQL语句和它的执行计划，只需要将pg_stat_statements和pg_store_plans进行关联就可以了。
test=# select a.query,b.plan,b.calls from pg_stat_statements a, pg_store_plans b where a.queryid=b.queryid_stat_statements and b.queryid_stat_statements=-235294569018386844 and a.dbid=13591;-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------query | SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description" | FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON | c.objoid = e.oid AND c.classoid = $1::pg_catalog.regclass ORDER BY 1plan | Sort (cost=10.46..10.47 rows=1 width=158) | Sort Key: e.extname | -> Nested Loop Left (cost=0.28..10.45 rows=1 width=158) | Join Filter: (n.oid = e.extnamespace) | -> Nested Loop Left (cost=0.28..9.32 rows=1 width=98) | -> Seq Scan on pg_extension e (cost=0.00..1.01 rows=1 width=76) | -> Index Scan using pg_description_o_c_o_index on pg_description c (cost=0.28..8.30 rows=1 width=30) | Index Cond: ((objoid = e.oid) AND (classoid = '3079'::oid)) | -> Seq Scan on pg_namespace n (cost=0.00..1.06 rows=6 width=68)calls | 1
提供的函数
除了提供视图方便查询之外，还提供了一系列的函数。
pg_store_plans_reset
：丢弃到目前为止由pg_store_plans
所有收集的统计信息。默认情况下，只有超级用户才能执行此功能。
pg_store_plans
：pg_store_plans视图的定义是基于pg_store_plans函数的。
pg_store_hash_query
：该函数计算查询文本的哈希值。计算pg_store_plans的查询id使用相同的算法，该函数可以与pg_store_plans结合使用。
test=# select public.pg_store_plans_hash_query('select 1');-[ RECORD 1 ]-------------+-----------pg_store_plans_hash_query | 2042559815
pg_store_plans_textplan
：当你把参数pg_store_plans.plan_formats设置为'raw'的时候会显示短格式json，短格式json是pg_store_plans中的计划的内部格式，这个函数可以将短格式的json转换成正常的text格式。
pg_store_plans_jsonplan
：类似同上，只是将短格式的json转换成正常的json格式表示。
pg_store_plans_xmlplan
：类似同上，将短格式的json转换成xml格式表示。
pg_store_plans_yamlplan
：类似同上，将短格式的json转换yaml格式表示。
控制参数
pg_store_plans.max
: pg_store_plans视图中的最大行数，默认值为1000，不频繁使用的执行计划的信息将被丢弃。该参数只能在服务器启动时设置。
pg_store_plans.track
: 控制对哪些语句进行计数。指定top
跟踪顶级语句(由客户端直接发出的语句),all
还跟踪嵌套语句(例如函数内调用的语句)，指定none
以禁用语句统计信息收集。默认值为top
。只有超级用户才能更改此设置。
pg_store_plans.plan_format
:控制pg_store_plans中存储执行计划的格式。text
为默认值并以普通文本表示形式显示，json
、xml
和yaml
以相应格式显示。raw
提供的是内部格式。
pg_store_plans.min_duration
: 语句最小执行时间，以毫秒为单位。如果设置为0(默认值)，则记录所有执行计划。只有超级用户才能更改此设置。
pg_store_plans.log_analyze
: 保存的执行计划中包含EXPLAIN ANALYZE 输出，而不仅仅是EXPLAIN输出。这个参数默认值为off。
pg_store_plans.log_buffers
: 保存的执行计划中包含EXPLAIN (ANALYZE, BUFFERS)输出，而不仅仅是EXPLAIN输出，该参数默认值为off。
pg_store_plans.log_timing
: 设置为 false 会禁用记录实际时间。在某些系统上，重复读取系统时钟的开销会显著降低查询的速度，因此当只需要实际行数而不是每个执行节点的精确执行时间时，将此参数设置为FALSE可能很有用。当pg_store_plans.log_analyze
设置为TRUE时，始终测量整个语句的运行时间。它默认为TRUE。
pg_store_plans.log_triggers
: 在记录的执行计划中包含触发器执行统计信息。除非打开pg_store_plans.log_analyze，
否则此参数无效。
pg_store_plans.verbose
: 保存的执行计划中包含EXPLAIN VERBOSE输出，而不仅仅是 EXPLAIN 输出。该参数默认值为off。
pg_store_plans.save
: 指定是否在服务器关闭时保存计划统计信息，默认值为on。如果设置为off，则统计信息不会在关闭时保存，也不会在服务器启动时重新加载。此参数只能在postgresql.conf
文件或服务器命令行中设置。
使用这个插件需要与
pg_store_plans.max
成比例的额外共享内存 。请注意，无论何时加载模块，都会消耗此内存，即使pg_store_plans.track
设置为none
。
测试试用
先看下我们当前的设置情况。
test=# SELECT name, setting FROM pg_settings WHERE name LIKE 'pg_store_plans.%'; name | setting -----------------------------+--------- pg_store_plans.log_analyze | off pg_store_plans.log_buffers | off pg_store_plans.log_timing | on pg_store_plans.log_triggers | off pg_store_plans.log_verbose | off pg_store_plans.max | 1000 pg_store_plans.min_duration | 0 pg_store_plans.plan_format | text pg_store_plans.save | on pg_store_plans.track | top
运行pgbench来生点数据和造点执行计划。在测试之前我们先清空pg_store_plans。
test=# SELECT pg_store_plans_reset(); pg_store_plans_reset ---------------------- (1 row)pgbench -U root -p 18804 -i -s 10 --unlogged-tables test -q dropping old tables...NOTICE: table "pgbench_accounts" does not exist, skippingNOTICE: table "pgbench_branches" does not exist, skippingNOTICE: table "pgbench_history" does not exist, skippingNOTICE: table "pgbench_tellers" does not exist, skippingcreating tables...generating data...1000000 of 1000000 tuples (100%) done (elapsed 0.69 s, remaining 0.00 s)vacuuming...creating primary keys...done.
查看pg_stort_plans视图。
test=# SELECT queryid, plan,calls FROM pg_store_plans where dbid=6673065; queryid | plan | calls ------------+------------------------------------------------------------------------+------- 329411391 | Function Scan on pg_store_plans (cost=0.00..10.00 rows=1000 width=24) | 1 3137955951 | Result (cost=0.00..0.01 rows=1 width=4) | 1 1446640908 | Insert on pgbench_branches (cost=0.00..0.01 rows=1 width=40) +| 10 | -> Result (cost=0.00..0.01 rows=1 width=40) | 3770510668 | Function Scan on pg_store_plans (cost=0.00..12.50 rows=5 width=40) +| 1 | Filter: (dbid = '6673065'::oid) | 3126036813 | Insert on pgbench_tellers (cost=0.00..0.01 rows=1 width=44) +| 100 | -> Result (cost=0.00..0.01 rows=1 width=44) | 791401476 | Function Scan on pg_store_plans (cost=0.00..12.50 rows=5 width=24) +| 1 | Filter: (dbid = '6673065'::oid) |
这里可以看到记录了两个insert语句的执行计划，分别是插入pgbench_branches和pgbench_tellers。但是像pgbench_accounts这样的表就没有记录，这是因为它是copy语句而不是dml。
手动执行一条查询语句，然后查看该语句的执行计划，已经记录进去了。
SELECT abalance FROM pgbench_accounts WHERE aid=123;test=# SELECT queryid, plan,calls FROM pg_store_plans where dbid=6673065; queryid | plan | calls ------------+----------------------------------------------------------------------------------------------+------- 3137955951 | Result (cost=0.00..0.01 rows=1 width=4) | 1 1446640908 | Insert on pgbench_branches (cost=0.00..0.01 rows=1 width=40) +| 10 | -> Result (cost=0.00..0.01 rows=1 width=40) | 3126036813 | Insert on pgbench_tellers (cost=0.00..0.01 rows=1 width=44) +| 100 | -> Result (cost=0.00..0.01 rows=1 width=44) | 2032872830 | Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.42..8.44 rows=1 width=4)+| 1 | Index Cond: (aid = 123)
后记
pg_store_plans
可以帮助我们实现像Oracle一样的v$sql_plan的功能。但是它同样也会消耗一定的共享内存，特别是当你max设置的比较大的时候。
