Overview

Part 1 looks at some principles and introduces some of the tools in your arsenal.

Part 2 covers the dreaded PostgreSQL bloat issue as well as how TOAST can help performance, or hinder it.

Part 3 dives deeper into identifying, investigating and mitigating slow queries.

Part 4 covers indexes.

Part 5 (this post) covers rewriting queries.

TL;DR

• Break your query down using CTEs
• Optimise each step
• Rewrite using what you’ve learned (if you want to)
• Visualising an explain plan can really make problems obvious, compared to sifting through a sea of text

Rewriting queries

bench=# \d pgbench_accounts
              Table "public.pgbench_accounts"
  Column  |     Type      | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
 aid      | integer       |           | not null |
 bid      | integer       |           |          |
 abalance | integer       |           |          |
 filler   | character(84) |           |          |
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
    "i_pa2" btree (aid, abalance, bid) WHERE aid > 0 AND aid < 194999 AND abalance < '-4500'::integer AND abalance > '-5000'::integer
Foreign-key constraints:
    "pgbench_accounts_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
Referenced by:
    TABLE "pgbench_history" CONSTRAINT "pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid)

bench=# \d pgbench_branches
              Table "public.pgbench_branches"
  Column  |     Type      | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
 bid      | integer       |           | not null |
 bbalance | integer       |           |          |
 filler   | character(88) |           |          |
Indexes:
    "pgbench_branches_pkey" PRIMARY KEY, btree (bid)
Referenced by:
    TABLE "pgbench_accounts" CONSTRAINT "pgbench_accounts_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
    TABLE "pgbench_history" CONSTRAINT "pgbench_history_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
    TABLE "pgbench_tellers" CONSTRAINT "pgbench_tellers_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)

bench=# \d pgbench_history
                    Table "public.pgbench_history"
 Column |            Type             | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
 tid    | integer                     |           |          |
 bid    | integer                     |           |          |
 aid    | integer                     |           |          |
 delta  | integer                     |           |          |
 mtime  | timestamp without time zone |           |          |
 filler | character(22)               |           |          |
Indexes:
    "i1" btree (aid, delta) WHERE delta < 0
Foreign-key constraints:
    "pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid)
    "pgbench_history_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
    "pgbench_history_tid_fkey" FOREIGN KEY (tid) REFERENCES pgbench_tellers(tid)

bench=# \d pgbench_tellers
              Table "public.pgbench_tellers"
  Column  |     Type      | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
 tid      | integer       |           | not null |
 bid      | integer       |           |          |
 tbalance | integer       |           |          |
 filler   | character(84) |           |          |
Indexes:
    "pgbench_tellers_pkey" PRIMARY KEY, btree (tid)
Foreign-key constraints:
    "pgbench_tellers_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)
Referenced by:
    TABLE "pgbench_history" CONSTRAINT "pgbench_history_tid_fkey" FOREIGN KEY (tid) REFERENCES pgbench_tellers(tid)

Joins

We have already covered one aspect of query rewriting, namely how to rewrite joins, which we covered in Query Performance Improvement Part 3.

Overview

Part 1 looks at some principles and introduces some of the tools in your arsenal.

Part 2 covers the dreaded PostgreSQL bloat issue as well as how TOAST can help performance, or hinder it.

Part 3 dives deeper into identifying, investigating and mitigating slow queries.

Part 4 (this post) covers indexing.

Part 5 covers rewriting queries.

TL;DR

• If you see “Filter:” or “Rows Removed” in your explain plan, you are likely to be using the wrong index
• Composite indexes can be much faster than multiple single indexes
• Partial indexes reduce the height and size of the index, making them faster
• Covering indexes can speed queries even further
• The bigger the table, the more likely it is that an index will add value
• Small tables are often sequentially scanned, whether indexed or not
• Small to medium tables tend to benefit from single column indexes, less from more complex indexes
• Large tables tend to benefit more from complex indexes
• Always test before pushing to production!

Indexes

Indexes are one of the most effective tools to improve query performance. Indexes work best when they return a small subset of data and are small compared to row size. So an 8-byte integer index on a 2kB row is more efficient than a 1kB text index on a 1.5kB row.

Overview

Part 1 looks at some principles and introduces some of the tools in your arsenal.

Part 2 covers the dreaded PostgreSQL bloat issue as well as how TOAST can help performance, or hinder it.

Part 3 (this post) dives deeper into identifying, investigating and mitigating slow queries.

Part 4 covers indexing.

Part 5 covers rewriting queries.

TL;DR

• Use logging to identify slow queries
• Use EXPLAIN to examine an explain plan
• EXPLAIN (ANALYZE) can be dangerous!
• Use depesz, dalibo or PEV to visualize your explain plan
• Red flags in your explain plan include filters, Rows Removed, and / or costs in excess of 100,000
• Data may not be evenly distributed, check it! Especially test data!
• Only select the rows and columns you need

Identifying slow queries

The most reliable way of identifying slow queries is to enable slow query logging using the parameter log_min_duration_statement. The pain threshold varies by environment, but for example if you wanted to log all queries that take longer than 50ms, you would issue the following command in psql:

Overview

Part 1 looks at some principles and introduces some of the tools in your arsenal.

Part 2 (this post) covers the dreaded PostgreSQL bloat issue as well as how TOAST can help performance, or hinder it.

Part 3 dives deeper into identifying, investigating and mitigating slow queries.

Part 4 covers indexing.

Part 5 covers rewriting queries.

TL;DR

• Bloat occurs because PostgreSQL does not update in place, it creates a new row and marks the old one as obsolete
• VACUUM fixes bloat, well, it marks obsolete rows as re-usable space
• VACUUM FULL fixes bloat by compactly rewriting the data file and rebuilding indexes
• You should never run VACUUM FULL because it requires a full table lock
• pg_repack is an option … possibly
• Use pg_repack with care - it can have problematic side effects
• TOAST compresses wide columns so that the resulting row is < 2kB
• If the table is still too wide, then compressed wide columns are split into 2kB chunks and move to a TOAST table
• A 2kB (ish) wide table can be problematic

Bloat

What is bloat?

Strictly speaking, bloat is the unused space left behind by a VACUUM that is not a VACUUM FULL.

Overview

Part 1 (this post) looks at some principles and introduces some of the tools in your arsenal.

Part 2 covers the dreaded PostgreSQL bloat issue as well as how TOAST can help performance, or not.

Part 3 dives deeper into identifying, investigating and mitigating slow queries.

Part 4 covers indexing.

Part 5 covers rewriting queries.

TL;DR

• Scaling up or out is more expensive than spending a couple of hours optimising
• Get answers, not data
• Lazy load lookups
• Eliminate non-essential queries
• Don’t use SERIALIZABLE isolation
• Make sure auto-analyze is running and have a manual ANALYZE schedule as a safety net
• Denormalising is usually the wrong solution to performance issues

Introduction

Welcome to this series of blog posts which will hopefully walk you through everything you need to know about improving query performance, specifically in PostgreSQL. The basic principles apply to all databases, though, even if implementation differences mean that different databases find different things painful.