Reference

Appendix: Query Plans

Raw EXPLAIN ANALYZE output for every documentation example

Every benchmark result in this documentation links here. These are the raw EXPLAIN ANALYZE plans that Postgres produced, one before and one after per example. Plans are shown at the 500K row scale point only, as that's where the most interesting plan behavior occurs (parallel workers, plan strategy changes). Including plans for every scale point would make this appendix unreadably long.

Query Optimization: Orders + Customers (500K orders)

[Qo1] Without index — Parallel Seq Scan, 110.9 ms

Limit  (cost=9119.77..9122.11 rows=20 width=31) (actual time=108.180..110.847 rows=20 loops=1)
  ->  Gather Merge  (cost=9119.77..18758.28 rows=82610 width=31) (actual time=108.179..110.843 rows=20 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=8119.75..8223.01 rows=41305 width=31) (actual time=98.359..98.362 rows=16 loops=3)
              Sort Key: o.created_at DESC
              Sort Method: top-N heapsort  Memory: 27kB
              ->  Hash Join  (cost=329.00..7020.64 rows=41305 width=31) (actual time=9.304..90.232 rows=33240 loops=3)
                    Hash Cond: (o.customer_id = c.id)
                    ->  Parallel Seq Scan on orders o  (cost=0.00..6583.17 rows=41305 width=22) (actual time=0.011..50.505 rows=33240 loops=3)
                          Filter: ((status)::text = 'shipped'::text)
                          Rows Removed by Filter: 133427
                    ->  Hash  (cost=204.00..204.00 rows=10000 width=17) (actual time=9.198..9.199 rows=10000 loops=3)
                          Buckets: 16384  Batches: 1  Memory Usage: 636kB
                          ->  Seq Scan on customers c  (cost=0.00..204.00 rows=10000 width=17) (actual time=0.007..4.113 rows=10000 loops=3)
Planning Time: 0.362 ms
Execution Time: 110.879 ms

[Qo2] With index on (status, created_at DESC) — Index Scan + Nested Loop, 0.14 ms

Limit  (cost=0.72..5.75 rows=20 width=31) (actual time=0.034..0.117 rows=20 loops=1)
  ->  Nested Loop  (cost=0.72..25085.57 rows=99650 width=31) (actual time=0.033..0.114 rows=20 loops=1)
        ->  Index Scan using idx_orders_status_created on orders o  (cost=0.42..19538.84 rows=99650 width=22) (actual time=0.015..0.045 rows=20 loops=1)
              Index Cond: ((status)::text = 'shipped'::text)
        ->  Memoize  (cost=0.30..0.32 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=20)
              Cache Key: o.customer_id
              Cache Mode: logical
              Hits: 0  Misses: 20  Evictions: 0  Overflows: 0  Memory Usage: 3kB
              ->  Index Scan using customers_pkey on customers c  (cost=0.29..0.31 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=20)
                    Index Cond: (id = o.customer_id)
Planning Time: 0.434 ms
Execution Time: 0.140 ms

Scaling Analysis: User Event Lookup (500K events)

[Sc1] Without index — Parallel Seq Scan, 40.3 ms

Limit  (cost=13221.10..13222.26 rows=10 width=19) (actual time=37.625..40.237 rows=10 loops=1)
  ->  Gather Merge  (cost=13221.10..13230.90 rows=84 width=19) (actual time=37.624..40.235 rows=10 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=12221.07..12221.18 rows=42 width=19) (actual time=28.691..28.693 rows=8 loops=3)
              Sort Key: created_at DESC
              Sort Method: top-N heapsort  Memory: 26kB
              ->  Parallel Seq Scan on events  (cost=0.00..12220.17 rows=42 width=19) (actual time=2.086..28.617 rows=32 loops=3)
                    Filter: (user_id = 42)
                    Rows Removed by Filter: 166634
Planning Time: 0.141 ms
Execution Time: 40.259 ms

[Sc2] With index on (user_id, created_at DESC) — Index Scan, 0.04 ms

Limit  (cost=0.42..41.00 rows=10 width=19) (actual time=0.013..0.029 rows=10 loops=1)
  ->  Index Scan using idx_events_user_created on events  (cost=0.42..406.17 rows=100 width=19) (actual time=0.012..0.027 rows=10 loops=1)
        Index Cond: (user_id = 42)
Planning Time: 0.161 ms
Execution Time: 0.042 ms

Query Technique Validation: OFFSET vs Cursor (500K posts)

[Tv1] OFFSET/LIMIT — Index Scan (walks 50K entries then returns 20), 223.4 ms

Limit  (cost=9690.68..9694.56 rows=20 width=29) (actual time=223.315..223.354 rows=20 loops=1)
  ->  Index Scan using idx_posts_published_created on posts  (cost=0.42..82519.35 rows=425783 width=29) (actual time=0.034..218.027 rows=50020 loops=1)
        Index Cond: (published = true)
Planning Time: 0.178 ms
Execution Time: 223.372 ms

[Tv2] Cursor pagination — Index Scan (reads exactly 20 rows), 0.12 ms

Limit  (cost=0.42..6.24 rows=20 width=29) (actual time=0.028..0.108 rows=20 loops=1)
  ->  Index Scan using idx_posts_published_created on posts  (cost=0.42..75267.52 rows=258741 width=29) (actual time=0.027..0.105 rows=20 loops=1)
        Index Cond: ((published = true) AND (created_at < '2025-06-15 12:00:00'::timestamp without time zone))
Planning Time: 0.189 ms
Execution Time: 0.122 ms

Data Distribution Impact: Selective vs Non-Selective (500K transactions)

[Dd1] status = 'failed' (0.5% of rows) — Index Scan, 3.9 ms

Limit  (cost=2771.30..2771.35 rows=20 width=44) (actual time=3.877..3.880 rows=20 loops=1)
  ->  Sort  (cost=2771.30..2776.90 rows=2243 width=44) (actual time=3.875..3.877 rows=20 loops=1)
        Sort Key: (sum(amount)) DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  HashAggregate  (cost=2683.57..2711.61 rows=2243 width=44) (actual time=2.831..3.547 rows=2157 loops=1)
              Group Key: account_id
              Batches: 1  Memory Usage: 1137kB
              ->  Index Scan using idx_transactions_status on transactions  (cost=0.42..2664.57 rows=2533 width=10) (actual time=0.039..1.927 rows=2446 loops=1)
                    Index Cond: ((status)::text = 'failed'::text)
Planning Time: 2.214 ms
Execution Time: 3.940 ms

[Dd2] status = 'completed' (90% of rows) — Parallel Seq Scan (index ignored), 280.5 ms

Limit  (cost=11854.70..11854.75 rows=20 width=44) (actual time=280.197..280.330 rows=20 loops=1)
  ->  Sort  (cost=11854.70..11879.73 rows=10010 width=44) (actual time=280.196..280.326 rows=20 loops=1)
        Sort Key: (sum(amount)) DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Finalize HashAggregate  (cost=11463.22..11588.34 rows=10010 width=44) (actual time=274.437..278.835 rows=10000 loops=1)
              Group Key: account_id
              Batches: 1  Memory Usage: 5521kB
              ->  Gather  (cost=9135.89..11263.02 rows=20020 width=44) (actual time=231.569..253.682 rows=30000 loops=1)
                    Workers Planned: 2
                    Workers Launched: 2
                    ->  Partial HashAggregate  (cost=8135.89..8261.02 rows=10010 width=44) (actual time=225.514..229.929 rows=10000 loops=3)
                          Group Key: account_id
                          Batches: 1  Memory Usage: 4241kB
                          ->  Parallel Seq Scan on transactions  (cost=0.00..6730.17 rows=187430 width=10) (actual time=0.010..49.075 rows=150042 loops=3)
                                Filter: ((status)::text = 'completed'::text)
                                Rows Removed by Filter: 16624
Planning Time: 1.637 ms
Execution Time: 280.468 ms