Hands-on Lab·PostgreSQL
PostgreSQL Query Plans with EXPLAIN
`EXPLAIN (ANALYZE, BUFFERS)` gives estimates, actuals and the number that matters most - how many pages were touched. Adding one index takes a query from 4,546 buffers and two parallel workers to 21 buffers and none.
Performance Guide 33 of 47 Intermediate
- OSUbuntu 26.04 LTS (resolute)
- PostgreSQL18.6-0ubuntu0.26.04.1
- Test data400,000 rows, 2 core / 4 GB host
- TimeAbout 16 min
- Reviewed27 August 2026
Written against the versions above. `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.
| Server Name | IP Address | OS | Roles | CPU | RAM | HDD |
|---|---|---|---|---|---|---|
| db-a01 | 192.168.0.81 | Ubuntu 26.04 LTS | Primary / Source / Replica Set Member 1 | 2 Core | 4 GB | 50 GB |
Before you start
- The
appdbdatabase. - Enough rows for plans to differ - this builds 400,000.
-
Build a table worth planning against
-
Read a plan with no index
-
Add the index and compare
-
Filter on the second column only