Learning Hub

How to Find a Slow Query in PostgreSQL 18 With pg_stat_statements, Read Its EXPLAIN ANALYZE Plan, and Fix It With the Right Index

Find the slowest query with pg_stat_statements, read its EXPLAIN ANALYZE plan line by line on PostgreSQL 18.6, and fix it with the right index, with every plan measured on a 2-million-row practice...

October 27, 2026 39 Min Read
16

A slow query stops being a mystery once you can see what the database actually did. PostgreSQL will show you: the plan it chose, how many rows it expected at each step, how many it really found, and how many 8 kB pages of data it had to touch along the way. The command that prints all of that is EXPLAIN ANALYZE. Its output looks dense until someone explains what each line is saying, and then it becomes the most useful debugging tool a database developer has.

Table Of Content

In this tutorial you will find a slow query on a PostgreSQL 18 server, read its plan line by line, fix it with the right index, and confirm the fix with numbers. You will work on a practice database of 100,000 customers and about 2 million orders. Along the way you will meet the situations that confuse beginners: a query that stays slow after you add an index, an index the planner refuses to use, row estimates that are off by a factor of 60,000 because nobody ran ANALYZE, a function wrapped around an indexed column, and an index-only scan that still visits the table.

Everything below ran on PostgreSQL 18.6, and some of what you will see is new in version 18. The release notes list “Automatically include BUFFERS output in EXPLAIN ANALYZE”, “In EXPLAIN ANALYZE, report the number of index lookups used per index scan node”, “Modify EXPLAIN to output fractional row counts” and “Allow skip scans of btree indexes”. I tested only 18.6. Older versions print plans of the same shape with fewer lines, and Step 11 depends on skip scans, which are an 18 feature.

What you will build, and the words you need first

By the end you will have:

  • a practice database named shop with a deliberately awkward data distribution (Step 1);
  • a ranked list of the most expensive queries, taken from the pg_stat_statements extension (Step 2);
  • the ability to read the cost, row counts, timings and buffer counts in an EXPLAIN plan (Steps 3 to 5);
  • five kinds of index, each added for a measured reason: single-column, composite, expression, multicolumn with skip scan, and covering (Steps 6 to 12);
  • a measurement of what those indexes cost to keep (Step 13), and a before and after comparison of the whole workload (Step 14).

Some terms, defined before they are used. A query plan is the list of steps PostgreSQL decides on to answer a query. The planner is the part of the server that picks the plan it estimates to be cheapest. A plan is a tree of nodes such as scans, sorts and joins, and rows flow upward from the lowest nodes to the top. Cost is the planner’s estimate of the work a node does, in arbitrary units rather than seconds. PostgreSQL keeps tables and indexes in pages of 8 kB (you will confirm the size in Step 1), and a buffer is one page held in memory. Statistics are the summaries that ANALYZE collects about your data, such as how common each value is; the planner uses them to guess how many rows a condition will match, a fraction called its selectivity. An index is a separate structure, a B-tree unless you say otherwise, that keeps column values in sorted order together with the location of the matching table rows. The table itself is called the heap.

Prerequisites

  • A PostgreSQL 18 server and the psql client. I used 18.6 on Windows 11. Any installation works: your operating system’s packages, the official installer, or a container. Use a scratch server: this lab creates and drops tables, scans a 2-million-row table many times, and switches autovacuum off for two tables.
  • A superuser role is the simplest way to follow along, and you need permission to edit the server configuration and restart it. Step 2 needs a restart.
  • About 1 GB of free disk space. The finished shop database was 341 MB, and the write-ahead log needs room while it loads.
  • Default settings. I changed nothing except loading pg_stat_statements, so shared_buffers was 128 MB, work_mem 4 MB, random_page_cost 4 and max_parallel_workers_per_gather 2.
  • Background: you can read basic SQL (SELECT, WHERE, JOIN). You need no earlier experience with EXPLAIN.
  • Time: about an hour. The data load takes roughly 15 seconds.

Timings in this article come from one Windows 11 machine with the data in the operating system’s cache. Yours will differ, so compare ratios and page counts rather than exact milliseconds. Each timing was taken after running the statement once to warm the cache, and I kept the second run.

Step 1: Create the practice database

Connect to your server with psql (for example psql -U postgres), then create the database and switch to it:

CREATE DATABASE shop;
\c shop

Now run the load script below. You can paste it into psql or save it as load.sql and run \i load.sql. It creates two tables, fills them with generated data, and runs ANALYZE:

CREATE TABLE customers (
    id            integer   PRIMARY KEY,
    email         text      NOT NULL,
    country       text      NOT NULL,
    signed_up_at  timestamp NOT NULL
);

CREATE TABLE orders (
    id           bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  integer     NOT NULL REFERENCES customers (id),
    status       text        NOT NULL,
    total_cents  integer     NOT NULL,
    created_at   timestamptz NOT NULL
);

-- Keep the autovacuum worker from analyzing these two tables behind our back during the lessons.
ALTER TABLE customers SET (autovacuum_enabled = false);
ALTER TABLE orders    SET (autovacuum_enabled = false);

INSERT INTO customers (id, email, country, signed_up_at)
SELECT i,
       'User' || i || '@' || (ARRAY['example.com', 'example.org', 'example.net'])[1 + i % 3],
       (ARRAY['US', 'GB', 'DE', 'FR', 'NZ', 'BR', 'IN', 'JP', 'AU', 'CA'])[1 + i % 10],
       timestamp '2024-01-01 00:00:00' + i * interval '5 minutes'
FROM generate_series(1, 100000) AS i;

-- Customer 7 is a very large account: every 41st order belongs to them.
INSERT INTO orders (customer_id, status, total_cents, created_at)
SELECT CASE WHEN i % 41 = 0 THEN 7 ELSE 1 + (i * 7919) % 100000 END,
       CASE WHEN i % 1000 = 0 THEN 'cancelled'
            WHEN i % 100 < 4  THEN 'pending'
            WHEN i % 100 < 12 THEN 'shipped'
            ELSE 'delivered' END,
       500 + (i * 37) % 49500,
       timestamptz '2024-01-01 00:00:00+00' + i * interval '31 seconds'
FROM generate_series(1::bigint, 2000000) AS i;

-- The planner needs statistics about the data; ANALYZE collects them.
ANALYZE customers;
ANALYZE orders;

SELECT relname,
       reltuples::bigint AS estimated_rows,
       relpages          AS pages,
       pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname IN ('customers', 'orders')
ORDER BY relname;

SELECT status,
       count(*) AS n,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM orders
GROUP BY status
ORDER BY n DESC;

The data is generated by formulas, so the tables hold the same rows on every machine. A few choices in the script matter for what follows:

  • Autovacuum is switched off for these two tables. Autovacuum is a background worker that cleans tables and refreshes their statistics on its own. If it ran between two steps it would quietly change the plans you are studying. The documentation describes the setting as “Enables or disables the autovacuum daemon for a particular table.” You will switch it back on in Step 14. Do not do this to tables you care about.
  • The order statuses are skewed: most orders are delivered and very few are cancelled. Real data looks like this, and the skew decides whether an index helps.
  • One customer is enormous. Every 41st order belongs to customer 7, so customer 7 has about 48,800 orders while a typical customer has about 20.
  • ANALYZE collects statistics. Without them the planner would have to guess how many rows match each condition.

The script ends with two summary queries. This is what I saw:

  relname  | estimated_rows | pages |  size
-----------+----------------+-------+---------
 customers |         100000 |   834 | 6672 kB
 orders    |        2000000 | 16493 | 129 MB
(2 rows)

  status   |    n    | pct
-----------+---------+------
 delivered | 1760000 | 88.0
 shipped   |  160000 |  8.0
 pending   |   78000 |  3.9
 cancelled |    2000 |  0.1
(4 rows)

The orders table occupies 16,493 pages and 129 MB. Confirm the page size and you will see why the numbers line up (16,493 pages of 8,192 bytes is about 129 MiB):

SHOW block_size;
 block_size
------------
 8192
(1 row)

How to check this step worked: estimated_rows for orders should read 2000000 and the percentages should match the second table. If estimated_rows shows 0, the ANALYZE lines did not run.

Step 2: Find the query worth fixing with pg_stat_statements

Before you tune a query, make sure it is the right one. A query that takes 50 ms but runs a million times a day costs the server far more time than a 3-second report that runs once a week, and you cannot see that from a single plan. The pg_stat_statements extension keeps a running total for every distinct statement the server executes.

Turn the extension on

The documentation says: “The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf, because it requires additional shared memory. This means that a server restart is needed to add or remove the module.” Add this line to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Restart the server the way your installation normally does it. I stopped mine with pg_ctl stop and started it again with the setting on the command line (-o "-c shared_preload_libraries=pg_stat_statements"), which has the same effect. Then create the extension inside the shop database:

CREATE EXTENSION pg_stat_statements;

Generate some traffic

A real application would produce the workload. For this lab, run the script below, which pretends to be one: 40 lookups of a customer’s ten most recent orders, 10 counts of cancelled orders, 60 look-ups by e-mail address, and 2,000 look-ups by order id. The first line clears the statistics so the data load does not dominate the list. \gexec runs each row that the query returns as a statement of its own, and \o NUL throws the results away. That is the Windows spelling; on Linux or macOS the equivalent is \o /dev/null, which I did not run.

SELECT pg_stat_statements_reset() IS NOT NULL AS reset_done;

-- Pretend to be the application: run four kinds of query many times (results are thrown away).
\o NUL
SELECT format('SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = %s ORDER BY created_at DESC LIMIT 10', 1 + (g * 37) % 100000) FROM generate_series(1, 40) AS g \gexec
SELECT format('SELECT count(*) FROM orders WHERE status = %L', 'cancelled') FROM generate_series(1, 10) AS g \gexec
SELECT format('SELECT id, country FROM customers WHERE lower(email) = %L', lower('User' || (1 + (g * 61) % 100000) || '@example.com')) FROM generate_series(1, 60) AS g \gexec
SELECT format('SELECT status, total_cents FROM orders WHERE id = %s', 1 + (g * 997) % 2000000) FROM generate_series(1, 2000) AS g \gexec
\o

Rank the queries by total time

SELECT calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND query NOT ILIKE '%pg_stat_statements%'
  AND query NOT ILIKE '%generate_series%'
ORDER BY total_exec_time DESC
LIMIT 4;

The output:

 calls | total_ms | mean_ms |                                                      query
-------+----------+---------+-----------------------------------------------------------------------------------------------------------------
    40 |     2114 |   52.84 | SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT $2
    10 |      630 |   63.00 | SELECT count(*) FROM orders WHERE status = $1
    60 |      522 |    8.71 | SELECT id, country FROM customers WHERE lower(email) = $1
  2000 |       63 |    0.03 | SELECT status, total_cents FROM orders WHERE id = $1
(4 rows)

Read it from the left. calls is how often the statement ran, total_ms is the time the server spent on it in total, and mean_ms is the average per call. The extension replaced the constants with $1 and $2, which is why the 40 customer lookups appear as one statement. The query for a customer’s ten most recent orders used 2,114 ms of server time, far more than anything else, so it is the one to fix. Notice that the count of cancelled orders has the highest average (63.00 ms) but only 10 calls, and the id look-up runs 2,000 times at 0.03 ms each and costs almost nothing. Sort by total time to see where the server’s time goes, and by mean time to find statements that are slow every single time. The second and third rows are close in total time (630 ms and 522 ms here), and on another run of mine they traded places, so do not read anything into their order.

Gotcha: the view covers every database on the server, which is why the query filters on dbid. Statements that mention pg_stat_statements or generate_series are left out so that the report does not list itself.

Step 3: Ask the planner for its plan with EXPLAIN

Now look at the winner. EXPLAIN followed by a statement asks PostgreSQL to plan the statement and print the plan without running it. I ask for the plan twice. The first time uses default settings, and the second turns off parallel workers for this session, a setting that lasts until you disconnect:

EXPLAIN
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 10;

SET max_parallel_workers_per_gather = 0;

EXPLAIN
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 10;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Limit  (cost=27912.22..27912.25 rows=10 width=29)
   ->  Sort  (cost=27912.22..27912.27 rows=21 width=29)
         Sort Key: created_at DESC
         ->  Gather  (cost=1000.00..27911.77 rows=21 width=29)
               Workers Planned: 2
               ->  Parallel Seq Scan on orders  (cost=0.00..26909.67 rows=9 width=29)
                     Filter: (customer_id = 4242)
(7 rows)

                               QUERY PLAN
------------------------------------------------------------------------
 Limit  (cost=41493.45..41493.48 rows=10 width=29)
   ->  Sort  (cost=41493.45..41493.51 rows=21 width=29)
         Sort Key: created_at DESC
         ->  Seq Scan on orders  (cost=0.00..41493.00 rows=21 width=29)
               Filter: (customer_id = 4242)
(5 rows)

The first plan contains a Gather node and a Parallel Seq Scan with Workers Planned: 2. (Your plan may instead put a Gather Merge above the Sort, as mine did in other runs of this same lab, probably because the planner’s row estimate differs slightly from one ANALYZE sample to the next.) For a large table PostgreSQL can split a scan between worker processes. That is useful in production but makes a first plan harder to read, so every plan from here on uses the serial version, the second one. Keep that SET in force for the whole tutorial, and run it again if you reconnect.

Reading a plan

A plan is a tree of nodes, and the indentation shows the tree. The documentation says: “Nodes at the bottom level of the tree are scan nodes: they return raw rows from a table.” A line that starts with -> is a child of the line above it, and rows flow upward: the most indented node produces rows, its parent processes them, and so on up to the top. Read this plan from the bottom up:

  1. Seq Scan on orders reads the table from start to finish and keeps rows where customer_id = 4242 (the Filter line).
  2. Sort orders the surviving rows by created_at DESC.
  3. Limit returns the first 10 of them.

Each node carries (cost=0.00..41493.00 rows=21 width=29). The first cost number is the startup cost, the work done before the first row can be returned. The second is the total cost of returning every row. rows=21 is the planner’s estimate of how many rows the node produces, and width=29 is the estimated average row size in bytes. The documentation is blunt about the unit: “The costs are measured in arbitrary units determined by the planner’s cost parameters.” Cost numbers compare plans with each other. They are not milliseconds.

They are not magic either. You can reproduce 41,493.00 yourself from three planner settings:

SELECT name, setting
FROM pg_settings
WHERE name IN ('seq_page_cost', 'cpu_tuple_cost', 'cpu_operator_cost')
ORDER BY name;
       name        | setting
-------------------+---------
 cpu_operator_cost | 0.0025
 cpu_tuple_cost    | 0.01
 seq_page_cost     | 1
(3 rows)

A sequential scan costs one seq_page_cost per page read, one cpu_tuple_cost per row, and, because there is a filter, one cpu_operator_cost per row to evaluate it. With the 16,493 pages and 2,000,000 rows from Step 1 that is 16,493 + 2,000,000 × 0.01 + 2,000,000 × 0.0025 = 16,493 + 20,000 + 5,000 = 41,493. The planner is doing arithmetic on the table’s statistics, nothing more. The documentation’s own example does a similar sum for a smaller table.

Gotcha: plain EXPLAIN runs nothing, so it shows only the planner’s opinion. A plan can look perfectly reasonable and still be slow if that opinion is wrong. To find out, you have to run the query.

Step 4: Run it for real with EXPLAIN ANALYZE

Adding ANALYZE makes PostgreSQL execute the statement and report what actually happened next to what it expected. Run it twice and keep the second result: the first run may read pages from disk that the second finds in memory.

EXPLAIN ANALYZE
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 10;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=41493.45..41493.48 rows=10 width=29) (actual time=77.373..77.374 rows=10.00 loops=1)
   Buffers: shared hit=13244 read=3252 written=9
   ->  Sort  (cost=41493.45..41493.51 rows=21 width=29) (actual time=77.371..77.371 rows=10.00 loops=1)
         Sort Key: created_at DESC
         Sort Method: quicksort  Memory: 26kB
         Buffers: shared hit=13244 read=3252 written=9
         ->  Seq Scan on orders  (cost=0.00..41493.00 rows=21 width=29) (actual time=4.921..77.330 rows=20.00 loops=1)
               Filter: (customer_id = 4242)
               Rows Removed by Filter: 1999980
               Buffers: shared hit=13241 read=3252 written=9
 Planning:
   Buffers: shared hit=94
 Planning Time: 0.529 ms
 Execution Time: 77.407 ms
(14 rows)

Reading each part of the output

Every node now has a second set of parentheses with the measured numbers. Here is what each part means.

Timing and row counts

actual time=4.921..77.330 on the scan is the time in milliseconds until the first row was produced and until the last one was. rows=20.00 is how many rows the node really returned, and loops=1 says the node ran once. PostgreSQL 18 prints row counts with two decimals. The figure is an average per loop, so it can be fractional when a node runs several times. The documentation explains the relationship: “the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution.” To get the total work of a node, multiply by loops. You will need that in Step 9.

Compare rows=21 (estimate) with rows=20.00 (actual). They are close, and that is the good case. The documentation puts this check first: “The thing that’s usually most important to look for is whether the estimated row counts are reasonably close to reality.”

The line that gives the problem away

Rows Removed by Filter: 1999980 means the scan read 2,000,000 rows and threw away all but 20. That is the whole story of this query. To find 20 rows it looked at every row in the table.

Buffers

As of version 18, EXPLAIN ANALYZE prints buffer counts by default. The documentation says: “Buffers information is automatically included when ANALYZE is used.” shared hit=13241 is the number of pages found in PostgreSQL’s own cache, and read=3252 is the number it had to request from the operating system, which may have answered from its cache rather than the disk. Add them: 13,241 + 3,252 = 16,493, exactly the page count of the table. The total of hit and read is a steadier measure of effort than a time: how it splits between the two depends on what happens to be cached, but the total does not. written=9 counts dirty pages the query itself had to write out to make room, a leftover from loading the data, and is not a sign of trouble.

Planning and execution time

The last two lines separate the time spent choosing the plan (0.529 ms) from the time spent running it (77.407 ms). For a statement that runs often, a planning time close to the execution time is worth noticing. Here it is irrelevant. You may notice that some plans end with a Planning: block that lists buffers and others do not. The documentation says that “In text format, only non-zero values are printed.” so the block appears only when planning had to read at least one page. I ran every step in a fresh psql session, so my plans show it more often than a long session will.

Verdict for this query: it reads all 16,493 pages to return 20 rows, in 77 ms. An index that lets PostgreSQL go straight to customer 4242 should fix exactly that.

Step 5: Use EXPLAIN ANALYZE safely on statements that change data

Before adding the index, one warning that has cost people real data. EXPLAIN ANALYZE does not just describe an UPDATE or DELETE, it performs it. The documentation says so plainly: “Keep in mind that the statement is actually executed when the ANALYZE option is used.” Its recommended pattern is to wrap the statement in a transaction that you roll back. Watch it happen:

SELECT id, status FROM orders WHERE id = 1;

BEGIN;

EXPLAIN ANALYZE
UPDATE orders SET status = 'cancelled' WHERE id = 1;

-- Inside the same transaction: the statement really ran.
SELECT id, status FROM orders WHERE id = 1;

ROLLBACK;

SELECT id, status FROM orders WHERE id = 1;
 id | status
----+---------
  1 | pending
(1 row)

                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Update on orders  (cost=0.43..8.45 rows=0 width=0) (actual time=0.252..0.252 rows=0.00 loops=1)
   Buffers: shared hit=16 read=3 dirtied=1
   ->  Index Scan using orders_pkey on orders  (cost=0.43..8.45 rows=1 width=38) (actual time=0.009..0.009 rows=1.00 loops=1)
         Index Cond: (id = 1)
         Index Searches: 1
         Buffers: shared hit=4
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.095 ms
 Execution Time: 0.524 ms
(10 rows)

 id |  status
----+-----------
  1 | cancelled
(1 row)

 id | status
----+---------
  1 | pending
(1 row)

The first SELECT shows order 1 as pending. The EXPLAIN ANALYZE UPDATE printed a plan, and the SELECT after it, still inside the transaction, shows cancelled: the update really ran. ROLLBACK undid it, and the last SELECT shows pending again. Without the BEGIN and ROLLBACK the order would have stayed cancelled. The plan itself has a new line worth knowing, Index Searches: 1, which is the version 18 counter of how many times the index was searched. It will matter in Step 9 and Step 11.

Step 6: Add an index and watch the plan change

An index lets PostgreSQL jump to the rows for one customer instead of reading the whole table. Create one on customer_id:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

Now run the statement from Step 4 again, the same EXPLAIN ANALYZE on customer 4242, and compare:

                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=87.06..87.08 rows=10 width=29) (actual time=0.121..0.122 rows=10.00 loops=1)
   Buffers: shared hit=26
   ->  Sort  (cost=87.06..87.11 rows=21 width=29) (actual time=0.120..0.120 rows=10.00 loops=1)
         Sort Key: created_at DESC
         Sort Method: quicksort  Memory: 26kB
         Buffers: shared hit=26
         ->  Bitmap Heap Scan on orders  (cost=4.59..86.60 rows=21 width=29) (actual time=0.029..0.098 rows=20.00 loops=1)
               Recheck Cond: (customer_id = 4242)
               Heap Blocks: exact=20
               Buffers: shared hit=23
               ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.58 rows=21 width=0) (actual time=0.014..0.014 rows=20.00 loops=1)
                     Index Cond: (customer_id = 4242)
                     Index Searches: 1
                     Buffers: shared hit=3
 Planning:
   Buffers: shared hit=118
 Planning Time: 0.576 ms
 Execution Time: 0.151 ms
(18 rows)

The plan changed shape. The table scan has been replaced by two nodes. Bitmap Index Scan on orders_customer_id_idx searches the index (Index Searches: 1) and collects the locations of the 20 matching rows. The Bitmap Heap Scan above it then reads those rows from the table. The documentation explains the two levels: “The reason for using two plan levels is that the upper plan node sorts the row locations identified by the index into physical order before reading them, to minimize the cost of separate fetches.” Heap Blocks: exact=20 says the 20 rows lived on 20 different pages.

The effect is large. The statement went from 77.407 ms to 0.151 ms, about 500 times faster, and from 16,496 buffers to 26. The Sort is still there, because the bitmap scan returns rows in table order rather than date order.

The index did not fix every customer

Run the same query for customer 7, the enormous account:

EXPLAIN ANALYZE
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 7
ORDER BY created_at DESC
LIMIT 10;
                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=18730.98..18731.01 rows=10 width=29) (actual time=79.605..79.607 rows=10.00 loops=1)
   Buffers: shared hit=7798 read=8742 written=3136
   ->  Sort  (cost=18730.98..18853.15 rows=48867 width=29) (actual time=79.603..79.604 rows=10.00 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 26kB
         Buffers: shared hit=7798 read=8742 written=3136
         ->  Bitmap Heap Scan on orders  (cost=571.15..17674.98 rows=48867 width=29) (actual time=3.846..75.112 rows=48800.00 loops=1)
               Recheck Cond: (customer_id = 7)
               Heap Blocks: exact=16493
               Buffers: shared hit=7795 read=8742 written=3136
               ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..558.93 rows=48867 width=0) (actual time=2.367..2.367 rows=48800.00 loops=1)
                     Index Cond: (customer_id = 7)
                     Index Searches: 1
                     Buffers: shared hit=2 read=42 written=1
 Planning:
   Buffers: shared hit=118
 Planning Time: 0.579 ms
 Execution Time: 79.719 ms
(18 rows)

This is still slow: 79.719 ms. The index did its job and found customer 7’s 48,800 rows quickly, but the bitmap scan then had to fetch them all (Heap Blocks: exact=16493, every page in the table, 16,540 buffers) so that Sort could keep the 10 newest, which is what Sort Method: top-N heapsort means. The index helps find rows. It does not help the database get the newest rows first.

Step 7: Make the index match the ORDER BY

An index stores its entries in sorted order, and you can choose that order. Put the column you filter by with equality first and the column you sort by second:

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);

Entries for each customer are now stored newest first, so the ten newest orders for a customer are the first ten entries in that customer’s range. Run the query for customer 4242 again:

                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..42.51 rows=10 width=29) (actual time=0.017..0.052 rows=10.00 loops=1)
   Buffers: shared hit=13
   ->  Index Scan using orders_customer_created_idx on orders  (cost=0.43..88.79 rows=21 width=29) (actual time=0.015..0.050 rows=10.00 loops=1)
         Index Cond: (customer_id = 4242)
         Index Searches: 1
         Buffers: shared hit=13
 Planning:
   Buffers: shared hit=138
 Planning Time: 0.635 ms
 Execution Time: 0.074 ms
(10 rows)

Two nodes remain, Limit and Index Scan. The Sort is gone because the index already delivers rows in the right order, and the scan stops after 10 rows (rows=10.00). It touched 13 buffers and took 0.074 ms. The same query for customer 7 is where the difference is dramatic:

                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..14.26 rows=10 width=29) (actual time=0.019..0.030 rows=10.00 loops=1)
   Buffers: shared hit=7
   ->  Index Scan using orders_customer_created_idx on orders  (cost=0.43..67583.60 rows=48867 width=29) (actual time=0.017..0.028 rows=10.00 loops=1)
         Index Cond: (customer_id = 7)
         Index Searches: 1
         Buffers: shared hit=7
 Planning:
   Buffers: shared hit=138
 Planning Time: 0.615 ms
 Execution Time: 0.053 ms
(10 rows)

0.053 ms and 7 buffers, down from 79.719 ms and 16,540. Do not be alarmed by rows=48867 in the estimate for the Index Scan against rows=10.00 in the actual numbers. The node was asked to stop early, so the estimate describes a full scan that never happened. The Limit node above it has the realistic cost, 0.43..14.26, because the planner interpolates between the endpoint costs. The same composite-index idea is what makes keyset pagination fast, which I covered in an earlier tutorial.

Drop the index you no longer need

The new index starts with customer_id, so it can answer anything the single-column index could. Keeping both costs disk space and slows every write. Look at the sizes, then drop the older one:

SELECT indexrelid::regclass AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index
WHERE indrelid = 'orders'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;

DROP INDEX orders_customer_id_idx;
            index            | size
-----------------------------+-------
 orders_customer_created_idx | 60 MB
 orders_pkey                 | 43 MB
 orders_customer_id_idx      | 15 MB
(3 rows)

That frees 15 MB and removes one index to maintain on every insert. I re-ran the Step 4 statement afterwards and the plan was unchanged.

Step 8: Understand when the planner ignores an index

An index is not always the right tool. To see why, add one on status and count orders with three different statuses, whose shares of the table are 0.1%, 8% and 88%:

CREATE INDEX orders_status_idx ON orders (status);

A rare value: 0.1% of rows

EXPLAIN ANALYZE
SELECT count(*) FROM orders WHERE status = 'cancelled';
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3053.77..3053.78 rows=1 width=8) (actual time=3.864..3.864 rows=1.00 loops=1)
   Buffers: shared hit=2004
   ->  Index Only Scan using orders_status_idx on orders  (cost=0.43..3048.61 rows=2067 width=0) (actual time=0.059..3.795 rows=2000.00 loops=1)
         Index Cond: (status = 'cancelled'::text)
         Heap Fetches: 2000
         Index Searches: 1
         Buffers: shared hit=2004
 Planning:
   Buffers: shared hit=110
 Planning Time: 0.648 ms
 Execution Time: 3.937 ms
(11 rows)

The planner uses the index, and because count(*) needs no column values it chose an Index Only Scan. Ignore Heap Fetches: 2000 for now. Step 12 explains it. This ran in 3.937 ms and touched 2,004 buffers.

A middling value: 8% of rows

EXPLAIN ANALYZE
SELECT count(*) FROM orders WHERE status = 'shipped';
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=20615.25..20615.26 rows=1 width=8) (actual time=52.298..52.299 rows=1.00 loops=1)
   Buffers: shared hit=10095 read=6536 written=5
   ->  Bitmap Heap Scan on orders  (cost=1763.25..20222.08 rows=157267 width=0) (actual time=5.643..47.982 rows=160000.00 loops=1)
         Recheck Cond: (status = 'shipped'::text)
         Heap Blocks: exact=16493
         Buffers: shared hit=10095 read=6536 written=5
         ->  Bitmap Index Scan on orders_status_idx  (cost=0.00..1723.93 rows=157267 width=0) (actual time=4.162..4.163 rows=160000.00 loops=1)
               Index Cond: (status = 'shipped'::text)
               Index Searches: 1
               Buffers: shared hit=1 read=137
 Planning:
   Buffers: shared hit=110
 Planning Time: 0.587 ms
 Execution Time: 52.483 ms
(14 rows)

Here is the surprise. 8% sounds selective, but Heap Blocks: exact=16493 shows the bitmap scan visited every page in the table. The 160,000 shipped orders are spread through the whole table, about ten per page, so an index cannot let PostgreSQL skip any page. It took 52.483 ms.

A common value: 88% of rows

EXPLAIN ANALYZE
SELECT count(*) FROM orders WHERE status = 'delivered';
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=45900.00..45900.01 rows=1 width=8) (actual time=159.483..159.483 rows=1.00 loops=1)
   Buffers: shared hit=15728 read=765
   ->  Seq Scan on orders  (cost=0.00..41493.00 rows=1762800 width=0) (actual time=0.020..115.335 rows=1760000.00 loops=1)
         Filter: (status = 'delivered'::text)
         Rows Removed by Filter: 240000
         Buffers: shared hit=15728 read=765
 Planning:
   Buffers: shared hit=110
 Planning Time: 0.562 ms
 Execution Time: 159.561 ms
(10 rows)

Now the planner ignores the index and reads the table sequentially, as it should. Using the index for 1.76 million rows would mean reading about 1.76 million index entries (roughly 1,500 index pages, as the forced plan below shows) on top of reading every table page. Summing up the three:

Status Share of rows Plan chosen Buffers touched Execution time
cancelled 0.1% Index Only Scan 2,004 3.937 ms
shipped 8% Bitmap Heap Scan 16,631 52.483 ms
delivered 88% Seq Scan 16,493 159.561 ms

What happens if you force the index?

PostgreSQL has switches that discourage particular plan types, mainly for experiments. The documentation says of enable_seqscan: “It is impossible to suppress sequential scans entirely, but turning this variable off discourages the planner from using one if there are other methods available.” Use it to see the road not taken:

SET enable_seqscan = off;

EXPLAIN ANALYZE
SELECT count(*) FROM orders WHERE status = 'delivered';

RESET enable_seqscan;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=62665.13..62665.14 rows=1 width=8) (actual time=187.856..187.857 rows=1.00 loops=1)
   Buffers: shared hit=5979 read=12022 written=1009
   ->  Bitmap Heap Scan on orders  (cost=19730.13..58258.13 rows=1762800 width=0) (actual time=33.758..142.451 rows=1760000.00 loops=1)
         Recheck Cond: (status = 'delivered'::text)
         Heap Blocks: exact=16493
         Buffers: shared hit=5979 read=12022 written=1009
         ->  Bitmap Index Scan on orders_status_idx  (cost=0.00..19289.43 rows=1762800 width=0) (actual time=32.322..32.322 rows=1760000.00 loops=1)
               Index Cond: (status = 'delivered'::text)
               Index Searches: 1
               Buffers: shared hit=1 read=1507 written=414
 Planning:
   Buffers: shared hit=110
 Planning Time: 0.668 ms
 Execution Time: 188.009 ms
(14 rows)

The forced bitmap plan has an estimated cost of 62,665 against 45,900 for the sequential scan, about 37% higher, and in this single run it took 188.009 ms against 159.561 ms. A single run can mislead, so here is the same count five times each way, using \timing:

\o NUL
\o
\echo planner choice, five runs:
\o NUL
\timing on
SELECT count(*) FROM orders WHERE status = 'delivered';
SELECT count(*) FROM orders WHERE status = 'delivered';
SELECT count(*) FROM orders WHERE status = 'delivered';
SELECT count(*) FROM orders WHERE status = 'delivered';
SELECT count(*) FROM orders WHERE status = 'delivered';
\timing off
SET enable_seqscan = off;
\o
\echo index forced with enable_seqscan = off, five runs:
\o NUL
\timing on
SELECT count(*) FROM orders WHERE status = 'delivered';
SELECT count(*) FROM orders WHERE status = 'delivered';
SELECT count(*) FROM orders WHERE status = 'delivered';
SELECT count(*) FROM orders WHERE status = 'delivered';
SELECT count(*) FROM orders WHERE status = 'delivered';
\timing off
RESET enable_seqscan;
\o
planner choice, five runs:
Time: 107.752 ms
Time: 87.188 ms
Time: 86.916 ms
Time: 85.879 ms
Time: 86.182 ms
index forced with enable_seqscan = off, five runs:
Time: 103.746 ms
Time: 93.282 ms
Time: 84.921 ms
Time: 94.085 ms
Time: 89.552 ms

The median is 86.9 ms for the planner’s own choice and 93.3 ms for the forced index. The planner was right, but only by about 7%, even though its cost estimate said 37%. The reason is that my whole table sits in the operating system’s cache, so the page fetches that the cost model charges heavily for were cheap. This is the practical meaning of the rule that cost is not milliseconds. On a server where the table is not cached the gap would be wider, which I did not test. The lesson is the order of work: when a plan looks wrong, check the row estimates first (Step 9), and never leave an enable_ switch off on a production server.

Step 9: Fix wrong row estimates with ANALYZE

The planner chooses plans from its estimates, and the estimates come from statistics. See what it knows about status:

SELECT n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
 n_distinct |           most_common_vals            |              most_common_freqs
------------+---------------------------------------+----------------------------------------------
          4 | {delivered,shipped,pending,cancelled} | {0.8814,0.07863333,0.038933333,0.0010333334}
(1 row)

The planner knows there are four distinct values and how common each is. The frequencies (0.8814 for delivered) are close to the true 88.0% but not equal, because ANALYZE reads a sample. The documentation says the entries are “always approximate even when freshly updated”. That is fine. Good estimates only need to be roughly right.

Problems start when the statistics are stale. Suppose a nightly import adds 60,000 refunded orders, a status the planner has never seen, and nobody runs ANALYZE:

-- A nightly import adds 60,000 refunded orders. Nobody runs ANALYZE afterwards.
INSERT INTO orders (customer_id, status, total_cents, created_at)
SELECT 1 + (i * 7919) % 100000,
       'refunded',
       500 + (i * 37) % 49500,
       timestamptz '2024-01-01 00:00:00+00' + i * interval '31 seconds'
FROM generate_series(2000001::bigint, 2060000) AS i;

Now run a report that joins orders to customers for the refunded ones:

EXPLAIN ANALYZE
SELECT c.country, count(*) AS refunds, sum(o.total_cents) AS refunded_cents
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'refunded'
GROUP BY c.country
ORDER BY refunded_cents DESC;
                                                                        QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=16.80..16.80 rows=1 width=19) (actual time=57.615..57.616 rows=10.00 loops=1)
   Sort Key: (sum(o.total_cents)) DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=180557
   ->  GroupAggregate  (cost=16.77..16.79 rows=1 width=19) (actual time=53.804..57.600 rows=10.00 loops=1)
         Group Key: c.country
         Buffers: shared hit=180554
         ->  Sort  (cost=16.77..16.77 rows=1 width=7) (actual time=53.385..54.688 rows=60000.00 loops=1)
               Sort Key: c.country
               Sort Method: quicksort  Memory: 2943kB
               Buffers: shared hit=180554
               ->  Nested Loop  (cost=0.72..16.76 rows=1 width=7) (actual time=0.032..48.061 rows=60000.00 loops=1)
                     Buffers: shared hit=180551
                     ->  Index Scan using orders_status_idx on orders o  (cost=0.43..8.45 rows=1 width=8) (actual time=0.018..4.417 rows=60000.00 loops=1)
                           Index Cond: (status = 'refunded'::text)
                           Index Searches: 1
                           Buffers: shared hit=551
                     ->  Index Scan using customers_pkey on customers c  (cost=0.29..8.31 rows=1 width=7) (actual time=0.001..0.001 rows=1.00 loops=60000)
                           Index Cond: (id = o.customer_id)
                           Index Searches: 60000
                           Buffers: shared hit=180000
 Planning:
   Buffers: shared hit=280
 Planning Time: 1.072 ms
 Execution Time: 57.799 ms
(25 rows)

Look at the two nodes at the bottom. The planner expected rows=1 refunded order and found rows=60000.00. Believing there was only one row, it chose a Nested Loop, which takes each row from the first input and looks up its partner in the second. That is excellent for one row and terrible for 60,000. The inner Index Scan using customers_pkey shows loops=60000, so it ran 60,000 times and Index Searches: 60000 confirms it. Each run costs a tiny actual time=0.001, but the documentation tells you to multiply by the loops value, and the buffers say the same thing: 180,557 of them for a query that returns 10 rows, in 57.799 ms.

Refresh the statistics and run the report again:

ANALYZE orders;
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=22152.27..22152.29 rows=10 width=19) (actual time=27.015..27.016 rows=10.00 loops=1)
   Sort Key: (sum(o.total_cents)) DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=1388
   ->  HashAggregate  (cost=22152.00..22152.10 rows=10 width=19) (actual time=26.987..26.989 rows=10.00 loops=1)
         Group Key: c.country
         Batches: 1  Memory Usage: 32kB
         Buffers: shared hit=1385
         ->  Hash Join  (cost=3771.65..21692.11 rows=61319 width=7) (actual time=12.824..22.759 rows=60000.00 loops=1)
               Hash Cond: (o.customer_id = c.id)
               Buffers: shared hit=1385
               ->  Bitmap Heap Scan on orders o  (cost=687.65..18447.14 rows=61319 width=8) (actual time=0.737..3.895 rows=60000.00 loops=1)
                     Recheck Cond: (status = 'refunded'::text)
                     Heap Blocks: exact=501
                     Buffers: shared hit=551
                     ->  Bitmap Index Scan on orders_status_idx  (cost=0.00..672.32 rows=61319 width=0) (actual time=0.693..0.693 rows=60000.00 loops=1)
                           Index Cond: (status = 'refunded'::text)
                           Index Searches: 1
                           Buffers: shared hit=50
               ->  Hash  (cost=1834.00..1834.00 rows=100000 width=7) (actual time=11.850..11.851 rows=100000.00 loops=1)
                     Buckets: 131072  Batches: 1  Memory Usage: 4931kB
                     Buffers: shared hit=834
                     ->  Seq Scan on customers c  (cost=0.00..1834.00 rows=100000 width=7) (actual time=0.009..5.917 rows=100000.00 loops=1)
                           Buffers: shared hit=834
 Planning:
   Buffers: shared hit=280
 Planning Time: 0.973 ms
 Execution Time: 27.346 ms
(28 rows)

The planner now estimates 61,319 refunded orders (the truth is 60,000) and switches to a Hash Join, which builds an in-memory hash table from one input and streams the other through it. That is usually the better choice when both inputs are large. The query finishes in 27.346 ms using 1,388 buffers, about 130 times fewer than before.

This gives you a habit that pays off constantly: at every node, compare rows= in the first parentheses with rows= in the second. When they differ by a factor of ten or more, and no Limit node explains it (see Step 7), suspect missing or outdated statistics first, and look at the lowest such node first. In normal operation autovacuum runs ANALYZE for you after enough rows change. After a bulk load, do not wait for it, and run ANALYZE on the table yourself.

Step 10: Do not hide the column inside a function

An index on a column is an index on the column’s values, not on anything computed from them. Create an ordinary index on the customers’ e-mail addresses, then search the way an application that ignores letter case would:

CREATE INDEX customers_email_idx ON customers (email);
EXPLAIN ANALYZE
SELECT id, country FROM customers WHERE lower(email) = '[email protected]';
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on customers  (cost=0.00..2334.00 rows=500 width=7) (actual time=0.481..9.372 rows=1.00 loops=1)
   Filter: (lower(email) = '[email protected]'::text)
   Rows Removed by Filter: 99999
   Buffers: shared hit=834
 Planning:
   Buffers: shared hit=81
 Planning Time: 0.512 ms
 Execution Time: 9.435 ms
(8 rows)

The index exists, yet the plan is a Seq Scan that removes 99,999 rows. The query asks about lower(email), and the index stores email. They are different values, so the index cannot help. The fix is an expression index, which stores the result of the function:

CREATE INDEX customers_email_lower_idx ON customers (lower(email));

Run the same EXPLAIN ANALYZE again:

                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on customers  (cost=16.29..779.05 rows=500 width=7) (actual time=0.033..0.034 rows=1.00 loops=1)
   Recheck Cond: (lower(email) = '[email protected]'::text)
   Heap Blocks: exact=1
   Buffers: shared hit=4
   ->  Bitmap Index Scan on customers_email_lower_idx  (cost=0.00..16.17 rows=500 width=0) (actual time=0.018..0.018 rows=1.00 loops=1)
         Index Cond: (lower(email) = '[email protected]'::text)
         Index Searches: 1
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=95
 Planning Time: 0.549 ms
 Execution Time: 0.111 ms
(12 rows)

The index is used now, but look at the estimate: rows=500 against an actual 1. The planner has no statistics about lower(email) yet, so it falls back on a generic guess. Gather statistics for the new expression and run the statement a third time:

ANALYZE customers;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using customers_email_lower_idx on customers  (cost=0.42..8.44 rows=1 width=7) (actual time=0.025..0.025 rows=1.00 loops=1)
   Index Cond: (lower(email) = '[email protected]'::text)
   Index Searches: 1
   Buffers: shared hit=4
 Planning:
   Buffers: shared hit=100
 Planning Time: 0.588 ms
 Execution Time: 0.096 ms
(8 rows)

The estimate is now rows=1 and the plan is a plain Index Scan in 0.096 ms, down from 9.435 ms. You can see where the new statistics live, a row in pg_stats that belongs to the index itself:

SELECT tablename, attname, n_distinct
FROM pg_stats
WHERE tablename = 'customers_email_lower_idx';
         tablename         | attname | n_distinct
---------------------------+---------+------------
 customers_email_lower_idx | lower   |         -1
(1 row)

So after creating an expression index, run ANALYZE on the table.

The same trap with a cast

Casting an indexed column is a function call on it too. The customers table has a signed_up_at timestamp. Index it, then ask for one day’s sign-ups two ways:

CREATE INDEX customers_signed_up_idx ON customers (signed_up_at);

-- The index is on signed_up_at, but this query asks about signed_up_at::date.
EXPLAIN ANALYZE
SELECT count(*) FROM customers WHERE signed_up_at::date = DATE '2024-03-15';

-- Same question as a half-open range on the bare column.
EXPLAIN ANALYZE
SELECT count(*) FROM customers
WHERE signed_up_at >= DATE '2024-03-15' AND signed_up_at < DATE '2024-03-16';
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2335.25..2335.26 rows=1 width=8) (actual time=4.342..4.343 rows=1.00 loops=1)
   Buffers: shared hit=834
   ->  Seq Scan on customers  (cost=0.00..2334.00 rows=500 width=0) (actual time=0.939..4.325 rows=288.00 loops=1)
         Filter: ((signed_up_at)::date = '2024-03-15'::date)
         Rows Removed by Filter: 99712
         Buffers: shared hit=834
 Planning:
   Buffers: shared hit=78 read=1
 Planning Time: 0.631 ms
 Execution Time: 4.383 ms
(10 rows)

                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=17.18..17.19 rows=1 width=8) (actual time=0.143..0.143 rows=1.00 loops=1)
   Buffers: shared hit=6 read=3
   ->  Index Only Scan using customers_signed_up_idx on customers  (cost=0.29..16.41 rows=306 width=0) (actual time=0.100..0.134 rows=288.00 loops=1)
         Index Cond: ((signed_up_at >= '2024-03-15'::date) AND (signed_up_at < '2024-03-16'::date))
         Heap Fetches: 288
         Index Searches: 1
         Buffers: shared hit=6 read=3
 Planning:
   Buffers: shared hit=10
 Planning Time: 0.083 ms
 Execution Time: 0.157 ms
(11 rows)

Both queries count the same 288 customers. The first applies ::date to the column, so the index is ignored and 99,712 rows are filtered out (4.383 ms). The second compares the bare column with a half-open range, so the index is used (0.157 ms). Whenever you can, rewrite a condition so that the indexed column stands alone on one side of the comparison.

Step 11: Multicolumn indexes and the version 18 skip scan

Suppose a report counts the orders created on one day, whatever their status, and you already have an index that starts with status:

CREATE INDEX orders_status_created_idx ON orders (status, created_at);
EXPLAIN ANALYZE
SELECT count(*) FROM orders
WHERE created_at >= timestamptz '2025-06-01 00:00:00+00'
  AND created_at <  timestamptz '2025-06-02 00:00:00+00';
                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=6891.00..6891.01 rows=1 width=8) (actual time=0.536..0.536 rows=1.00 loops=1)
   Buffers: shared hit=126
   ->  Index Only Scan using orders_status_created_idx on orders  (cost=0.43..6883.98 rows=2810 width=0) (actual time=0.088..0.453 rows=2787.00 loops=1)
         Index Cond: ((created_at >= '2025-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-06-02 00:00:00+00'::timestamp with time zone))
         Heap Fetches: 2787
         Index Searches: 10
         Buffers: shared hit=126
 Planning:
   Buffers: shared hit=134
 Planning Time: 0.673 ms
 Execution Time: 0.613 ms
(11 rows)

The query has no condition on status, the first column of the index. The version 18 release notes say skip scans allow multi-column indexes “to be used in more cases such as when there are no restrictions on the first or early indexed columns”, and this is one of those cases. The documentation describes the mechanism: “Skip scan works by generating a dynamic equality constraint internally, that matches every possible value in an index column.” In effect PostgreSQL runs the range search once for each distinct status. The result: 0.613 ms with Index Searches: 10. Without any helpful index the same query is a sequential scan:

DROP INDEX orders_status_created_idx;
                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=47900.03..47900.04 rows=1 width=8) (actual time=80.415..80.415 rows=1.00 loops=1)
   Buffers: shared hit=7215 read=9778
   ->  Seq Scan on orders  (cost=0.00..47893.00 rows=2810 width=0) (actual time=51.701..80.325 rows=2787.00 loops=1)
         Filter: ((created_at >= '2025-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-06-02 00:00:00+00'::timestamp with time zone))
         Rows Removed by Filter: 2057213
         Buffers: shared hit=7215 read=9778
 Planning:
   Buffers: shared hit=113
 Planning Time: 0.560 ms
 Execution Time: 80.497 ms
(10 rows)

That is 80.497 ms and 16,993 buffers, against 0.613 ms. For comparison, an index built for this query, on created_at alone, searches once:

CREATE INDEX orders_created_idx ON orders (created_at);
                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=122.65..122.66 rows=1 width=8) (actual time=0.406..0.406 rows=1.00 loops=1)
   Buffers: shared hit=35
   ->  Index Only Scan using orders_created_idx on orders  (cost=0.43..115.63 rows=2810 width=0) (actual time=0.053..0.326 rows=2787.00 loops=1)
         Index Cond: ((created_at >= '2025-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2025-06-02 00:00:00+00'::timestamp with time zone))
         Heap Fetches: 2787
         Index Searches: 1
         Buffers: shared hit=35
 Planning:
   Buffers: shared hit=128
 Planning Time: 0.617 ms
 Execution Time: 0.482 ms
(11 rows)

0.482 ms and Index Searches: 1. Skip scan is a pleasant fallback, not a substitute for designing the index for your query.

Why ten searches for five statuses?

The table has five distinct statuses now (cancelled, delivered, pending, refunded, shipped), yet the plan reports 10 searches. I measured what the counter does on scratch tables with 300,000 rows and an index on (a, b), filtering only on b:

CREATE TEMP TABLE skip3 AS SELECT i % 3 AS a, i AS b FROM generate_series(1, 300000) AS i;
CREATE TEMP TABLE skip5 AS SELECT i % 5 AS a, i AS b FROM generate_series(1, 300000) AS i;
CREATE TEMP TABLE skip5t AS SELECT 'v' || (i % 5) AS a, i AS b FROM generate_series(1, 300000) AS i;
CREATE TEMP TABLE skip8 AS SELECT i % 8 AS a, i AS b FROM generate_series(1, 300000) AS i;
CREATE INDEX ON skip3 (a, b);
CREATE INDEX ON skip5 (a, b);
CREATE INDEX ON skip5t (a, b);
CREATE INDEX ON skip8 (a, b);
ANALYZE skip3;
ANALYZE skip5;
ANALYZE skip5t;
ANALYZE skip8;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT count(*) FROM skip3 WHERE b BETWEEN 1000 AND 1100;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT count(*) FROM skip5 WHERE b BETWEEN 1000 AND 1100;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT count(*) FROM skip5t WHERE b BETWEEN 1000 AND 1100;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT count(*) FROM skip8 WHERE b BETWEEN 1000 AND 1100;

The four plans reported these Index Searches values:

Table Type of a Distinct values of a Index Searches
skip3 integer 3 4
skip5 integer 5 6
skip8 integer 8 9
skip5t text 5 10

With integers the count was one more than the number of distinct values. With the text column I tried, five values gave ten searches, twice the number. The documentation for extending B-tree operator classes lists an optional “skip support” function, and the system catalogs show which built-in operator families supply one:

SELECT opf.opfname AS operator_family, p.amproc AS skip_support_function
FROM pg_amproc AS p
JOIN pg_opfamily AS opf ON opf.oid = p.amprocfamily
JOIN pg_am AS am ON am.oid = opf.opfmethod
WHERE am.amname = 'btree' AND p.amprocnum = 6
ORDER BY 1, 2;
 operator_family | skip_support_function
-----------------+-----------------------
 bool_ops        | btboolskipsupport
 char_ops        | btcharskipsupport
 datetime_ops    | date_skipsupport
 datetime_ops    | timestamp_skipsupport
 datetime_ops    | timestamp_skipsupport
 integer_ops     | btint2skipsupport
 integer_ops     | btint4skipsupport
 integer_ops     | btint8skipsupport
 oid_ops         | btoidskipsupport
 uuid_ops        | uuid_skipsupport
(10 rows)

Integers, dates and timestamps, booleans, char, object ids and UUIDs have one. Text has none. My reading is that without skip support PostgreSQL needs an extra search to find the next distinct value before it can search inside it. The documentation I read does not say this in so many words, so treat that as my interpretation of the measurements.

The same page explains when skip scan applies: “This approach is generally only taken when there are so few distinct x values that the planner expects the scan to skip over most of the index.” It also warns: “If there are many distinct x values, then the entire index will have to be scanned, so in most cases the planner will prefer a sequential table scan over using the index.” My own run agrees. When the only multicolumn index available was the one on (customer_id, created_at), whose first column has 100,000 distinct values, the planner chose the sequential scan you saw above.

Step 12: Index-only scans, INCLUDE, and VACUUM

An index-only scan tries to answer a query from the index alone, without visiting the table. It needs every column the query uses to be in the index. Take a variation of the first query that also needs total_cents, which no index contains yet:

EXPLAIN ANALYZE
SELECT created_at, total_cents
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC;
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=87.10..87.15 rows=21 width=12) (actual time=0.120..0.121 rows=20.00 loops=1)
   Sort Key: created_at DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=27
   ->  Bitmap Heap Scan on orders  (cost=4.59..86.64 rows=21 width=12) (actual time=0.041..0.098 rows=20.00 loops=1)
         Recheck Cond: (customer_id = 4242)
         Heap Blocks: exact=20
         Buffers: shared hit=24
         ->  Bitmap Index Scan on orders_customer_created_idx  (cost=0.00..4.58 rows=21 width=0) (actual time=0.026..0.026 rows=20.00 loops=1)
               Index Cond: (customer_id = 4242)
               Index Searches: 1
               Buffers: shared hit=4
 Planning:
   Buffers: shared hit=165
 Planning Time: 0.696 ms
 Execution Time: 0.156 ms
(16 rows)

The index on (customer_id, created_at) finds the rows, but PostgreSQL has to visit the table for total_cents and then sort. Build an index that carries the extra column. INCLUDE adds a column to the index as a non-key column: the index can return its value, but the column is not used to search the index:

CREATE INDEX orders_customer_created_covering_idx
    ON orders (customer_id, created_at DESC) INCLUDE (total_cents);

Run the same query again:

                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=87.10..87.15 rows=21 width=12) (actual time=0.110..0.111 rows=20.00 loops=1)
   Sort Key: created_at DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=26
   ->  Bitmap Heap Scan on orders  (cost=4.59..86.64 rows=21 width=12) (actual time=0.032..0.088 rows=20.00 loops=1)
         Recheck Cond: (customer_id = 4242)
         Heap Blocks: exact=20
         Buffers: shared hit=23
         ->  Bitmap Index Scan on orders_customer_created_covering_idx  (cost=0.00..4.58 rows=21 width=0) (actual time=0.017..0.017 rows=20.00 loops=1)
               Index Cond: (customer_id = 4242)
               Index Searches: 1
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=183
 Planning Time: 0.746 ms
 Execution Time: 0.146 ms
(16 rows)

It is still a Bitmap Heap Scan with a Sort. The new index is not being used for an index-only scan. The reason is in the visibility map. The documentation says that an index-only scan checks, for each entry, whether the table page is known to contain only rows visible to every transaction, and that “If it’s not set, the heap entry must be visited to find out whether it’s visible, so no performance advantage is gained over a standard index scan.” The visibility map page adds: “Visibility map bits are only set by vacuum, but are cleared by any data-modifying operations on a page.” This table has never been vacuumed. Check the numbers, run VACUUM, and check again:

SELECT relpages, relallvisible FROM pg_class WHERE relname = 'orders';

VACUUM orders;

SELECT relpages, relallvisible FROM pg_class WHERE relname = 'orders';
 relpages | relallvisible
----------+---------------
    16993 |             0
(1 row)

 relpages | relallvisible
----------+---------------
    16993 |         16992
(1 row)

relallvisible counts the pages marked all-visible. It went from 0 of 16,993 to 16,992. Now run the query once more:

                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using orders_customer_created_covering_idx on orders  (cost=0.43..8.79 rows=21 width=12) (actual time=0.075..0.076 rows=20.00 loops=1)
   Index Cond: (customer_id = 4242)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=4
 Planning:
   Buffers: shared hit=183
 Planning Time: 0.755 ms
 Execution Time: 0.108 ms
(9 rows)

This is the plan you wanted: a single Index Only Scan with Heap Fetches: 0, no table visits and no Sort, in 0.108 ms. Now you can also explain the Heap Fetches: 2000 you saw in Step 8 and the Heap Fetches: 288 in Step 10. Both tables had never been vacuumed, so the scans had to check the table for every row. Heavy updates clear the bits again, and autovacuum normally restores them. Finally, the older composite index starts with the same two columns as the new one, so it is redundant:

DROP INDEX orders_customer_created_idx;

Step 13: Count what the indexes cost

Every index has to be updated on every insert and takes disk space, and it only pays off if queries use it. Measure the first two costs. The script below creates two scratch tables, one with only a primary key and one with the same four indexes as orders, and loads 200,000 rows into each three times:

-- Two scratch tables with the same columns: one with only a primary key, one with the same indexes as orders.
CREATE TABLE orders_bare (LIKE orders);
ALTER TABLE orders_bare ADD PRIMARY KEY (id);
CREATE TABLE orders_indexed (LIKE orders INCLUDING INDEXES);

\set rows 'SELECT i, 1 + (i * 7919) % 100000, ''delivered'', 500 + (i * 37) % 49500, timestamptz ''2024-01-01 00:00:00+00'' + i * interval ''31 seconds'' FROM generate_series(1::bigint, 200000) AS i'

-- Load 200,000 rows into each table three times (TRUNCATE between runs) and compare the times.
\echo primary key only:
\timing on
INSERT INTO orders_bare :rows;
\timing off
TRUNCATE orders_bare;
\timing on
INSERT INTO orders_bare :rows;
\timing off
TRUNCATE orders_bare;
\timing on
INSERT INTO orders_bare :rows;
\timing off

\echo same indexes as orders:
\timing on
INSERT INTO orders_indexed :rows;
\timing off
TRUNCATE orders_indexed;
\timing on
INSERT INTO orders_indexed :rows;
\timing off
TRUNCATE orders_indexed;
\timing on
INSERT INTO orders_indexed :rows;
\timing off

SELECT count(*) AS indexes_on_orders_indexed FROM pg_index WHERE indrelid = 'orders_indexed'::regclass;
DROP TABLE orders_bare, orders_indexed;
primary key only:
Time: 352.501 ms
Time: 349.594 ms
Time: 667.091 ms
same indexes as orders:
Time: 1010.412 ms (00:01.010)
Time: 1671.427 ms (00:01.671)
Time: 1596.341 ms (00:01.596)
 indexes_on_orders_indexed
---------------------------
                         4
(1 row)

With only a primary key the load took 350 to 667 ms. With four indexes it took 1,010 to 1,671 ms, around three to five times longer. The runs vary from one to the next, so I show all three. On disk, orders now has 182 MB of indexes (80, 44, 44 and 14 MB) beside a 133 MB table:

\d orders

SELECT indexrelid::regclass AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index
WHERE indrelid = 'orders'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;
                                    Table "public.orders"
   Column    |           Type           | Collation | Nullable |           Default
-------------+--------------------------+-----------+----------+------------------------------
 id          | bigint                   |           | not null | generated always as identity
 customer_id | integer                  |           | not null |
 status      | text                     |           | not null |
 total_cents | integer                  |           | not null |
 created_at  | timestamp with time zone |           | not null |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "orders_created_idx" btree (created_at)
    "orders_customer_created_covering_idx" btree (customer_id, created_at DESC) INCLUDE (total_cents)
    "orders_status_idx" btree (status)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)

                index                 | size
--------------------------------------+-------
 orders_customer_created_covering_idx | 80 MB
 orders_pkey                          | 44 MB
 orders_created_idx                   | 44 MB
 orders_status_idx                    | 14 MB
(4 rows)

PostgreSQL counts how often each index is used. The pg_stat_user_indexes view reports idx_scan, the “Number of index scans initiated on this index”:

SELECT indexrelname AS index,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;
                index                 | idx_scan | size
--------------------------------------+----------+-------
 orders_pkey                          |     4004 | 44 MB
 orders_customer_created_covering_idx |       44 | 80 MB
 orders_status_idx                    |       24 | 14 MB
 orders_created_idx                   |        2 | 44 MB
(4 rows)

The primary key is busy because of the 4,000 id look-ups in the two workload runs. orders_created_idx has been used twice, both times by my own experiments in Step 11. In a real system an index like that is a candidate for removal. Check over a long enough period, and ask whether a rarely run job such as a month-end report needs it, before you drop anything.

Step 14: Confirm the fix end to end, and clean up

Go back to where you started. Run the workload script from Step 2 again (it clears the statistics first), then the ranking query:

SELECT pg_stat_statements_reset() IS NOT NULL AS reset_done;

-- Pretend to be the application: run four kinds of query many times (results are thrown away).
\o NUL
SELECT format('SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = %s ORDER BY created_at DESC LIMIT 10', 1 + (g * 37) % 100000) FROM generate_series(1, 40) AS g \gexec
SELECT format('SELECT count(*) FROM orders WHERE status = %L', 'cancelled') FROM generate_series(1, 10) AS g \gexec
SELECT format('SELECT id, country FROM customers WHERE lower(email) = %L', lower('User' || (1 + (g * 61) % 100000) || '@example.com')) FROM generate_series(1, 60) AS g \gexec
SELECT format('SELECT status, total_cents FROM orders WHERE id = %s', 1 + (g * 997) % 2000000) FROM generate_series(1, 2000) AS g \gexec
\o
SELECT calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND query NOT ILIKE '%pg_stat_statements%'
  AND query NOT ILIKE '%generate_series%'
ORDER BY total_exec_time DESC
LIMIT 4;
 calls | total_ms | mean_ms |                                                      query
-------+----------+---------+-----------------------------------------------------------------------------------------------------------------
  2000 |       40 |    0.02 | SELECT status, total_cents FROM orders WHERE id = $1
    40 |        3 |    0.07 | SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT $2
    60 |        1 |    0.02 | SELECT id, country FROM customers WHERE lower(email) = $1
    10 |        1 |    0.06 | SELECT count(*) FROM orders WHERE status = $1
(4 rows)

Compare it with the ranking from Step 2:

Statement Calls Total before Total after
Ten most recent orders for a customer 40 2,114 ms 3 ms
Count of cancelled orders 10 630 ms 1 ms
Look-up by e-mail address 60 522 ms 1 ms
Look-up by order id 2,000 63 ms 40 ms

The four statements went from about 3.3 seconds of server time to about 45 milliseconds. The statement you started with fell from a 52.84 ms average to 0.07 ms. The busiest statement is now the id look-up, which was already cheap. That is what a finished tuning session looks like: the top of the list is a cheap query that is simply popular.

Put autovacuum back

Switch the autovacuum setting you changed in Step 1 back to its default, and check it:

SELECT relname, reloptions FROM pg_class WHERE relname IN ('customers', 'orders') ORDER BY relname;

ALTER TABLE orders    RESET (autovacuum_enabled);
ALTER TABLE customers RESET (autovacuum_enabled);

SELECT relname, reloptions FROM pg_class WHERE relname IN ('customers', 'orders') ORDER BY relname;
  relname  |         reloptions
-----------+----------------------------
 customers | {autovacuum_enabled=false}
 orders    | {autovacuum_enabled=false}
(2 rows)

  relname  | reloptions
-----------+------------
 customers |
 orders    |
(2 rows)

The empty reloptions column in the second result means both tables use the server defaults again. If you are finished with the lab, DROP DATABASE shop; removes everything.

Common mistakes, in one place

  • Running EXPLAIN ANALYZE on an UPDATE or DELETE without BEGIN and ROLLBACK (Step 5). It performs the change.
  • Reading cost as time (Steps 3 and 8). Cost compares plans. Milliseconds measure them.
  • Trusting one timing (Steps 4 and 8). Run the statement twice, and for close calls five times, and look at buffers as well.
  • Adding an index that does not match the query (Steps 6 and 7). Match the WHERE columns first and the ORDER BY column second.
  • Expecting an index to help with a common value (Step 8). Check how much of the table the condition matches.
  • Skipping ANALYZE after a bulk load or after creating an expression index (Steps 9 and 10).
  • Wrapping an indexed column in a function or cast (Step 10).
  • Expecting an index-only scan on a table nobody vacuums (Step 12).
  • Collecting indexes (Step 13). Each one is paid for on every write.

Where to go next

You can catch slow statements without running them by hand. The auto_explain module provides “a means for logging execution plans of slow statements automatically, without having to run EXPLAIN by hand,” so the plan that was running when a real incident happened ends up in your log. If you work in SQLite as well, the same plan-reading habit applies there: my tutorial on reading SQLite EXPLAIN QUERY PLAN shows how to check plans automatically. And the plans you now know how to read are a good way to judge the cost of features that add conditions to every query, such as the row-level security policies in my tutorial on PostgreSQL row-level security in FastAPI, whose performance test compares plans with and without an index on a 2-million-row table.

The habit to take with you is a short loop: find the statement that costs the most overall, read its plan from the bottom up, compare each estimate with the actual rows, count the buffers, change one thing, and measure again.

Tags:

DatabasesPerformance EngineeringPostgreSQLQuery OptimizationSQL

Share

No Comment! Be the first one.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts