Long Queries and the Art of Full Scan

Presented by:

Img 5860 2

Henrietta Dombrovskaya

BrokerX

``` Henrietta Dombrovskaya is a database researcher and developer with over 35 years of academic and industrial experience. She holds a Ph.D. in Computer Science from the University of Saint Petersburg, Russia. At present she is

  • A Senior Cloud Consultant at Enterprise DB
  • Local Organizer of the Chicago PostgreSQL User Group
  • Active community member, a frequent speaker at the PostgreSQL Conferences
  • A researcher focused on developing efficient interactions between applications and databases; publications include Proceedings of EDBT 2014 Athens, ICDE 2016 in Helsinki, and SOFSEM 2020 Limassol
  • A winner of the “Technologist of the Year” 2019 award of the Illinois Technology Association
  • An author of PostgreSQL Query Optimization book ```

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

Date:
2021 September 22 13:00 EDT
Duration:
1 h
Room:
Online
Conference:
2021 Postgres Conference Webinars
Language:
Track:
Ops
Difficulty:
Requires Registration:
Yes (Registered: 154)