asish.comTechnical articles by Asish Punnose

Inside a PostgreSQL query plan

Start with a question

Suppose the latest 20 orders for one tenant take a second to load. First capture the exact filter, sort order and representative tenant size. A plan for a tenant with three orders can look quite different from one with three million.

Read from the scans upwards

On a representative test database run EXPLAIN (ANALYZE, BUFFERS) on the SELECT. Find the scan that feeds the sort. Compare estimated and actual rows. For repeated nodes, actual times and rows are averages per loop. ANALYZE executes the statement.

Test one change

For a filter on tenant_id ordered by created_at, test a composite index beginning with tenant_id then created_at. Compare rows visited and buffer activity as well as elapsed time. Include the additional write cost in the decision.

Back to homepage