CYBERTEC Logo

pg_show_plans: Watching execution plans in PostgreSQL LIVE

03.2020 / Category: / Tags:

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:

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:

Finally, you can run “make” and “make install”

If things work properly, the code is now successfully installed.

To activate the module, you have to set shared_preload_libraries in postgresql.conf:

Once this is done, the extension can be activated in your database:

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:

You can now use a second connection to inspect the plan:

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.

 

 

0 0 votes
Article Rating
Subscribe
Notify of
guest
3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
PgM
PgM
1 year ago

Hello, is it possible to run pg_show_plans on AWS RDS Postgres? I would assume no? Thank you!

Bogdan Talpiga
Bogdan Talpiga
4 years ago

Hey Hans!

I really enjoy your articles. I was also reading your book about mastering postgresql 11, but i came across a strange behavior withing PostgreSQL planner. I have a huge table (around 100 million rows), and i'm trying to aggregate some records. It's more of an analytical query, something like
SELECT cab.id_consumer,
cab.id_action,
cab.id_subcampaign,
cab.brand,
count(*) AS hits
FROM dimensions.consumer_action_brand cab
GROUP BY cab.id_consumer, cab.id_action, cab.id_subcampaign, cab.brand;

Firstly, the planner gave me a cost of 31779206, with actual time of 200927 ms. It also showed that it does some external merge on the Disk of around 2875752kB. So i increased the work_mem to about 3 GB. After that, the planner gave me a cost of 8847090 (almost 4 times lower), but with an actual time of 236008ms and no more external merge.
I find it quite hard to understand this! Any hint or direction would be much appreciated!

Thank you!

Oleg Bartunov
Oleg Bartunov
4 years ago
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    3
    0
    Would love your thoughts, please comment.x
    ()
    x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram