pg_show_plans
PostgreSQL extension that shows query plans of all the currently running SQL
statements. Query plans can be shown in several formats, like JSON.
This extension creates a hash table within shared memory. The hash table is not resizable, thus, no new plans can be added once it has been filled up.
INSTALL
Either use PGXS infrastructure (recommended), or compile within the source tree. PostgreSQL versions 12 and newer are supported.
PGXS
Install PostgreSQL before proceeding. Make sure to have pg_config binary,
these are typically included in -dev and -devel packages.
git clone https://github.com/cybertec-postgresql/pg_show_plans.git
cd pg_show_plans
make
make installWithin Source Tree
PG_VER='15.3' # Set the required PostgreSQL version.
curl -O "https://download.postgresql.org/pub/source/v${PG_VER}/postgresql-${PG_VER}.tar.bz2"
tar xvfj "postgresql-${PG_VER}.tar.bz2"
cd postgresql-${PG_VER}
./configure
cd contrib
git clone https://github.com/cybertec-postgresql/pg_show_plans.git
cd pg_show_plans
make USE_PGXS=
make USE_PGXS= installConfigure
Add pg_show_plans to shared_preload_libraries within postgresql.conf:
shared_preload_libraries = 'pg_show_plans'
Restart the server, and invoke CREATE EXTENSION pg_show_plans;:
postgresql=# CREATE EXTENSION pg_show_plans;
CREATE EXTENSION
postgresql=#
USAGE
To see the query plans:
testdb=# SELECT * FROM pg_show_plans;
pid | level | userid | dbid | plan
-------+-------+--------+-------+-----------------------------------------------------------------------
11473 | 0 | 10 | 16384 | Function Scan on pg_show_plans (cost=0.00..10.00 rows=1000 width=56)
11504 | 0 | 10 | 16384 | Function Scan on print_item (cost=0.25..10.25 rows=1000 width=524)
11504 | 1 | 10 | 16384 | Result (cost=0.00..0.01 rows=1 width=4)
(3 rows)
To get query plans and see the corresponding query expression:
testdb=# \x
Expanded display is on.
testdb=# SELECT p.pid, p.level, p.plan, a.query
FROM pg_show_plans p
LEFT JOIN pg_stat_activity a
ON p.pid = a.pid AND p.level = 0 ORDER BY p.pid, p.level;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------
pid | 11473
level | 0
plan | Sort (cost=72.08..74.58 rows=1000 width=80) +
| Sort Key: pg_show_plans.pid, pg_show_plans.level +
| -> Hash Left Join (cost=2.25..22.25 rows=1000 width=80) +
| Hash Cond: (pg_show_plans.pid = s.pid) +
| Join Filter: (pg_show_plans.level = 0) +
| -> Function Scan on pg_show_plans (cost=0.00..10.00 rows=1000 width=48) +
| -> Hash (cost=1.00..1.00 rows=100 width=44) +
| -> Function Scan on pg_stat_get_activity s (cost=0.00..1.00 rows=100 width=44)
query | SELECT p.pid, p.level, p.plan, a.query FROM pg_show_plans p +
| LEFT JOIN pg_stat_activity a +
| ON p.pid = a.pid AND p.level = 0 ORDER BY p.pid, p.level;
-[ RECORD 2 ]-----------------------------------------------------------------------------------------
pid | 11517
level | 0
plan | Function Scan on print_item (cost=0.25..10.25 rows=1000 width=524)
query | SELECT * FROM print_item(1,20);
-[ RECORD 3 ]-----------------------------------------------------------------------------------------
pid | 11517
level | 1
plan | Result (cost=0.00..0.01 rows=1 width=4)
query |
DOCUMENTATION
GUC Variables
pg_show_plans.plan_format = text: query plans output format, either oftext,json,yaml, andxml.pg_show_plans.max_plan_length = 16384: query plan maximal length in bytes. This value affects the amount of shared memory the extension asks for, the server may not start if the value is too high.pg_show_plans.is_enabled = true: whether the extension is enabled at server start up.
Default values are shown after '=' sign.
Views
pg_show_plans: defined asSELECT * FROM pg_show_plans();.
Functions
pg_show_plans(): show query plans:pid: server process ID that runs the query.level: query nest level. Top level is 0. For example, if you execute a simple select query, the level of this query's plan is 0. If you execute a function that invokes a select query, level 0 is the plan of the function and level 1 is the plan of the select query invoked by the function.userid: user ID who runs the query.dbid: database ID the query runs in.plan: query plan.
pg_show_plans_disable(): disable extension.pg_show_plans_enable(): enable extension.pgsp_format_text(): changes query format tojson.pgsp_format_json(): changes query format tojson.pgsp_format_yaml(): changes query format toyaml.pgsp_format_xml(): changes query format toxml.