PostgreSQL Query Plans with EXPLAIN

Performance / Guide 33 of 47 / Intermediate

• OSUbuntu 26.04 LTS
• PostgreSQL18.6-0ubuntu0.26.04.1
• Test data400,000 rows, 2 core / 4 GB host
• TimeAbout 16 min

BUFFERS is on by default with ANALYZE from PostgreSQL 18. On earlier versions you must ask for it, and it is the option most worth asking for.

This guide includes

Use this when a query is slow and you need the plan rather than a guess. This matters because EXPLAIN (ANALYZE, BUFFERS) gives estimates, actuals and buffer counts - and a plan is read inside out rather than top down.

• building a table worth planning against, with 400,000 rows
• reading a plan with no index, from the innermost node outwards
• adding a composite index, and comparing
• filtering on the second column only, and testing the rule people are taught

Before you start

• The appdb database.
• Enough rows for plans to differ - this builds 400,000.

1. 1

   Build a table worth planning against

2. 2

   Read a plan with no index

3. 3

   Add the index and compare

4. 4

   Filter on the second column only

Official sources

• PostgreSQL 18 Documentation - Using EXPLAIN
• PostgreSQL 18 Documentation - Index Types
• PostgreSQL 18 Documentation - Partial Indexes
• PostgreSQL 18 Documentation - Routine Vacuuming
• PostgreSQL 18 Documentation - pg_stat_statements