Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE purchases (
  id          serial
, customer_id int  -- REFERENCES customer
, total       int
, some_column text -- make row bigger, more realistic
);
INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,1000000) g;  -- 1M rows

ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id);

CREATE INDEX purchases_3c_idx ON purchases (customer_id, total DESC, id);
1000000 rows affected

VACUUM ANALYZE purchases;  -- makes a BIG difference
-- metrics (and prewarm cache)
SELECT l.metric, l.nr AS bytes
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- = 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   purchases t
   ) x
CROSS  JOIN LATERAL (
   VALUES
     (true , 'core_relation_size'               , pg_relation_size(tbl))
   , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
   , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
   , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
   , (true , 'indexes_size'                     , pg_indexes_size(tbl))
   , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
   , (true , 'live_rows_in_text_representation' , txt_len)
   , (false, '------------------------------'   , NULL)
   , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
   , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, metric, nr);


metric
bytes
bytes_pretty
bytes_per_row


core_relation_size
93118464
89 MB
93

visibility_map
8192
8192 bytes
0

free_space_map
40960
40 kB
0

table_size_incl_toast
93175808
89 MB
93

indexes_size
54009856
52 MB
54

total_size_incl_toast_and_indexes
147185664
140 MB
147

live_rows_in_text_representation
69003182
66 MB
69

------------------------------
null
null
null

live_tuples
1000000
null
null

dead_tuples
0
null
null


-- customer table for query 5. (and prewarm cache)
CREATE TABLE customer AS
WITH RECURSIVE cte AS (
   SELECT min(customer_id) AS customer_id FROM purchases

   UNION ALL
   SELECT (SELECT p.customer_id
           FROM   purchases p
           WHERE  p.customer_id > c.customer_id
           ORDER  BY p.customer_id
           LIMIT  1)
   FROM   cte c
   WHERE  c.customer_id IS NOT NULL
   )
SELECT * FROM cte
WHERE  customer_id IS NOT NULL;
10001 rows affected

VACUUM ANALYZE customer;
-- avg rows per customer
SELECT count(*) AS customer, round(1000000.0 / count(*)) AS avg_rows FROM customer;


customer
avg_rows


10001
100


-- 1.
EXPLAIN (ANALYZE, TIMING OFF)
WITH cte AS (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   )
SELECT id, customer_id, total
FROM   cte
WHERE  rn = 1;


QUERY PLAN


Subquery Scan on cte  (cost=0.42..60392.43 rows=5000 width=12) (actual rows=10001 loops=1)

  Filter: (cte.rn = 1)

  Rows Removed by Filter: 989999

  ->  WindowAgg  (cost=0.42..47892.43 rows=1000000 width=20) (actual rows=1000000 loops=1)

        ->  Index Only Scan using purchases_3c_idx on purchases  (cost=0.42..30392.42 rows=1000000 width=12) (actual rows=1000000 loops=1)

              Heap Fetches: 0

Planning Time: 0.320 ms

Execution Time: 520.848 ms


-- 2.
EXPLAIN (ANALYZE, TIMING OFF)
SELECT id, customer_id, total
FROM   (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   ) sub
WHERE  rn = 1;


QUERY PLAN


Subquery Scan on sub  (cost=0.42..60392.43 rows=5000 width=12) (actual rows=10001 loops=1)

  Filter: (sub.rn = 1)

  Rows Removed by Filter: 989999

  ->  WindowAgg  (cost=0.42..47892.43 rows=1000000 width=20) (actual rows=1000000 loops=1)

        ->  Index Only Scan using purchases_3c_idx on purchases  (cost=0.42..30392.42 rows=1000000 width=12) (actual rows=1000000 loops=1)

              Heap Fetches: 0

Planning Time: 0.116 ms

Execution Time: 514.092 ms


-- 3.
EXPLAIN (ANALYZE, TIMING OFF)
SELECT DISTINCT ON (customer_id)
       id, customer_id, total
FROM   purchases
ORDER  BY customer_id, total DESC, id;


QUERY PLAN


Result  (cost=0.42..32892.43 rows=9972 width=12) (actual rows=10001 loops=1)

  ->  Unique  (cost=0.42..32892.43 rows=9972 width=12) (actual rows=10001 loops=1)

        ->  Index Only Scan using purchases_3c_idx on purchases  (cost=0.42..30392.42 rows=1000000 width=12) (actual rows=1000000 loops=1)

              Heap Fetches: 0

Planning Time: 0.094 ms

Execution Time: 129.836 ms


-- 4.
EXPLAIN (ANALYZE, TIMING OFF)
WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT id, customer_id, total
   FROM   purchases
   ORDER  BY customer_id, total DESC
   LIMIT  1
   )
   UNION ALL
   SELECT u.*
   FROM   cte c
   ,      LATERAL (
      SELECT id, customer_id, total
      FROM   purchases
      WHERE  customer_id > c.customer_id  -- lateral reference
      ORDER  BY customer_id, total DESC
      LIMIT  1
      ) u
   )
SELECT id, customer_id, total
FROM   cte
ORDER  BY customer_id;


QUERY PLAN


Sort  (cost=57.65..57.90 rows=101 width=12) (actual rows=10001 loops=1)

  Sort Key: cte.customer_id

  Sort Method: quicksort  Memory: 853kB

  CTE cte

    ->  Recursive Union  (cost=0.42..52.26 rows=101 width=12) (actual rows=10001 loops=1)

          ->  Limit  (cost=0.42..0.46 rows=1 width=12) (actual rows=1 loops=1)

                ->  Index Only Scan using purchases_3c_idx on purchases  (cost=0.42..30392.42 rows=1000000 width=12) (actual rows=1 loops=1)

                      Heap Fetches: 0

          ->  Nested Loop  (cost=0.42..4.98 rows=10 width=12) (actual rows=1 loops=10001)

                ->  WorkTable Scan on cte c  (cost=0.00..0.20 rows=10 width=4) (actual rows=1 loops=10001)

                ->  Limit  (cost=0.42..0.46 rows=1 width=12) (actual rows=1 loops=10001)

                      ->  Index Only Scan using purchases_3c_idx on purchases purchases_1  (cost=0.42..10965.75 rows=333333 width=12) (actual rows=1 loops=10001)

                            Index Cond: (customer_id > c.customer_id)

                            Heap Fetches: 0

  ->  CTE Scan on cte  (cost=0.00..2.02 rows=101 width=12) (actual rows=10001 loops=1)

Planning Time: 0.200 ms

Execution Time: 130.722 ms


-- 5.
EXPLAIN (ANALYZE, TIMING OFF)
SELECT l.*
FROM   customer c
,      LATERAL (
   SELECT id, customer_id, total
   FROM   purchases
   WHERE  customer_id = c.customer_id  -- lateral reference
   ORDER  BY total DESC
   LIMIT  1
   ) l;


QUERY PLAN


Nested Loop  (cost=0.42..5170.51 rows=10001 width=12) (actual rows=10001 loops=1)

  ->  Seq Scan on customer c  (cost=0.00..145.01 rows=10001 width=4) (actual rows=10001 loops=1)

  ->  Limit  (cost=0.42..0.48 rows=1 width=12) (actual rows=1 loops=10001)

        ->  Index Only Scan using purchases_3c_idx on purchases  (cost=0.42..6.17 rows=100 width=12) (actual rows=1 loops=10001)

              Index Cond: (customer_id = c.customer_id)

              Heap Fetches: 0

Planning Time: 0.213 ms

Execution Time: 76.748 ms


-- 6.
EXPLAIN (ANALYZE, TIMING OFF)
SELECT (array_agg(id ORDER BY total DESC))[1] AS id
     , customer_id
     , max(total) AS total
FROM   purchases
GROUP  BY customer_id;


QUERY PLAN


GroupAggregate  (cost=0.42..38017.08 rows=9972 width=12) (actual rows=10001 loops=1)

  Group Key: customer_id

  ->  Index Only Scan using purchases_3c_idx on purchases  (cost=0.42..30392.42 rows=1000000 width=12) (actual rows=1000000 loops=1)

        Heap Fetches: 0

Planning Time: 0.179 ms

Execution Time: 373.340 ms
metric	bytes	bytes_pretty	bytes_per_row
core_relation_size	93118464	89 MB	93
visibility_map	8192	8192 bytes	0
free_space_map	40960	40 kB	0
table_size_incl_toast	93175808	89 MB	93
indexes_size	54009856	52 MB	54
total_size_incl_toast_and_indexes	147185664	140 MB	147
live_rows_in_text_representation	69003182	66 MB	69
------------------------------	null	null	null
live_tuples	1000000	null	null
dead_tuples	0	null	null