Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE purchases (
  id          serial
, customer_id int  -- REFERENCES customer
, total       int
, some_column text -- make row bigger, more realistic
);

CREATE TABLE


INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 100)::int               AS customer_id  -- 100 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);

INSERT 0 1000000



ALTER TABLE



CREATE INDEX


VACUUM ANALYZE purchases;  -- makes a BIG difference

VACUUM


-- 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
93184000
89 MB
93

visibility_map
8192
8192 bytes
0

free_space_map
40960
40 kB
0

table_size_incl_toast
93241344
89 MB
93

indexes_size
54018048
52 MB
54

total_size_incl_toast_and_indexes
147259392
140 MB
147

live_rows_in_text_representation
67088181
64 MB
67

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

live_tuples
1000000
null
null

dead_tuples
0
null
null



SELECT 10


-- 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;

SELECT 101


VACUUM ANALYZE customer;

VACUUM


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


customer
avg_rows


101
9901



SELECT 1


-- 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..60396.43 rows=5000 width=12) (actual rows=101 loops=1)

  Filter: (cte.rn = 1)

  Rows Removed by Filter: 999899

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

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

              Heap Fetches: 0

Planning Time: 0.128 ms

Execution Time: 788.332 ms



EXPLAIN


-- 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..60396.43 rows=5000 width=12) (actual rows=101 loops=1)

  Filter: (sub.rn = 1)

  Rows Removed by Filter: 999899

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

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

              Heap Fetches: 0

Planning Time: 0.125 ms

Execution Time: 578.947 ms



EXPLAIN


-- 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..32896.43 rows=101 width=12) (actual rows=101 loops=1)

  ->  Unique  (cost=0.42..32896.43 rows=101 width=12) (actual rows=101 loops=1)

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

              Heap Fetches: 0

Planning Time: 0.097 ms

Execution Time: 153.136 ms



EXPLAIN


-- 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=101 loops=1)

  Sort Key: cte.customer_id

  Sort Method: quicksort  Memory: 29kB

  CTE cte

    ->  Recursive Union  (cost=0.42..52.26 rows=101 width=12) (actual rows=101 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..30396.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=101)

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

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

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

                            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=101 loops=1)

Planning Time: 0.218 ms

Execution Time: 1.846 ms



EXPLAIN


-- 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..50.31 rows=101 width=12) (actual rows=101 loops=1)

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

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

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

              Index Cond: (customer_id = c.customer_id)

              Heap Fetches: 0

Planning Time: 0.175 ms

Execution Time: 0.760 ms



EXPLAIN


-- 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..37897.69 rows=101 width=12) (actual rows=101 loops=1)

  Group Key: customer_id

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

        Heap Fetches: 0

Planning Time: 0.109 ms

Execution Time: 417.093 ms



EXPLAIN
metric	bytes	bytes_pretty	bytes_per_row
core_relation_size	93184000	89 MB	93
visibility_map	8192	8192 bytes	0
free_space_map	40960	40 kB	0
table_size_incl_toast	93241344	89 MB	93
indexes_size	54018048	52 MB	54
total_size_incl_toast_and_indexes	147259392	140 MB	147
live_rows_in_text_representation	67088181	64 MB	67
------------------------------	null	null	null
live_tuples	1000000	null	null
dead_tuples	0	null	null