PostgreSQL: Tuning parameters or Tuning Queries?

About this session

When it comes to database performance tuning, most PostgreSQL practitioners focus on optimizing configuration parameters. It is often assumed that as soon as we choose the correct values for parameters and restart the database instance, all the world's problems will be solved. Indeed, due to configuration parameters tuning, we can observe database performance increase up to 10-20% and sometimes up to 50%. That might sound like impressive numbers, but individual query optimization routinely makes queries run several times faster, sometimes ten or more times faster. All of us might recall some examples of such drastic performance improvement, but it is not easy to quantify the impact.
In this talk, we will demonstrate the difference each approach can make with practical examples by using the Postgres_air database (https://github.com/hettie-d/postgres_air)- the largest publicly available PostgreSQL training DB. We will compare the impact of tuning different memory allocation parameters with the impact of creating missing indexes and, finally - with the impact of query rewrite. Hopefully, the numbers will speak for themselves.

Presented by

Henrietta Dombrovskaya

Henrietta (Hettie) Dombrovskaya is a database researcher and practitioner with over 40 years of academic and industrial experience. At present she is

–- Database Architect at DRW Holdings in Chicago, IL
-- Prairie Postgres NFP President
-- Illinois Prairie Postgres User Group Organizer
-- LPI Board of Directors member
-- ACM Chicago Chapter Communications Chair

Also:

–- Active community member, a frequent speaker at the PostgreSQL Conferences
-- A researcher focused on developing efficient interactions between applications and databases and implementation of temporal data
–– An author of the PostgreSQL Query Optimization book