Скачать с ютуб Webinar Recording: How to use the Postgres query planner to debug bad plans and speed up queries в хорошем качестве

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: https://resources.pganalyze.com/pgana... - pganalyze newsletter: pganalyze newsletter - The Basics of Postgres Query Planning: https://pganalyze.com/docs/explain/ba... - Hacking the Query Planner (PDF) - Tom Lane's presentation from PGCon 2011: https://www.pgcon.org/2011/schedule/a... - How Postgres Chooses Which Index To Use For A Query: https://pganalyze.com/blog/how-postgr... - CREATE STATISTICS postgresql.org docs: https://www.postgresql.org/docs/curre... - 5mins of Postgres E15: Tuning Postgres' Deterministic Query Planner, Extended Statistics and Join Collapse Limits: https://pganalyze.com/blog/5mins-post... - Query Planning Gone Wrong (video) - Robert Haas' presentation from Postgres Open 2013:    • Robert Haas: Query Planning Gone Wrong   - auto_explain postgresql.org docs: https://www.postgresql.org/docs/curre... - "Sampling-based timing for EXPLAIN ANALYZE” on the pgsql-hackers list: https://www.postgresql.org/message-id... - Recording of our previous webinar "Optimizing Postgres I/O Performance and Costs": https://pganalyze.com/webinars/thank-... - EXPLAIN (ANALYZE) needs BUFFERS to improve the Postgres query optimization: https://postgres.ai/blog/20220106-exp... - pg_hint_plan GitHub repository: https://github.com/ossc-db/pg_hint_plan - 5mins of Postgres E1: Using Postgres statistics to improve bad query plans, pg_hint_plan extension: https://pganalyze.com/blog/5mins-post... - "How to influence query planning in Postgresql" - by Chris Kiehl: https://chriskiehl.com/article/query-... - 5mins of Posrgres E51: Using Memoize to speed up joins between two Postgres tables: https://pganalyze.com/blog/5mins-post... - pganalyze Resources Library: https://pganalyze.com/resources - Get in touch with us to get a product walk-through or a demo tailored to your needs: https://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. - - -