After 20 years in professional PostgreSQL support and consulting we are finally able to answer one of the most frequently asked questions: “How can I see all active query/ execution plans?” Ladies and gentlemen, let me introduce you to pg_show_plans, an extension which does exactly that. pg_show_plans is Open Source and can be used free of charge as a standard PostgreSQL extension. It will help people to diagnose problems more efficiently and help to digging into more profound performance issues.
How does pg_show_plans work?
pg_show_plans uses a set of hooks in the PostgreSQL core to extract all the relevant information. These plans are then stored in shared memory and exposed via a view. This makes it possible to access these plans in an easy way.
The performance overhead of pg_show_plans will be discussed in a future blog post. Stay tuned for more information and visit our blog on a regular basis.
Installing pg_show_plans
pg_show_plans is available on GitHub for free and can be used free of charge.
Just clone the GitHub repo:
iMac:src hs$ git clone https://github.com/cybertec-postgresql/pg_show_plans.git Cloning into 'pg_show_plans'... remote: Enumerating objects: 54, done. remote: Counting objects: 100% (54/54), done. remote: Compressing objects: 100% (25/25), done. remote: Total 54 (delta 31), reused 52 (delta 29), pack-reused 0 Unpacking objects: 100% (54/54), done.
Then “cd” into the directory and set USE_PGXS. USE_PGXS is important if you want to compile the code outside of the PostgreSQL source tree:
iMac:src hs$ cd pg_show_plans iMac:pg_show_plans hs$ export USE_PGXS=1
Finally, you can run “make” and “make install”
iMac:pg_show_plans hs$ make gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -g -O2 -I. -I./ -I/Users/hs/pg12/include/postgresql/server -I/Users/hs/pg12/include/postgresql/internal -c -o pg_show_plans.o pg_show_plans.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -g -O2 -bundle -multiply_defined suppress -o pg_show_plans.so pg_show_plans.o -L/Users/hs/pg12/lib -Wl,-dead_strip_dylibs -bundle_loader /Users/hs/pg12/bin/postgres iMac:pg_show_plans hs$ make install /opt/local/bin/gmkdir -p '/Users/hs/pg12/lib/postgresql' /opt/local/bin/gmkdir -p '/Users/hs/pg12/share/postgresql/extension' /opt/local/bin/gmkdir -p '/Users/hs/pg12/share/postgresql/extension' /opt/local/bin/ginstall -c -m 755 pg_show_plans.so '/Users/hs/pg12/lib/postgresql/pg_show_plans.so' /opt/local/bin/ginstall -c -m 644 .//pg_show_plans.control '/Users/hs/pg12/share/postgresql/extension/' /opt/local/bin/ginstall -c -m 644 .//pg_show_plans--1.0.sql '/Users/hs/pg12/share/postgresql/extension/'
If things work properly, the code is now successfully installed.
To activate the module, you have to set shared_preload_libraries in postgresql.conf:
test=# SHOW shared_preload_libraries; shared_preload_libraries ----------------------------------- pg_stat_statements, pg_show_plans (1 row)
Once this is done, the extension can be activated in your database:
test=# CREATE EXTENSION pg_show_plans; CREATE EXTENSION
Checking execution plans in PostgreSQL
To see how this module works we need two connections: In the first connection I will run a long SQL statement which selects data from a fairly complex system view. To make sure that the query takes forever I have added pg_sleep:
test=# SELECT *, pg_sleep(10000) FROM pg_stats;
You can now use a second connection to inspect the plan:
test=# \x
Expanded display is on.
test=# SELECT * FROM pg_show_plans ;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------
pid | 34871
level | 0
userid | 10
dbid | 26534
plan | Function Scan on pg_show_plans (cost=0.00..10.00 rows=1000 width=56)
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------
pid | 34881
level | 0
userid | 10
dbid | 26534
plan | Subquery Scan on pg_stats (cost=129.30..169.05 rows=5 width=405) +
| -> Nested Loop Left Join (cost=129.30..168.99 rows=5 width=401) +
| -> Hash Join (cost=129.17..167.68 rows=5 width=475) +
| Hash Cond: ((s.starelid = c.oid) AND (s.staattnum = a.attnum)) +
| -> Seq Scan on pg_statistic s (cost=0.00..34.23 rows=423 width=349) +
| -> Hash (cost=114.40..114.40 rows=985 width=142) +
| -> Hash Join (cost=23.05..114.40 rows=985 width=142) +
| Hash Cond: (a.attrelid = c.oid) +
| Join Filter: has_column_privilege(c.oid, a.attnum, 'select'::text) +
| -> Seq Scan on pg_attribute a (cost=0.00..83.56 rows=2956 width=70) +
| Filter: (NOT attisdropped) +
| -> Hash (cost=18.02..18.02 rows=402 width=72) +
| -> Seq Scan on pg_class c (cost=0.00..18.02 rows=402 width=72) +
| Filter: ((NOT relrowsecurity) OR (NOT row_security_active(oid))) +
| -> Index Scan using pg_namespace_oid_index on pg_namespace n (cost=0.13..0.18 rows=1 width=68)+
| Index Cond: (oid = c.relnamespace)
pg_show_plans is going to return information for every database connection. Therefore it is possible to see everything at a glance and you can react even before a slow query has ended. As far as we know, pg_show_plans is the only module capable of providing you with this level of detail.
Finally …
If you want to know more about execution plans we suggest checking out one of our other posts dealing with index scans, bitmap index scans and alike.
We are looking forward to seeing you on our blog soon for more interesting stuff.