Webinar Recording: How to use the Postgres query planner to debug bad plans and speed up queries

Find the presentation slide deck and all other shared material below!
On March 29th, 2023, we hosted our webinar on how to use the Postgres query planner to debug bad plans and speed up queries. We had a look at how the Postgres query planner chooses different plans for the same query, and how to debug bad plans when they occur. We also showed how to track slow queries over time and how to debug them to improve application performance.
Articles and material we talked about in the webinar:
- Presentation slides: resources.pganalyze.com/pgana...
- pganalyze newsletter: pganalyze newsletter
- The Basics of Postgres Query Planning: pganalyze.com/docs/explain/ba...
- Hacking the Query Planner (PDF) - Tom Lane's presentation from PGCon 2011: www.pgcon.org/2011/schedule/a...
- How Postgres Chooses Which Index To Use For A Query: pganalyze.com/blog/how-postgr...
- CREATE STATISTICS postgresql.org docs: www.postgresql.org/docs/curre...
- 5mins of Postgres E15: Tuning Postgres' Deterministic Query Planner, Extended Statistics and Join Collapse Limits: pganalyze.com/blog/5mins-post...
- Query Planning Gone Wrong (video) - Robert Haas' presentation from Postgres Open 2013: • Robert Haas: Query Pla...
- auto_explain postgresql.org docs: www.postgresql.org/docs/curre...
- "Sampling-based timing for EXPLAIN ANALYZE” on the pgsql-hackers list: www.postgresql.org/message-id...
- Recording of our previous webinar "Optimizing Postgres I/O Performance and Costs": pganalyze.com/webinars/thank-...
- EXPLAIN (ANALYZE) needs BUFFERS to improve the Postgres query optimization: postgres.ai/blog/20220106-exp...
- pg_hint_plan GitHub repository: github.com/ossc-db/pg_hint_plan
- 5mins of Postgres E1: Using Postgres statistics to improve bad query plans, pg_hint_plan extension: pganalyze.com/blog/5mins-post...
- "How to influence query planning in Postgresql" - by Chris Kiehl: chriskiehl.com/article/query-...
- 5mins of Posrgres E51: Using Memoize to speed up joins between two Postgres tables: pganalyze.com/blog/5mins-post...
- pganalyze Resources Library: pganalyze.com/resources
- Get in touch with us to get a product walk-through or a demo tailored to your needs: pganalyze.com/contact
- - - - - - - - - - - - - -
Following, we are sharing some of the responses to the questions we could not fit into our live Q&A session with all of you as they might prove to be useful to all of you, not just the original questioner:
- - -
"Will postgres cache query plans for parameterized queries? Is there value in setting some parameters as literal values in queries?" - by Lukas F.
Postgres will re-use query plans for prepared statements, but not necessarily for parameterized queries. Unless you (or your ORM) are using explicit PREPARE/EXECUTE statements, Postgres will not re-use query plans (i.e. plan from scratch each time). To my knowledge there is no benefit to using constants instead of bind parameters in regular queries.
- - -
- - -
"Which is a better choice, adding an index condition on the WHERE clause, or on the tables being joined?" by Aswin B.
Generally speaking, clauses that only involve a single table (e.g. "a = 1") are going to be better fits for indexes, because they can be used in any type of join method. If you have clauses that involve two tables (e.g. "a = b.a_id"), that requires use of a Paramaterized Index Scan, which is dependent on the join order and join method.
- - -
- - -
"Do the underlying tables of a view count as part of the table limit for using the GEQO. E.g. I have one view (that joins 6 tables) joining to another view (with 7 tables), will this join use the GQO?" - by Noah P.
This depends on some details we didn't talk about today (join_collapse_limit and from_collapse_limit), but its certainly possible for GEQO to engage in this situation, if the from_collapse_limit allows. View expansion happens early in the planning process, before join order is considered (and the GEQO limit applies). You could experiment with from_collapse_limit, and the geqo_threshold setting (i.e. raise it on a connection) to see if that affects the plan choice.
- - -

[@Golf_Quest]

Hi Lucas, nice video. I'm the original author of Aurora's QPM, and I'd be happy to tell you more about it. Many people think of QPM as you described it - as a way to force the planner to use a known-good plan or to avoid a known-bad plan, but it's actually a fully cost-based mechanism. Multiple plans may be approved, and the planner will choose the minimum cost plan among them for the current bind variables and literals. If you think about prepared statements with bind variables, it's immediately apparent that a single optimal plan may not be enough, but the same is true with literals. The ability to evolve the set of known-good plans enables plans to just get better and better with time as the planner discovers new and better plans, especially across major version upgrades.

[@LukasFittl]

Thanks for providing the additional context! And yes, makes a lot of sense to allow better costed plans vs just a single fixed plan.

[@2008topshelf]

nicely done Lukas

[@marcelocastrillo4137]

Amazing webinar, thanks!

[@szuliq]

Thanks for the great webinar!

[@LukasFittl]

Thank you Krzysztof - happy to hear you found it useful!

[@shamstabrez2986]

uploadd the entire series of postgres ffrom beginners to advanced level