SQL · Advanced

SQL query optimization: rewrite slow queries

27 min read

Why it matters

SQL query optimization is mostly rewriting, not hardware. In this lesson one query goes from 52.7 seconds to 0.28 seconds by replacing a correlated subquery with a window function, another goes from 72 ms to 0.19 ms by removing a .0 from a number, and a paginated list goes from 36 ms to 0.09 ms by changing how it asks for page 2,001. None of them needed a bigger server. Knowing these patterns, and proving each fix with a before and after measurement, is what makes an analyst's dashboards fast and a data engineer's pipelines cheap. SQL appears in 88% of Data Analyst and 80% of Data Engineer job descriptions we studied, and "give three ways to speed up a query without adding hardware" is one of the most common SQL interview questions.

You will first find which queries are worth fixing with pg_stat_statements, then apply six rewrites on real data, checking each time that the answer did not change.

The concept

Measure first, then fix the queries that matter

A query that takes 2 seconds once a day matters less than one that takes 20 ms and runs 300,000 times. Rank queries by total time (calls x average time), fix the top few, and measure again. In PostgreSQL the pg_stat_statements extension records this for every query shape; in MySQL it is the performance_schema statement digests, in SQL Server Query Store, and in BigQuery and Snowflake the job and query history views.

The rewrite checklist

Most slow queries do more work than the answer needs. The usual causes, in the order this lesson covers them:

Pattern	Why it is slow	Rewrite
Correlated subquery in `WHERE` or `SELECT`	Runs once per outer row	Window function, or aggregate once and join
Join first, aggregate later	Every detail row goes through the join	Aggregate the big side first, then join
Wrong-type literal or join key (`= 17850.0`, `int = text`)	The column is cast, so its index cannot be used	Match the column's type; cast the other side
`OR` across columns of different tables	No single index can answer it; the database joins everything first	`UNION ALL` of two indexed queries
`DISTINCT` after a join	Builds all duplicates, then sorts them away	`EXISTS` (or fix the join)
Large `OFFSET`	Reads and discards every skipped row	Keyset pagination: `WHERE key > last_seen`

Two more rules apply to every query: filter early (sargable filters on indexed columns, covered in SQL EXPLAIN: read query plans and use indexes) and select only the columns you need (it enables index-only scans, see SQL composite indexes and covering indexes, and in columnar warehouses it decides the bill).

Correctness first

Every rewrite must return the same answer. Before calling a rewrite done, compare row counts and a total (count(*), sum(...)) between the old and new query, side by side. A fast wrong query is a bug.

Warehouses: bytes scanned is the cost

BigQuery on-demand pricing charges by bytes scanned, and Snowflake charges for warehouse time. There, the same rewrites help, plus two that matter more than indexes:

Select only the columns you need. Columnar storage reads each column separately, so SELECT * on a 50-column table reads 50 columns. BigQuery's query validator shows the bytes a query will scan before you run it.
Filter on the partition and clustering columns (usually a date). A filter like WHERE event_date >= '2024-01-01' lets the engine skip whole partitions or micro-partitions; wrapping that column in a function can stop the pruning, just as it stops an index.

The data warehousing skill covers partitioning and clustering in depth.

Worked example: six rewrites on two datasets

PostgreSQL 16 with the sales table (541,909 lines of UCI Online Retail, CC BY 4.0) and its indexes from SQL EXPLAIN: read query plans and use indexes, plus the Olist orders and order_items tables from Hash join vs nested loop vs merge join. Every output is from a real run; your times will differ, but the ratios should be similar.

Set up pg_stat_statements and the customers table

pg_stat_statements ships with PostgreSQL but must be loaded at server start. Add this line to postgresql.conf (or run ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';), restart PostgreSQL, then run:

sql

CREATE EXTENSION pg_stat_statements;

-- one row per customer, used in steps 3 and 5
CREATE TABLE customers AS
SELECT customer_id, min(country) AS country, min(invoiced_at) AS first_seen
FROM sales WHERE customer_id IS NOT NULL GROUP BY customer_id;
ALTER TABLE customers ADD PRIMARY KEY (customer_id);
ANALYZE customers;

Output

CREATE EXTENSION
SELECT 4372
ALTER TABLE
ANALYZE

On managed services (Amazon RDS, Cloud SQL, Azure) the library is usually preloaded already; you only run CREATE EXTENSION.

Step 1: find the queries that matter

Simulate a busy hour with pgbench, which ships with PostgreSQL. Three small script files:

text

-- lookup.sql: the app looks up one customer's lines
\set cid random(12346, 18287)
SELECT count(*) FROM sales WHERE customer_id = :cid;
 
-- badlookup.sql: another service sends the id as a decimal
\set cid random(12346, 18287)
SELECT count(*) FROM sales WHERE customer_id = :cid.0;
 
-- report.sql: a dashboard tile
SELECT country, date_trunc('month', invoiced_at) AS month, sum(quantity * unit_price) AS revenue
FROM sales GROUP BY 1, 2 ORDER BY 1, 2;

Reset the statistics, run 2,000 lookups, 300 decimal lookups and 20 reports, then ask which queries used the most time:

sql

SELECT pg_stat_statements_reset();

bash

pgbench -n -t 2000 -f lookup.sql    your_db
pgbench -n -t 300  -f badlookup.sql your_db
pgbench -n -t 20   -f report.sql    your_db

sql

SELECT calls,
       round(total_exec_time::numeric) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
       left(query, 60) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 5;

Output

 pg_stat_statements_reset 
--------------------------
 
(1 row)
 
 calls | total_ms | mean_ms | pct  |                            query                             
-------+----------+---------+------+--------------------------------------------------------------
   300 |     6593 |   21.98 | 54.5 | SELECT count(*) FROM sales WHERE customer_id = $1
    20 |     5485 |  274.23 | 45.3 | SELECT country, date_trunc($1, invoiced_at) AS month, sum(qu
  2000 |       17 |    0.01 |  0.1 | SELECT count(*) FROM sales WHERE customer_id = $1
     1 |        1 |    0.57 |  0.0 | SELECT pg_stat_statements_reset()
(4 rows)

Three things to read here:

pg_stat_statements replaces constants with $1, so all 2,000 lookups for different customers count as one query shape.
The top line looks identical to the third, but it is a different shape: $1 there is a numeric, not an integer. Those 300 calls averaged 21.98 ms, against 0.01 ms for the 2,000 normal ones, and they used 54.5% of all database time. Step 4 explains why.
The dashboard query is 45.3% of the time with only 20 calls. That is a candidate for a summary table or materialized view (SQL views vs materialized views), not for a rewrite.

The 2,000 fast lookups, the most frequent query, are 0.1% of the load. Ranking by calls would have sent you to the wrong query.

Step 2: correlated subquery to window function

"How many lines have a quantity above that customer's own average?" A natural first draft computes the average inside the WHERE:

sql

SET max_parallel_workers_per_gather = 0;  -- one process, so plans are easier to read

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) AS big_lines
FROM sales AS s
WHERE s.customer_id IS NOT NULL
  AND s.quantity > (SELECT avg(s2.quantity) FROM sales AS s2
                    WHERE s2.customer_id = s.customer_id);

Output

SET
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=52686.050..52686.053 rows=1 loops=1)
   ->  Index Scan using sales_customer_idx on sales s (actual time=318.929..52668.852 rows=133269 loops=1)
         Index Cond: (customer_id IS NOT NULL)
         Filter: ((quantity)::numeric > (SubPlan 1))
         Rows Removed by Filter: 273560
         SubPlan 1
           ->  Aggregate (actual time=0.127..0.127 rows=1 loops=406829)
                 ->  Bitmap Heap Scan on sales s2 (actual time=0.015..0.081 rows=674 loops=406829)
                       Recheck Cond: (customer_id = s.customer_id)
                       Heap Blocks: exact=11655820
                       ->  Bitmap Index Scan on sales_customer_idx (actual time=0.012..0.012 rows=674 loops=406829)
                             Index Cond: (customer_id = s.customer_id)
 Planning Time: 0.448 ms
 Execution Time: 52704.526 ms
(14 rows)

52.7 seconds. SubPlan 1 ran loops=406829 times: once per line, it re-read all of that customer's lines (674 on average) to compute the same average again. A customer with thousands of lines had the same average computed thousands of times. In total it made 11,655,820 table page visits.

Two rewrites compute each customer's average once:

sql

SET max_parallel_workers_per_gather = 0;

-- window function: the average travels with each row
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) AS big_lines
FROM (
  SELECT quantity, avg(quantity) OVER (PARTITION BY customer_id) AS cust_avg
  FROM sales
  WHERE customer_id IS NOT NULL
) AS t
WHERE quantity > cust_avg;

-- aggregate once, then join
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) AS big_lines
FROM sales AS s
JOIN (SELECT customer_id, avg(quantity) AS cust_avg FROM sales
      WHERE customer_id IS NOT NULL GROUP BY customer_id) AS a
  ON a.customer_id = s.customer_id
WHERE s.quantity > a.cust_avg;

Output

SET
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=284.138..284.141 rows=1 loops=1)
   ->  Subquery Scan on t (actual time=0.027..276.991 rows=133269 loops=1)
         Filter: ((t.quantity)::numeric > t.cust_avg)
         Rows Removed by Filter: 273560
         ->  WindowAgg (actual time=0.025..225.679 rows=406829 loops=1)
               ->  Index Scan using sales_customer_idx on sales (actual time=0.013..98.348 rows=406829 loops=1)
                     Index Cond: (customer_id IS NOT NULL)
 Planning Time: 0.195 ms
 Execution Time: 284.196 ms
(9 rows)
 
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Aggregate (actual time=279.103..279.108 rows=1 loops=1)
   ->  Hash Join (actual time=107.494..272.194 rows=133269 loops=1)
         Hash Cond: (s.customer_id = sales.customer_id)
         Join Filter: ((s.quantity)::numeric > (avg(sales.quantity)))
         Rows Removed by Join Filter: 273560
         ->  Seq Scan on sales s (actual time=0.015..42.143 rows=541909 loops=1)
         ->  Hash (actual time=107.452..107.454 rows=4372 loops=1)
               Buckets: 8192 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 267kB
               ->  HashAggregate (actual time=105.165..106.743 rows=4372 loops=1)
                     Group Key: sales.customer_id
                     Batches: 1  Memory Usage: 977kB
                     ->  Seq Scan on sales (actual time=0.003..58.340 rows=406829 loops=1)
                           Filter: (customer_id IS NOT NULL)
                           Rows Removed by Filter: 135080
 Planning Time: 0.444 ms
 Execution Time: 279.202 ms
(16 rows)

Both return the same 133,269 lines (the rows= on the top child node) in about 0.28 seconds, 185 times faster. The window version reads the table once in customer order (the index provides the order) and attaches each customer's average to every row; the join version builds a 4,372-row table of averages and hashes it. Either is fine. Not every correlated subquery is slow: PostgreSQL turns many EXISTS and IN subqueries into joins on its own. The ones to hunt for are scalar subqueries (one value per row) in WHERE or SELECT, visible as SubPlan with a huge loops=.

Step 3: aggregate before joining

Revenue by country, where the country comes from customers:

sql

SET max_parallel_workers_per_gather = 0;

-- join every line, then group
EXPLAIN (ANALYZE, COSTS OFF)
SELECT c.country, sum(s.quantity * s.unit_price) AS revenue
FROM sales AS s
JOIN customers AS c ON c.customer_id = s.customer_id
GROUP BY c.country;

-- group lines per customer first, then join 4,372 rows
EXPLAIN (ANALYZE, COSTS OFF)
SELECT c.country, sum(t.revenue) AS revenue
FROM (SELECT customer_id, sum(quantity * unit_price) AS revenue
      FROM sales
      WHERE customer_id IS NOT NULL
      GROUP BY customer_id) AS t
JOIN customers AS c ON c.customer_id = t.customer_id
GROUP BY c.country;

Output

SET
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 HashAggregate (actual time=282.153..282.168 rows=37 loops=1)
   Group Key: c.country
   Batches: 1  Memory Usage: 32kB
   ->  Hash Join (actual time=1.449..148.257 rows=406829 loops=1)
         Hash Cond: (s.customer_id = c.customer_id)
         ->  Seq Scan on sales s (actual time=0.014..48.780 rows=541909 loops=1)
         ->  Hash (actual time=1.390..1.392 rows=4372 loops=1)
               Buckets: 8192  Batches: 1  Memory Usage: 284kB
               ->  Seq Scan on customers c (actual time=0.013..0.630 rows=4372 loops=1)
 Planning Time: 0.905 ms
 Execution Time: 282.281 ms
(11 rows)
 
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 HashAggregate (actual time=186.143..186.154 rows=37 loops=1)
   Group Key: c.country
   Batches: 1  Memory Usage: 32kB
   ->  Hash Join (actual time=184.315..185.368 rows=4372 loops=1)
         Hash Cond: (c.customer_id = t.customer_id)
         ->  Seq Scan on customers c (actual time=0.004..0.287 rows=4372 loops=1)
         ->  Hash (actual time=184.301..184.302 rows=4372 loops=1)
               Buckets: 8192 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 253kB
               ->  Subquery Scan on t (actual time=181.074..183.591 rows=4372 loops=1)
                     ->  HashAggregate (actual time=181.072..182.922 rows=4372 loops=1)
                           Group Key: sales.customer_id
                           Batches: 1  Memory Usage: 2001kB
                           ->  Seq Scan on sales (actual time=0.007..61.124 rows=406829 loops=1)
                                 Filter: (customer_id IS NOT NULL)
                                 Rows Removed by Filter: 135080
 Planning Time: 0.213 ms
 Execution Time: 186.367 ms
(17 rows)

The first plan pushes 406,829 rows through the join; the second joins only 4,372 pre-summed rows. 282 ms to 186 ms, about 1.5 times faster, and both return the same 37 countries with the same total:

sql

SELECT 1 AS n, count(*) AS countries, sum(revenue) AS total FROM (
  SELECT c.country, sum(s.quantity * s.unit_price) AS revenue
  FROM sales AS s JOIN customers AS c ON c.customer_id = s.customer_id
  GROUP BY c.country) AS a
UNION ALL
SELECT 2, count(*), sum(revenue) FROM (
  SELECT c.country, sum(t.revenue) AS revenue
  FROM (SELECT customer_id, sum(quantity * unit_price) AS revenue
        FROM sales WHERE customer_id IS NOT NULL GROUP BY customer_id) AS t
  JOIN customers AS c ON c.customer_id = t.customer_id
  GROUP BY c.country) AS b
ORDER BY n;

Output

 n | countries |    total    
---+-----------+-------------
 1 |        37 | 8300065.814
 2 |        37 | 8300065.814
(2 rows)

A modest gain on one machine with a small lookup table. The same rewrite matters far more when the join fans out (one row becomes many), when the other side is large, or in a distributed engine where the join moves data over the network. It is only safe when the measure can be summed in two steps: sums and counts can, averages and distinct counts cannot without care.

Step 4: a literal or join key of the wrong type

Back to the slow line from step 1. customer_id is an integer:

sql

SET max_parallel_workers_per_gather = 0;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM sales WHERE customer_id = 17850.0;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM sales WHERE customer_id = 17850;

Output

SET
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Aggregate (actual time=72.417..72.419 rows=1 loops=1)
   ->  Index Only Scan using sales_customer_idx on sales (actual time=60.327..72.385 rows=312 loops=1)
         Filter: ((customer_id)::numeric = 17850.0)
         Rows Removed by Filter: 541597
         Heap Fetches: 1365
 Planning Time: 0.591 ms
 Execution Time: 72.483 ms
(7 rows)
 
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Aggregate (actual time=0.163..0.163 rows=1 loops=1)
   ->  Index Only Scan using sales_customer_idx on sales (actual time=0.020..0.141 rows=312 loops=1)
         Index Cond: (customer_id = 17850)
         Heap Fetches: 354
 Planning Time: 0.134 ms
 Execution Time: 0.187 ms
(6 rows)

17850.0 is a numeric. To compare, PostgreSQL converts the column to numeric, (customer_id)::numeric, and an index on customer_id cannot search on a converted value. The plan still reads the index, but as a list to scan: Filter instead of Index Cond, with 541,597 rows removed. 72 ms against 0.19 ms, about 390 times slower, for the same 312 lines. In real systems this comes from application code: a JavaScript number or a pandas column with missing values (pandas turns those into floats) sent as 17850.0, or an ORM parameter typed as decimal.

The same trap in a join. A partner sends a VIP list where the ID is text:

sql

SET max_parallel_workers_per_gather = 0;

CREATE TABLE vip_customers (customer_id text PRIMARY KEY, tier text);
INSERT INTO vip_customers
SELECT customer_id::text, 'gold' FROM customers ORDER BY customer_id LIMIT 50;
ANALYZE vip_customers;

-- cast the big table's column to match the small one
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) AS lines
FROM vip_customers AS v
JOIN sales AS s ON s.customer_id::text = v.customer_id;

-- cast the small table's column instead
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) AS lines
FROM vip_customers AS v
JOIN sales AS s ON s.customer_id = v.customer_id::int;

Output

SET
CREATE TABLE
INSERT 0 50
ANALYZE
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=139.551..139.555 rows=1 loops=1)
   ->  Hash Join (actual time=0.101..139.291 rows=3639 loops=1)
         Hash Cond: ((s.customer_id)::text = v.customer_id)
         ->  Index Only Scan using sales_customer_idx on sales s (actual time=0.021..59.304 rows=541909 loops=1)
               Heap Fetches: 1365
         ->  Hash (actual time=0.057..0.059 rows=50 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 10kB
               ->  Seq Scan on vip_customers v (actual time=0.004..0.010 rows=50 loops=1)
 Planning Time: 0.186 ms
 Execution Time: 139.597 ms
(10 rows)
 
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=1.241..1.242 rows=1 loops=1)
   ->  Nested Loop (actual time=0.038..0.979 rows=3639 loops=1)
         ->  Seq Scan on vip_customers v (actual time=0.018..0.024 rows=50 loops=1)
         ->  Index Only Scan using sales_customer_idx on sales s (actual time=0.002..0.010 rows=73 loops=50)
               Index Cond: (customer_id = (v.customer_id)::integer)
               Heap Fetches: 0
 Planning Time: 0.319 ms
 Execution Time: 1.270 ms
(8 rows)

Casting sales.customer_id turned every one of 541,909 lines into text before matching (140 ms). Casting the 50 VIP ids to integer let the planner do 50 index lookups into sales (1.3 ms, 110 times faster). The rule: cast the small side, never the indexed column, and better still, store join keys with the same type in every table. MySQL has the mirror-image trap: comparing a string column to a number converts the column, so WHERE phone = 5551234 on a text phone column skips its index.

Step 5: OR across two tables to UNION ALL

Lines for product 85123A or from customers in Iceland:

sql

SET max_parallel_workers_per_gather = 0;
CREATE INDEX customers_country_idx ON customers (country);
ANALYZE customers;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) AS lines, sum(s.quantity * s.unit_price) AS revenue
FROM sales AS s
JOIN customers AS c ON c.customer_id = s.customer_id
WHERE s.stock_code = '85123A' OR c.country = 'Iceland';

Output

SET
CREATE INDEX
ANALYZE
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Aggregate (actual time=174.077..174.080 rows=1 loops=1)
   ->  Hash Join (actual time=1.107..172.628 rows=2259 loops=1)
         Hash Cond: (s.customer_id = c.customer_id)
         Join Filter: ((s.stock_code = '85123A'::text) OR (c.country = 'Iceland'::text))
         Rows Removed by Join Filter: 404570
         ->  Seq Scan on sales s (actual time=0.011..59.980 rows=541909 loops=1)
         ->  Hash (actual time=1.084..1.086 rows=4372 loops=1)
               Buckets: 8192  Batches: 1  Memory Usage: 279kB
               ->  Seq Scan on customers c (actual time=0.003..0.406 rows=4372 loops=1)
 Planning Time: 0.701 ms
 Execution Time: 174.138 ms
(11 rows)

Both columns are indexed, but they are in different tables. The condition can only be checked after the join (Join Filter), so the database joins all 406,829 customer lines and throws away 404,570 of them. Split the OR into two queries that each use their own index:

sql

SET max_parallel_workers_per_gather = 0;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) AS lines, sum(revenue) AS revenue
FROM (
  SELECT s.quantity * s.unit_price AS revenue
  FROM sales AS s
  JOIN customers AS c ON c.customer_id = s.customer_id
  WHERE s.stock_code = '85123A'
  UNION ALL
  SELECT s.quantity * s.unit_price
  FROM sales AS s
  JOIN customers AS c ON c.customer_id = s.customer_id
  WHERE c.country = 'Iceland'
    AND s.stock_code IS DISTINCT FROM '85123A'
) AS both_parts;

Output

SET
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Aggregate (actual time=7.690..7.694 rows=1 loops=1)
   ->  Append (actual time=1.845..7.396 rows=2259 loops=1)
         ->  Hash Join (actual time=1.844..6.973 rows=2077 loops=1)
               Hash Cond: (s.customer_id = c.customer_id)
               ->  Bitmap Heap Scan on sales s (actual time=0.732..4.491 rows=2313 loops=1)
                     Recheck Cond: (stock_code = '85123A'::text)
                     Heap Blocks: exact=1973
                     ->  Bitmap Index Scan on sales_stock_code_idx (actual time=0.385..0.385 rows=2313 loops=1)
                           Index Cond: (stock_code = '85123A'::text)
               ->  Hash (actual time=1.099..1.100 rows=4372 loops=1)
                     Buckets: 8192  Batches: 1  Memory Usage: 218kB
                     ->  Seq Scan on customers c (actual time=0.006..0.460 rows=4372 loops=1)
         ->  Nested Loop (actual time=0.069..0.187 rows=182 loops=1)
               ->  Index Scan using customers_country_idx on customers c_1 (actual time=0.042..0.043 rows=1 loops=1)
                     Index Cond: (country = 'Iceland'::text)
               ->  Bitmap Heap Scan on sales s_1 (actual time=0.021..0.065 rows=182 loops=1)
                     Recheck Cond: (customer_id = c_1.customer_id)
                     Filter: (stock_code IS DISTINCT FROM '85123A'::text)
                     Heap Blocks: exact=9
                     ->  Bitmap Index Scan on sales_customer_idx (actual time=0.013..0.013 rows=182 loops=1)
                           Index Cond: (customer_id = c_1.customer_id)
 Planning Time: 0.552 ms
 Execution Time: 7.744 ms
(23 rows)

174 ms to 7.7 ms, 22 times faster, and the same 2,259 lines. Each branch starts from an index: the product branch from sales_stock_code_idx, the Iceland branch from customers_country_idx (one customer, 182 lines). The IS DISTINCT FROM '85123A' in the second branch is what keeps it correct: a line from Iceland for 85123A matches both conditions and would otherwise be counted twice. IS DISTINCT FROM instead of <> also keeps lines whose stock_code is NULL. Do not use UNION (without ALL) to remove those duplicates: it would also merge genuinely different lines that happen to have equal values. Check the totals:

sql

SELECT count(*) AS lines, sum(s.quantity * s.unit_price) AS revenue
FROM sales AS s
JOIN customers AS c ON c.customer_id = s.customer_id
WHERE s.stock_code = '85123A' OR c.country = 'Iceland';

SELECT count(*) AS lines, sum(revenue) AS revenue
FROM (
  SELECT s.quantity * s.unit_price AS revenue
  FROM sales AS s
  JOIN customers AS c ON c.customer_id = s.customer_id
  WHERE s.stock_code = '85123A'
  UNION ALL
  SELECT s.quantity * s.unit_price
  FROM sales AS s
  JOIN customers AS c ON c.customer_id = s.customer_id
  WHERE c.country = 'Iceland'
    AND s.stock_code IS DISTINCT FROM '85123A'
) AS both_parts;

Output

 lines |  revenue  
-------+-----------
  2259 | 98289.200
(1 row)
 
 lines |  revenue  
-------+-----------
  2259 | 98289.200
(1 row)

An OR on two columns of the same table is usually fine in PostgreSQL: it combines both indexes with a BitmapOr. The rewrite is for OR across tables, and for engines that cannot combine indexes.

Step 6: DISTINCT after a join to EXISTS

Now on Olist. "Orders that contain at least one item over 1,000":

sql

SET max_parallel_workers_per_gather = 0;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT DISTINCT o.order_id, o.order_status, o.order_purchase_timestamp
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE i.price > 1000;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT o.order_id, o.order_status, o.order_purchase_timestamp
FROM orders AS o
WHERE EXISTS (SELECT 1 FROM order_items AS i
              WHERE i.order_id = o.order_id AND i.price > 1000);

Output

SET
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Unique (actual time=75.765..75.926 rows=820 loops=1)
   ->  Sort (actual time=75.764..75.817 rows=844 loops=1)
         Sort Key: o.order_id, o.order_status, o.order_purchase_timestamp
         Sort Method: quicksort  Memory: 90kB
         ->  Hash Join (actual time=53.354..75.095 rows=844 loops=1)
               Hash Cond: (i.order_id = o.order_id)
               ->  Seq Scan on order_items i (actual time=0.058..15.911 rows=844 loops=1)
                     Filter: (price > '1000'::numeric)
                     Rows Removed by Filter: 111806
               ->  Hash (actual time=52.699..52.700 rows=99441 loops=1)
                     Buckets: 131072  Batches: 2  Memory Usage: 5300kB
                     ->  Seq Scan on orders o (actual time=0.013..16.721 rows=99441 loops=1)
 Planning Time: 0.472 ms
 Execution Time: 76.186 ms
(14 rows)
 
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Hash Join (actual time=14.606..32.248 rows=820 loops=1)
   Hash Cond: (o.order_id = i.order_id)
   ->  Seq Scan on orders o (actual time=0.003..7.232 rows=99441 loops=1)
   ->  Hash (actual time=14.567..14.568 rows=820 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 61kB
         ->  HashAggregate (actual time=14.393..14.471 rows=820 loops=1)
               Group Key: i.order_id
               Batches: 1  Memory Usage: 129kB
               ->  Seq Scan on order_items i (actual time=0.044..14.118 rows=844 loops=1)
                     Filter: (price > '1000'::numeric)
                     Rows Removed by Filter: 111806
 Planning Time: 0.224 ms
 Execution Time: 32.315 ms
(13 rows)

The join produced 844 rows for 820 orders, because some orders have two expensive items, and DISTINCT then sorted all of them to throw the extras away. EXISTS says what we meant ("has at least one"), so the planner reduced the 844 items to 820 distinct order ids first and hashed that small set: 76 ms to 32 ms, same 820 orders.

The bigger point is correctness. DISTINCT added to make duplicate rows disappear usually hides a join that fans out (SQL join fan-out: avoid double counting rows). If the query also sums a column from orders, DISTINCT will not save it. When you see SELECT DISTINCT over a join, ask which join creates the duplicates and whether it should be an EXISTS, or a join to a pre-aggregated subquery.

Step 7: keyset pagination instead of OFFSET

An admin screen lists order items 50 at a time. Page 2,001 with OFFSET:

sql

SET max_parallel_workers_per_gather = 0;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT order_id, order_item_id, price
FROM order_items
ORDER BY order_id, order_item_id
OFFSET 100000 LIMIT 50;

-- the last row of page 2,000
SELECT order_id, order_item_id
FROM order_items
ORDER BY order_id, order_item_id
OFFSET 99999 LIMIT 1;

Output

SET
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Limit (actual time=36.371..36.421 rows=50 loops=1)
   ->  Index Scan using order_items_pkey on order_items (actual time=0.035..31.626 rows=100050 loops=1)
 Planning Time: 0.586 ms
 Execution Time: 36.465 ms
(4 rows)
 
             order_id             | order_item_id 
----------------------------------+---------------
 e2d027e2358cd57b0616adc9ced4c560 |             1
(1 row)

OFFSET 100000 does not jump: the index scan read 100,050 rows and discarded the first 100,000. Every page is slower than the one before. Keyset pagination (also called the seek method) remembers the last row the client saw and asks for rows after it, using a row comparison on the sort key:

sql

SET max_parallel_workers_per_gather = 0;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT order_id, order_item_id, price
FROM order_items
WHERE (order_id, order_item_id) > ('e2d027e2358cd57b0616adc9ced4c560', 1)
ORDER BY order_id, order_item_id
LIMIT 50;

-- same 50 rows as the OFFSET version?
SELECT count(*) AS same_rows FROM (
  (SELECT order_id, order_item_id FROM order_items
   ORDER BY order_id, order_item_id OFFSET 100000 LIMIT 50)
  INTERSECT
  (SELECT order_id, order_item_id FROM order_items
   WHERE (order_id, order_item_id) > ('e2d027e2358cd57b0616adc9ced4c560', 1)
   ORDER BY order_id, order_item_id LIMIT 50)
) AS x;

Output

SET
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Limit (actual time=0.032..0.055 rows=50 loops=1)
   ->  Index Scan using order_items_pkey on order_items (actual time=0.031..0.050 rows=50 loops=1)
         Index Cond: (ROW(order_id, order_item_id) > ROW('e2d027e2358cd57b0616adc9ced4c560'::text, 1))
 Planning Time: 0.563 ms
 Execution Time: 0.092 ms
(5 rows)
 
 same_rows 
-----------
        50
(1 row)

The index jumps straight to the last seen key and reads 50 rows: 0.092 ms against 36.5 ms, and it stays that fast on every page, including the last. Requirements: the sort key must be unique (add the primary key as a tie-breaker, as order_item_id does here) and covered by an index in the same order. The trade-off: users can go to the next page, not jump to page 1,437. That suits feeds, infinite scroll, APIs and batch exports, which is where deep pages happen. The row comparison (a, b) > (x, y) works in PostgreSQL and SQLite. SQL Server does not support it, and MySQL may not use the index for it, so there write a > x OR (a = x AND b > y).

Write a before and after note

A reviewer should be able to check your claim without trusting you. For each change, record the query, what changed, the timings from the same machine, and the correctness check. This lesson's results:

Change	Before	After	Same answer checked by
Correlated subquery to window function	52,705 ms	284 ms	133,269 rows both
Aggregate per customer before the join	282 ms	186 ms	37 countries, total 8300065.814
`customer_id = 17850.0` to `= 17850`	72.5 ms	0.19 ms	312 rows both
Cast the 50 VIP ids, not the sales column	140 ms	1.3 ms	3,639 rows both
`OR` across tables to `UNION ALL`	174 ms	7.7 ms	2,259 lines, revenue 98289.200
`DISTINCT` after join to `EXISTS`	76 ms	32 ms	820 orders both
`OFFSET 100000` to keyset	36.5 ms	0.09 ms	`INTERSECT` = 50 rows

Run each version a few times and quote a typical run: the first run after a restart reads from disk and is slower than the rest.

Common mistakes

Optimising the query you noticed instead of the one that costs the most. Rank by total time in pg_stat_statements or Query Store first.
Changing several things at once. Make one change, measure, keep or revert. Otherwise you cannot tell which change helped, or which one broke the result.
Not checking the answer. Compare counts and totals before and after. The OR rewrite without IS DISTINCT FROM would have been faster and wrong.
Scalar subqueries per row. A SubPlan with loops= in the hundreds of thousands is the signature. Replace it with a window function or a join to a pre-aggregated set.
Casting the indexed column. WHERE id::text = '42', WHERE customer_id = 17850.0, joins on mismatched types. Cast the constant or the small side instead.
SELECT DISTINCT as a fix for duplicates. Find the join that fans out.
Deep OFFSET pagination in APIs and exports. Use a keyset on a unique, indexed sort key.
SELECT * in warehouses. In BigQuery you pay for every column you read, whether you use it or not.
Rewriting what should be precomputed. A dashboard query that scans the whole table every few minutes wants a summary table or materialized view, not a cleverer query.

Interview angle

Give three ways to speed up a query without adding hardware.

First measure: find the query with the highest total time and read its EXPLAIN ANALYZE. Then, depending on the plan: (1) make filters sargable and add or adjust an index, so the database reads a few pages instead of the table; (2) remove repeated work, for example replacing a correlated subquery that runs once per row with a window function or a join to a pre-aggregated table, or aggregating before joining; (3) reduce what is read and returned, by selecting only needed columns, using keyset pagination instead of large OFFSETs, and in warehouses filtering on partition columns. Also: fix row estimates with ANALYZE, and precompute heavy recurring queries with a materialized view. I would verify each change with before and after timings and a check that the result did not change.

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

Confirm it is slow consistently and how often it runs (pg_stat_statements, Query Store). Run EXPLAIN (ANALYZE, BUFFERS) and find the node where most time goes. Check estimated vs actual rows for big mismatches, scans that read far more rows than they return (Rows Removed by Filter), SubPlans with high loops, hash nodes with Batches above 1, sorts spilling to disk, and casts on indexed columns in Filter or Hash Cond lines. Then pick one fix, apply it, re-run, compare timing and results.

Why is OFFSET pagination slow on deep pages and what do you use instead?

The database must produce and discard every skipped row, so OFFSET 100000 reads 100,050 rows to return 50, and each page gets slower. Keyset pagination remembers the last key returned and asks for WHERE (sort_key, id) > (last_sort_key, last_id) ORDER BY sort_key, id LIMIT 50, which an index answers by seeking straight to that key. It needs a unique sort key and gives next and previous navigation rather than jumping to arbitrary page numbers.

When is a correlated subquery a performance problem?

When it is a scalar subquery evaluated per outer row and the optimizer cannot turn it into a join, typically in the SELECT list or compared with > in WHERE. The work multiplies by the number of outer rows. EXISTS and IN subqueries are usually converted to semi-joins automatically and are fine. Rewrite the problem cases with window functions (avg() OVER (PARTITION BY ...)) or by aggregating once in a derived table and joining to it.

Practice task

On the Olist tables, this query lists January 2018 orders with their number of items. It works, but has three problems:

sql

SELECT DISTINCT o.order_id, o.customer_id,
       (SELECT count(*) FROM order_items AS i2
        WHERE i2.order_id = o.order_id) AS items
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE to_char(o.order_purchase_timestamp, 'YYYY-MM') = '2018-01';

Read its plan with EXPLAIN (ANALYZE) and name the three problems.
Rewrite it, adding an index if it helps.
Prove the new version returns the same orders and the same total number of items, and write a one-line before and after note.

Show a solution

The plan of the original:

sql

SET max_parallel_workers_per_gather = 0;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT DISTINCT o.order_id, o.customer_id,
       (SELECT count(*) FROM order_items AS i2
        WHERE i2.order_id = o.order_id) AS items
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE to_char(o.order_purchase_timestamp, 'YYYY-MM') = '2018-01';

Output

SET
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Unique (actual time=95.125..96.795 rows=7220 loops=1)
   ->  Sort (actual time=95.123..95.601 rows=8208 loops=1)
         Sort Key: o.order_id, o.customer_id, ((SubPlan 1))
         Sort Method: quicksort  Memory: 1218kB
         ->  Nested Loop (actual time=0.188..88.691 rows=8208 loops=1)
               ->  Seq Scan on orders o (actual time=0.128..40.823 rows=7269 loops=1)
                     Filter: (to_char(order_purchase_timestamp, 'YYYY-MM'::text) = '2018-01'::text)
                     Rows Removed by Filter: 92172
               ->  Index Only Scan using order_items_pkey on order_items i (actual time=0.003..0.003 rows=1 loops=7269)
                     Index Cond: (order_id = o.order_id)
                     Heap Fetches: 0
               SubPlan 1
                 ->  Aggregate (actual time=0.002..0.002 rows=1 loops=8208)
                       ->  Index Only Scan using order_items_pkey on order_items i2 (actual time=0.001..0.002 rows=1 loops=8208)
                             Index Cond: (order_id = o.order_id)
                             Heap Fetches: 0
 Planning Time: 0.931 ms
 Execution Time: 97.235 ms
(18 rows)

The three problems:

Non-sargable date filter. to_char(...) on every one of 99,441 orders (Rows Removed by Filter: 92172); no index on the timestamp could be used even if one existed.
Correlated subquery. SubPlan 1 counts items once per joined row (loops=8208), recounting an order once for each of its items.
DISTINCT hiding the fan-out. The join to order_items exists only to be undone: it makes 8,208 rows that DISTINCT sorts back down to 7,220 orders.

The rewrite: a range filter on an indexed column, and one join with GROUP BY that both counts and de-duplicates:

sql

SET max_parallel_workers_per_gather = 0;
CREATE INDEX orders_purchase_idx ON orders (order_purchase_timestamp);

EXPLAIN (ANALYZE, COSTS OFF)
SELECT o.order_id, o.customer_id, count(*) AS items
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.order_purchase_timestamp >= '2018-01-01'
  AND o.order_purchase_timestamp <  '2018-02-01'
GROUP BY o.order_id, o.customer_id;

-- same answer?
SELECT 1 AS n, count(*) AS orders, sum(items) AS items FROM (
  SELECT DISTINCT o.order_id, o.customer_id,
         (SELECT count(*) FROM order_items AS i2 WHERE i2.order_id = o.order_id) AS items
  FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id
  WHERE to_char(o.order_purchase_timestamp, 'YYYY-MM') = '2018-01') AS a
UNION ALL
SELECT 2, count(*), sum(items) FROM (
  SELECT o.order_id, o.customer_id, count(*) AS items
  FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id
  WHERE o.order_purchase_timestamp >= '2018-01-01'
    AND o.order_purchase_timestamp <  '2018-02-01'
  GROUP BY o.order_id, o.customer_id) AS b
ORDER BY n;

Output

SET
CREATE INDEX
                                                                                                  QUERY PLAN                                                                                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate (actual time=50.412..52.154 rows=7220 loops=1)
   Group Key: o.order_id
   Batches: 1  Memory Usage: 1681kB
   ->  Hash Join (actual time=5.570..46.894 rows=8208 loops=1)
         Hash Cond: (i.order_id = o.order_id)
         ->  Seq Scan on order_items i (actual time=0.013..25.310 rows=112650 loops=1)
         ->  Hash (actual time=5.546..5.548 rows=7269 loops=1)
               Buckets: 8192  Batches: 1  Memory Usage: 760kB
               ->  Bitmap Heap Scan on orders o (actual time=0.797..3.585 rows=7269 loops=1)
                     Recheck Cond: ((order_purchase_timestamp >= '2018-01-01 00:00:00'::timestamp without time zone) AND (order_purchase_timestamp < '2018-02-01 00:00:00'::timestamp without time zone))
                     Heap Blocks: exact=1784
                     ->  Bitmap Index Scan on orders_purchase_idx (actual time=0.587..0.587 rows=7269 loops=1)
                           Index Cond: ((order_purchase_timestamp >= '2018-01-01 00:00:00'::timestamp without time zone) AND (order_purchase_timestamp < '2018-02-01 00:00:00'::timestamp without time zone))
 Planning Time: 0.739 ms
 Execution Time: 52.482 ms
(15 rows)
 
 n | orders | items 
---+--------+-------
 1 |   7220 |  8208
 2 |   7220 |  8208
(2 rows)

The index finds the 7,269 January orders directly, one hash join attaches their items, and one aggregate counts them. Notice Group Key: o.order_id alone: PostgreSQL knows order_id is the primary key, so grouping by customer_id as well adds nothing. Also notice 7,269 orders were found but 7,220 returned: 49 January orders have no items, and the inner join drops them in both versions.

The note: "January 2018 order list: date filter made sargable with orders_purchase_idx, subquery and DISTINCT replaced by GROUP BY. 97 ms to 52 ms; 7,220 orders and 8,208 items in both."

#Why it matters

#The concept

#Measure first, then fix the queries that matter

#The rewrite checklist

#Correctness first

#Warehouses: bytes scanned is the cost

#Worked example: six rewrites on two datasets

#Step 1: find the queries that matter

#Step 2: correlated subquery to window function

#Step 3: aggregate before joining

#Step 4: a literal or join key of the wrong type

#Step 5: OR across two tables to UNION ALL

#Step 6: DISTINCT after a join to EXISTS

#Step 7: keyset pagination instead of OFFSET

#Write a before and after note

#Common mistakes

#Interview angle

#Practice task

#Further reading

Why it matters

The concept

Measure first, then fix the queries that matter

The rewrite checklist

Correctness first

Warehouses: bytes scanned is the cost

Worked example: six rewrites on two datasets

Step 1: find the queries that matter

Step 2: correlated subquery to window function

Step 3: aggregate before joining

Step 4: a literal or join key of the wrong type

Step 5: OR across two tables to UNION ALL

Step 6: DISTINCT after a join to EXISTS

Step 7: keyset pagination instead of OFFSET

Write a before and after note

Common mistakes

Interview angle

Practice task

Further reading