Long Queries and the Art of Full Scan

Long Queries and the Art of Full Scan
presented by Henrietta Dombrovskaya

• RSVP: https://bit.ly/2XpICvP

• Community Chat: https://discord.gg/ghJ6vBKTrd

• Postgres Conference Silicon Valley 2022 CFP: https://postgresconf.org/conferences/SV2022

Some queries just can’t run in a fraction of a second, no matter how well written. This does not mean they can’t be optimized. Many practitioners hold that since analytical reports do not have strict response time requirements, it is not important how fast or slow they run. In extreme cases, report developers make no effort to make sure that reports are complete in a reasonable time, giving the excuse that the query only runs once a day or once a week or once a month.

This is a dangerous practice. If report performance is neglected, performance can easily degrade from minutes to hours or more. We have observed reports that run for six days before completion! And when the situation becomes that severe, it is not easy to fix in a limited time frame. Often, when an analytical report is developed, source data volumes are really small, and everything performs well. It is the job of SQL developers to examine execution plans even if queries are running fine now and to be proactive to prevent future performance degradation.

In course of his presentation you will learn:

• Which queries are considered long
• How to optimize full table scans
• How the order of joins affects query performance
• How to optimize grouping
• How to avoid multiple table scans
• Additional techniques for long queries optimization, such as temporary tables, CTEs, and materialized view