SQL · Advanced

SQL EXPLAIN: read query plans and use indexes

18 min read

Why it matters

SQL EXPLAIN shows the query plan: how the database will actually find your rows, which indexes it uses, and how many rows it expects at each step. It is the first tool for any slow query, because guessing is how people add five useless indexes and still wait 30 seconds. A query that takes a second on 500,000 rows takes minutes on 500 million, and on a warehouse that bills per byte it also costs money. SQL appears in 88% of Data Analyst and 80% of Data Engineer job descriptions we studied, and "this query is slow, how do you find out why?" is asked in nearly every backend and data engineering interview.

This lesson reads real PostgreSQL plans on 541,909 sales lines: a full scan turned into an index lookup (25 ms to 0.5 ms), a date filter that silently stops using its index, a column where the planner is right to ignore an index, and statistics that were off by 335 times.

The concept

The planner and the plan

SQL says what you want, not how to get it. The planner considers ways to run the query (scan the whole table or use an index, which table first, which join method) and picks the one with the lowest estimated cost. The estimates come from statistics the database keeps about each table: row counts, common values, value ranges.

Command	What it does
`EXPLAIN query`	Shows the chosen plan with estimates. Does not run the query.
`EXPLAIN ANALYZE query`	Runs the query and adds actual rows and times to every step.
`EXPLAIN (ANALYZE, BUFFERS) query`	Also shows how many 8 kB pages were read from cache (`hit`) or disk (`read`).

EXPLAIN ANALYZE really executes the statement. On an UPDATE or DELETE, wrap it in BEGIN; ... ROLLBACK;.

Reading a PostgreSQL plan

A plan is a tree. Read it from the innermost, most indented line outwards: the deepest nodes produce rows, their parents consume them. On each line:

cost=0.00..10246.44: estimated startup cost and total cost, in arbitrary units. Compare costs between plans, never with milliseconds.
rows=38: estimated rows this node returns per loop.
actual time=6.968..19.908 rows=104 loops=3: real time to first row and to last row (ms), real rows per loop, and number of loops. Total rows = rows x loops.
Rows Removed by Filter: rows read and thrown away. A big number here on a selective query is the classic sign of a missing index.

The node names tell you the access method:

PostgreSQL	Meaning	SQLite	SQL Server	MySQL `EXPLAIN` type
Seq Scan	Read every row	`SCAN`	Table Scan / Clustered Index Scan	`ALL`
Index Scan	Walk the index, fetch each matching row	`SEARCH ... USING INDEX`	Index Seek + Key Lookup	`ref`, `range`
Index Only Scan	Answer from the index alone	`USING COVERING INDEX`	Index Seek (covering)	`Using index`
Bitmap Index Scan + Bitmap Heap Scan	Collect matching row locations, then read those pages in order
Gather, Parallel Seq Scan	Several worker processes share the work		Parallelism

What an index is

A B-tree index is a sorted copy of one or more columns, each entry pointing to its row, like the index at the back of a book. Finding one customer's 312 lines goes from reading every page to a few page reads. The costs: the index takes disk space, and every INSERT, UPDATE and DELETE has to update it too. Index the columns you filter and join on often, not everything.

Sargable filters

A filter the index can use is sargable (Search ARGument ABLE). The rule: leave the indexed column bare on one side of the comparison.

Not sargable	Sargable rewrite
`WHERE invoiced_at::date = '2011-11-15'`	`WHERE invoiced_at >= '2011-11-15' AND invoiced_at < '2011-11-16'`
`WHERE date_trunc('month', invoiced_at) = '2011-11-01'`	`WHERE invoiced_at >= '2011-11-01' AND invoiced_at < '2011-12-01'`
`WHERE lower(email) = 'a@b.com'`	Store emails lowercased, or add an expression index on `lower(email)`
`WHERE price * 1.18 > 100`	`WHERE price > 100 / 1.18`
`WHERE customer_id::text = '17850'`	Compare with the column's own type: `WHERE customer_id = 17850`

When the planner ignores your index

An index pays off when it returns a small fraction of the table. For a value that covers most rows, reading the whole table in order is cheaper than jumping around through an index, so the planner correctly chooses a Seq Scan. This is why indexes on low-variety columns (status, country in a one-country business, boolean flags) rarely help, except for the rare values.

Statistics

Estimates are only as good as the statistics. PostgreSQL's autovacuum refreshes them after enough rows change, but a big load followed straight away by a query can run on stale numbers. Run ANALYZE table after bulk loads (SQL Server: UPDATE STATISTICS, MySQL: ANALYZE TABLE). When estimated and actual rows differ by 10 times or more, suspect statistics first.

Columnar warehouses work differently

BigQuery, Snowflake, Redshift and DuckDB store data by column and mostly do not use B-tree indexes. They save work by reading only the columns you name (so SELECT * costs real money) and by skipping files or row groups whose min and max values cannot match your filter (partition pruning, clustering). The habit is the same: read the plan or the "bytes processed" figure before and after a change.

Worked example: plans on Online Retail

PostgreSQL 16 on the UCI Online Retail dataset (CC BY 4.0). Every output is from a real run; your times will differ, the shapes will not.

Set up the data

Download online-retail-dataset.csv (link in Further reading), start psql in the same folder, and run:

sql

CREATE TABLE raw_retail (
  invoice_no   text,
  stock_code   text,
  description  text,
  quantity     int,
  invoice_date text,
  unit_price   numeric(10,3),
  customer_id  int,
  country      text
);
\copy raw_retail FROM 'online-retail-dataset.csv' WITH (FORMAT csv, HEADER true)

CREATE TABLE sales AS
SELECT invoice_no, stock_code, description, quantity, unit_price, customer_id, country,
       to_timestamp(invoice_date, 'MM/DD/YYYY HH24:MI')::timestamp AS invoiced_at
FROM raw_retail;
ANALYZE sales;

Output

CREATE TABLE
COPY 541909
SELECT 541909
ANALYZE

Step 1: one customer's lines, no index

sql

EXPLAIN
SELECT count(*), sum(quantity * unit_price)
FROM sales
WHERE customer_id = 17850;

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), sum(quantity * unit_price)
FROM sales
WHERE customer_id = 17850;

Output

                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=11247.05..11247.06 rows=1 width=40)
   ->  Gather  (cost=11246.83..11247.04 rows=2 width=40)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=10246.83..10246.84 rows=1 width=40)
               ->  Parallel Seq Scan on sales  (cost=0.00..10246.44 rows=38 width=10)
                     Filter: (customer_id = 17850)
(6 rows)
 
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=11247.05..11247.06 rows=1 width=40) (actual time=22.438..24.726 rows=1 loops=1)
   Buffers: shared hit=2080 read=5344
   ->  Gather  (cost=11246.83..11247.04 rows=2 width=40) (actual time=22.327..24.715 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=2080 read=5344
         ->  Partial Aggregate  (cost=10246.83..10246.84 rows=1 width=40) (actual time=19.942..19.944 rows=1 loops=3)
               Buffers: shared hit=2080 read=5344
               ->  Parallel Seq Scan on sales  (cost=0.00..10246.44 rows=38 width=10) (actual time=6.968..19.908 rows=104 loops=3)
                     Filter: (customer_id = 17850)
                     Rows Removed by Filter: 180532
                     Buffers: shared hit=2080 read=5344
 Planning Time: 0.035 ms
 Execution Time: 24.746 ms
(14 rows)

Read it from the bottom up:

Parallel Seq Scan: three processes (the leader plus 2 workers, so loops=3) each read a third of the table. Each kept about 104 rows and threw away 180,532: 312 matches out of 541,909 rows read.
Partial Aggregate: each process counts and sums its share.
Gather collects the 3 partial results; Finalize Aggregate combines them into 1 row.
Buffers: 2,080 + 5,344 = 7,424 pages of 8 kB, the whole 58 MB table, to return one customer.

The estimate (38 rows per process, about 91 in total) is off from the real 312, but in the right order of magnitude. That is normal.

Step 2: add an index

sql

CREATE INDEX sales_customer_idx ON sales (customer_id);

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), sum(quantity * unit_price)
FROM sales
WHERE customer_id = 17850;

SELECT count(*), sum(quantity * unit_price)
FROM sales
WHERE customer_id = 17850;

SELECT pg_size_pretty(pg_relation_size('sales')) AS table_size,
       pg_size_pretty(pg_relation_size('sales_customer_idx')) AS index_size;

Output

CREATE INDEX
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=340.96..340.97 rows=1 width=40) (actual time=0.462..0.463 rows=1 loops=1)
   Buffers: shared read=21
   ->  Bitmap Heap Scan on sales  (cost=5.13..340.04 rows=91 width=10) (actual time=0.060..0.265 rows=312 loops=1)
         Recheck Cond: (customer_id = 17850)
         Heap Blocks: exact=18
         Buffers: shared read=21
         ->  Bitmap Index Scan on sales_customer_idx  (cost=0.00..5.11 rows=91 width=0) (actual time=0.037..0.037 rows=312 loops=1)
               Index Cond: (customer_id = 17850)
               Buffers: shared read=3
 Planning:
   Buffers: shared hit=65 read=1
 Planning Time: 0.427 ms
 Execution Time: 0.527 ms
(13 rows)
 
 count |   sum    
-------+----------
   312 | 5288.630
(1 row)
 
 table_size | index_size 
------------+------------
 58 MB      | 3792 kB
(1 row)

Bitmap Index Scan read 3 index pages to find the locations of all 312 rows.
Bitmap Heap Scan then read the 18 table pages holding them, in physical order.
21 pages instead of 7,424, and 0.5 ms instead of 25 ms. The Filter and Rows Removed lines are gone: the index condition (Index Cond) found exactly the right rows.

The index costs 3.7 MB, about 6% of the table, and makes every write to sales a little slower. Worth it for a column you look up constantly.

Step 3: a filter that silently stops using the index

sql

CREATE INDEX sales_invoiced_at_idx ON sales (invoiced_at);

-- the function hides the column from the index
EXPLAIN (ANALYZE)
SELECT count(*) FROM sales
WHERE invoiced_at::date = '2011-11-15';

-- the same rows, written as a range on the raw column
EXPLAIN (ANALYZE)
SELECT count(*) FROM sales
WHERE invoiced_at >= '2011-11-15' AND invoiced_at < '2011-11-16';

Output

CREATE INDEX
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=11813.97..11813.98 rows=1 width=8) (actual time=32.069..35.217 rows=1 loops=1)
   ->  Gather  (cost=11813.75..11813.96 rows=2 width=8) (actual time=31.929..35.206 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=10813.75..10813.76 rows=1 width=8) (actual time=29.398..29.399 rows=1 loops=3)
               ->  Parallel Seq Scan on sales  (cost=0.00..10810.93 rows=1129 width=0) (actual time=25.449..29.327 rows=1148 loops=3)
                     Filter: ((invoiced_at)::date = '2011-11-15'::date)
                     Rows Removed by Filter: 179488
 Planning Time: 0.428 ms
 Execution Time: 35.289 ms
(10 rows)
 
                                                                            QUERY PLAN                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=148.50..148.51 rows=1 width=8) (actual time=0.698..0.699 rows=1 loops=1)
   ->  Index Only Scan using sales_invoiced_at_idx on sales  (cost=0.42..139.61 rows=3559 width=0) (actual time=0.047..0.531 rows=3445 loops=1)
         Index Cond: ((invoiced_at >= '2011-11-15 00:00:00'::timestamp without time zone) AND (invoiced_at < '2011-11-16 00:00:00'::timestamp without time zone))
         Heap Fetches: 3445
 Planning Time: 0.109 ms
 Execution Time: 0.714 ms
(6 rows)

Both queries count the same lines: rows=1148 loops=3 is a rounded average per process, about 3,444 in total, and the index plan shows the exact 3,445. The index exists in both cases, yet the first one scans the whole table: the index is sorted by invoiced_at, not by invoiced_at::date, so the database cannot use it. The range version is 50 times faster.

The second plan is an Index Only Scan: count(*) needs nothing but the index. Heap Fetches: 3445 means PostgreSQL still visited the table to check each row's visibility because the table has not been vacuumed yet; covering indexes and that detail are the topic of the next lesson.

Step 4: when ignoring the index is right

sql

CREATE INDEX sales_country_idx ON sales (country);

SELECT country, count(*) AS lines,
       round(100.0 * count(*) / (SELECT count(*) FROM sales), 2) AS pct_of_table
FROM sales
WHERE country IN ('United Kingdom', 'Japan')
GROUP BY country
ORDER BY lines;

EXPLAIN SELECT sum(quantity) FROM sales WHERE country = 'Japan';
EXPLAIN SELECT sum(quantity) FROM sales WHERE country = 'United Kingdom';

Output

CREATE INDEX
    country     | lines  | pct_of_table 
----------------+--------+--------------
 Japan          |    358 |         0.07
 United Kingdom | 495478 |        91.43
(2 rows)
 
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Aggregate  (cost=359.09..359.10 rows=1 width=8)
   ->  Index Scan using sales_country_idx on sales  (cost=0.42..358.37 rows=289 width=4)
         Index Cond: (country = 'Japan'::text)
(3 rows)
 
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=11760.96..11760.97 rows=1 width=8)
   ->  Gather  (cost=11760.75..11760.96 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=10760.75..10760.76 rows=1 width=8)
               ->  Parallel Seq Scan on sales  (cost=0.00..10246.44 rows=205722 width=4)
                     Filter: (country = 'United Kingdom'::text)
(6 rows)

Same index, same query shape, two plans. Japan is 0.07% of the table, so the index is used. The UK is 91% of the table, and fetching 495,478 rows one index entry at a time would be far slower than reading the table straight through, so the planner scans. The statistics told it which value is common. "My index is not used" is not always a bug.

Step 5: stale statistics

A table analyzed while it held only December 2010, then loaded with a year of data before anyone ran ANALYZE (autovacuum is switched off on it so the demo is repeatable):

sql

-- a table that was analyzed when it held only December 2010
CREATE TABLE sales_live (LIKE sales) WITH (autovacuum_enabled = false);
INSERT INTO sales_live SELECT * FROM sales WHERE invoiced_at < '2011-01-01';
CREATE INDEX ON sales_live (invoiced_at);
ANALYZE sales_live;

-- then a year of data arrives
INSERT INTO sales_live SELECT * FROM sales WHERE invoiced_at >= '2011-01-01';

EXPLAIN (ANALYZE)
SELECT sum(quantity) FROM sales_live
WHERE invoiced_at >= '2011-11-01' AND invoiced_at < '2011-12-01';

ANALYZE sales_live;

EXPLAIN (ANALYZE)
SELECT sum(quantity) FROM sales_live
WHERE invoiced_at >= '2011-11-01' AND invoiced_at < '2011-12-01';

Output

CREATE TABLE
INSERT 0 42481
CREATE INDEX
ANALYZE
INSERT 0 499428
                                                                            QUERY PLAN                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=17.12..17.13 rows=1 width=8) (actual time=16.873..16.874 rows=1 loops=1)
   ->  Index Scan using sales_live_invoiced_at_idx on sales_live  (cost=0.42..16.49 rows=253 width=4) (actual time=0.009..10.751 rows=84711 loops=1)
         Index Cond: ((invoiced_at >= '2011-11-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2011-12-01 00:00:00'::timestamp without time zone))
 Planning Time: 0.167 ms
 Execution Time: 16.897 ms
(5 rows)
 
ANALYZE
                                                                            QUERY PLAN                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3397.01..3397.02 rows=1 width=8) (actual time=24.404..24.406 rows=1 loops=1)
   ->  Index Scan using sales_live_invoiced_at_idx on sales_live  (cost=0.42..3184.61 rows=84959 width=4) (actual time=0.018..15.480 rows=84711 loops=1)
         Index Cond: ((invoiced_at >= '2011-11-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2011-12-01 00:00:00'::timestamp without time zone))
 Planning Time: 0.213 ms
 Execution Time: 24.441 ms
(5 rows)

Before ANALYZE, the statistics only knew dates up to December 2010, so the planner expected 253 rows for November 2011. There were 84,711: off by 335 times. After ANALYZE the estimate is 84,959, within 0.3%.

Here the plan happened to survive, because the rows were inserted in date order and the index walk stays cheap. In a join, a 335 times underestimate is exactly what makes the planner choose a nested loop that runs for minutes instead of a hash join that takes seconds (see the join algorithms lesson). Compare rows= estimated against rows= actual on every plan you read; that gap is where most bad plans start.

Step 6: EXPLAIN ANALYZE really runs the statement

sql

BEGIN;

EXPLAIN (ANALYZE, COSTS OFF)
UPDATE sales SET unit_price = unit_price * 1.10
WHERE customer_id = 17850;

SELECT sum(quantity * unit_price) FROM sales WHERE customer_id = 17850;

ROLLBACK;

SELECT sum(quantity * unit_price) FROM sales WHERE customer_id = 17850;

Output

BEGIN
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Update on sales (actual time=5.586..5.587 rows=0 loops=1)
   ->  Bitmap Heap Scan on sales (actual time=0.069..0.337 rows=312 loops=1)
         Recheck Cond: (customer_id = 17850)
         Heap Blocks: exact=18
         ->  Bitmap Index Scan on sales_customer_idx (actual time=0.048..0.048 rows=312 loops=1)
               Index Cond: (customer_id = 17850)
 Planning Time: 0.375 ms
 Execution Time: 5.798 ms
(8 rows)
 
   sum    
----------
 5817.493
(1 row)
 
ROLLBACK
   sum    
----------
 5288.630
(1 row)

Inside the transaction the prices really went up 10% (5,288.63 became 5,817.49). The ROLLBACK undid it. Without BEGIN, "just looking at the plan" would have repriced 312 lines in production. COSTS OFF hides the estimates when you only care about the shape and timings.

Step 7: the same idea in SQLite

SQLite ships with Python, and its plans are one line per table. Same data, same query:

python

import sqlite3, csv
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE sales (invoice_no TEXT, stock_code TEXT, description TEXT, quantity INT, invoice_date TEXT, unit_price REAL, customer_id INT, country TEXT)")
with open("online-retail-dataset.csv", newline="") as f:
    r = csv.reader(f); next(r)
    con.executemany("INSERT INTO sales VALUES (?,?,?,?,?,?,?,?)",
                    ([a, b, c, int(d), e, float(g), int(h) if h else None, i] for a, b, c, d, e, g, h, i in r))
q = "SELECT count(*), sum(quantity * unit_price) FROM sales WHERE customer_id = 17850"
for row in con.execute("EXPLAIN QUERY PLAN " + q): print(row)
con.execute("CREATE INDEX sales_customer_idx ON sales (customer_id)")
for row in con.execute("EXPLAIN QUERY PLAN " + q): print(row)
print(con.execute(q).fetchone())

Output

(3, 0, 0, 'SCAN sales')
(4, 0, 0, 'SEARCH sales USING INDEX sales_customer_idx (customer_id=?)')
(312, 5288.63)

SCAN became SEARCH ... USING INDEX. The vocabulary changes between databases; the question you ask of every plan does not.

Common mistakes

Guessing instead of reading the plan. Run EXPLAIN (ANALYZE, BUFFERS) before changing anything, and again after. Keep both plans in the pull request.
Wrapping an indexed column in a function or cast. ::date, date_trunc, lower, to_char and implicit casts all turn an index lookup into a full scan. Rewrite as a range on the raw column, or index the expression.
Running EXPLAIN ANALYZE on a write without a transaction. It executes. Wrap it in BEGIN ... ROLLBACK.
Ignoring estimated vs actual rows. A large gap means stale statistics or a hard-to-estimate filter, and it is where bad join plans come from. ANALYZE after bulk loads.
Expecting an index to help on a common value. For 91% of the table, a scan is cheaper. Index selective columns.
Indexing every column "just in case". Each index slows every write and uses disk. Unused indexes are pure cost; the next lesson shows how to find them.
Comparing cost= numbers to milliseconds. Costs are relative units for the planner. Use actual time and Execution Time for real speed.
Testing on a tiny table. On 1,000 rows everything is fast and the planner may prefer scans. Test plans on production-sized data or a realistic sample.
SELECT * on a columnar warehouse. You pay for every column read. Name the columns.

Interview angle

This query is slow. Walk me through how you would find out why.

First reproduce it and get the real plan with EXPLAIN (ANALYZE, BUFFERS), on production-like data. Read it from the innermost nodes: look for sequential scans on big tables with a selective filter and many "Rows Removed by Filter" (a missing index or a non-sargable predicate), compare estimated and actual rows at each node (a big gap points to stale statistics or a hard filter, which leads to bad join choices), and look at which node has the largest actual time and buffer reads. Then fix one thing at a time: rewrite the predicate, add or adjust an index, run ANALYZE, or restructure the query, and measure again. On a warehouse I would look at bytes scanned, partition pruning and whether the query reads only the columns it needs.

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the plan the optimizer chose with its estimates, without running the query. EXPLAIN ANALYZE runs the query and adds actual rows, loops and timings to every node, so you can compare estimates with reality. Because it executes the statement, an EXPLAIN ANALYZE of an UPDATE or DELETE changes data unless you wrap it in a transaction and roll back.

Why might a query not use an index that exists?

Three common reasons. The predicate is not sargable: a function, cast or arithmetic on the indexed column hides it from the index. The filter is not selective: if most rows match, a sequential scan is cheaper and the planner is right. Or the estimates are wrong because statistics are stale. Others: a type mismatch between the column and the value, a leading wildcard in LIKE '%x', or a composite index whose first column is not in the filter.

What does an index cost?

Disk space (here 3.7 MB for a 58 MB table, per index), extra work on every insert, update of indexed columns, and delete, and more to vacuum and back up. On write-heavy tables too many indexes slow ingestion noticeably. So index columns used in frequent, selective filters, joins and sorts, and drop indexes that are never used.

Practice task

An analyst's query for product 85123A (the white hanging heart T-light holder) in the last quarter of 2011 is slow:

sql

SELECT count(*), sum(quantity) FROM sales
WHERE stock_code = '85123A'
  AND to_char(invoiced_at, 'YYYY-Q') = '2011-4';

Run it with EXPLAIN (ANALYZE, BUFFERS) on the sales table from this lesson (with the indexes from steps 2 to 4 in place). Note the access method, pages read, and estimated vs actual rows.
Make it sargable and add one index so it uses indexes for both conditions. Record the new plan and time, and check the result did not change.

Show a solution

The original:

sql

-- the slow version
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), sum(quantity) FROM sales
WHERE stock_code = '85123A'
  AND to_char(invoiced_at, 'YYYY-Q') = '2011-4';

Output

                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=12375.66..12375.67 rows=1 width=16) (actual time=24.736..27.289 rows=1 loops=1)
   Buffers: shared hit=2292 read=5132
   ->  Gather  (cost=12375.44..12375.65 rows=2 width=16) (actual time=24.638..27.280 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=2292 read=5132
         ->  Partial Aggregate  (cost=11375.44..11375.45 rows=1 width=16) (actual time=21.597..21.598 rows=1 loops=3)
               Buffers: shared hit=2292 read=5132
               ->  Parallel Seq Scan on sales  (cost=0.00..11375.42 rows=5 width=4) (actual time=14.548..21.565 rows=167 loops=3)
                     Filter: ((stock_code = '85123A'::text) AND (to_char(invoiced_at, 'YYYY-Q'::text) = '2011-4'::text))
                     Rows Removed by Filter: 180470
                     Buffers: shared hit=2292 read=5132
 Planning:
   Buffers: shared hit=108
 Planning Time: 0.363 ms
 Execution Time: 27.396 ms
(16 rows)

A parallel full scan of all 7,424 pages. The planner expected 5 rows per process and found about 167: it has no statistics on to_char(...) output, so it guesses.

The fix: replace to_char with a date range and index stock_code:

sql

CREATE INDEX sales_stock_code_idx ON sales (stock_code);

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), sum(quantity) FROM sales
WHERE stock_code = '85123A'
  AND invoiced_at >= '2011-10-01' AND invoiced_at < '2012-01-01';

SELECT count(*), sum(quantity) FROM sales
WHERE stock_code = '85123A'
  AND invoiced_at >= '2011-10-01' AND invoiced_at < '2012-01-01';

Output

CREATE INDEX
                                                                                              QUERY PLAN                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4588.03..4588.04 rows=1 width=16) (actual time=5.707..5.710 rows=1 loops=1)
   Buffers: shared hit=278 read=337
   ->  Bitmap Heap Scan on sales  (cost=2405.77..4584.34 rows=739 width=4) (actual time=4.105..5.646 rows=500 loops=1)
         Recheck Cond: ((stock_code = '85123A'::text) AND (invoiced_at >= '2011-10-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2012-01-01 00:00:00'::timestamp without time zone))
         Heap Blocks: exact=449
         Buffers: shared hit=278 read=337
         ->  BitmapAnd  (cost=2405.77..2405.77 rows=739 width=0) (actual time=4.042..4.044 rows=0 loops=1)
               Buffers: shared hit=3 read=163
               ->  Bitmap Index Scan on sales_stock_code_idx  (cost=0.00..29.90 rows=2330 width=0) (actual time=0.209..0.209 rows=2313 loops=1)
                     Index Cond: (stock_code = '85123A'::text)
                     Buffers: shared read=5
               ->  Bitmap Index Scan on sales_invoiced_at_idx  (cost=0.00..2375.26 rows=171883 width=0) (actual time=3.731..3.731 rows=170978 loops=1)
                     Index Cond: ((invoiced_at >= '2011-10-01 00:00:00'::timestamp without time zone) AND (invoiced_at < '2012-01-01 00:00:00'::timestamp without time zone))
                     Buffers: shared hit=3 read=158
 Planning:
   Buffers: shared hit=106 read=3
 Planning Time: 0.467 ms
 Execution Time: 5.772 ms
(18 rows)
 
 count | sum  
-------+------
   500 | 7149
(1 row)

From 27.4 ms and 7,424 pages to 5.8 ms and 615 pages, with the same 500 lines. The plan is worth reading closely: PostgreSQL used two indexes, built a bitmap from each (2,313 rows for the product, 170,978 for the quarter) and intersected them with BitmapAnd. Most of the time went into the date index, which matched almost a third of the table. One index on (stock_code, invoiced_at) would find the 500 rows directly; that is a composite index, the subject of the next lesson.

#Why it matters

#The concept

#The planner and the plan

#Reading a PostgreSQL plan

#What an index is

#Sargable filters

#When the planner ignores your index

#Statistics

#Columnar warehouses work differently

#Worked example: plans on Online Retail

#Step 1: one customer's lines, no index

#Step 2: add an index

#Step 3: a filter that silently stops using the index

#Step 4: when ignoring the index is right

#Step 5: stale statistics

#Step 6: EXPLAIN ANALYZE really runs the statement

#Step 7: the same idea in SQLite

#Common mistakes

#Interview angle

#Practice task

#Further reading

Why it matters

The concept

The planner and the plan

Reading a PostgreSQL plan

What an index is

Sargable filters

When the planner ignores your index

Statistics

Columnar warehouses work differently

Worked example: plans on Online Retail

Step 1: one customer's lines, no index

Step 2: add an index

Step 3: a filter that silently stops using the index

Step 4: when ignoring the index is right

Step 5: stale statistics

Step 6: EXPLAIN ANALYZE really runs the statement

Step 7: the same idea in SQLite

Common mistakes

Interview angle

Practice task

Further reading