add batch remove batch split batch show hidden batches hide batch
db<>fiddle
feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
1000000 rows affected
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 71005895 68 MB 71
------------------------------ null null null
live_tuples 1000000 null null
dead_tuples 0 null null
632079 rows affected
customer avg_rows
632079 1.5820807209225429
QUERY PLAN
Subquery Scan on cte (cost=0.42..60392.43 rows=5000 width=12) (actual rows=632079 loops=1)
  Filter: (cte.rn = 1)
  Rows Removed by Filter: 367921
  -> 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.317 ms
Execution Time: 860.144 ms
QUERY PLAN
Subquery Scan on sub (cost=0.42..60392.43 rows=5000 width=12) (actual rows=632079 loops=1)
  Filter: (sub.rn = 1)
  Rows Removed by Filter: 367921
  -> 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.149 ms
Execution Time: 855.671 ms
QUERY PLAN
Result (cost=0.42..32892.43 rows=496335 width=12) (actual rows=632079 loops=1)
  -> Unique (cost=0.42..32892.43 rows=496335 width=12) (actual rows=632079 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.156 ms
Execution Time: 303.901 ms
QUERY PLAN
Sort (cost=57.65..57.90 rows=101 width=12) (actual rows=632079 loops=1)
  Sort Key: cte.customer_id
  Sort Method: external merge Disk: 13664kB
  CTE cte
    -> Recursive Union (cost=0.42..52.26 rows=101 width=12) (actual rows=632079 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=632079)
                -> WorkTable Scan on cte c (cost=0.00..0.20 rows=10 width=4) (actual rows=1 loops=632079)
                -> Limit (cost=0.42..0.46 rows=1 width=12) (actual rows=1 loops=632079)
                      -> Index Only Scan using purchases_3c_idx on purchases purchases_1 (cost=0.42..10965.75 rows=333333 width=12) (actual rows=1 loops=632079)
                            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=632079 loops=1)
Planning Time: 0.299 ms
Execution Time: 6202.791 ms
QUERY PLAN
Nested Loop (cost=0.42..1565612.33 rows=632079 width=12) (actual rows=632079 loops=1)
  -> Seq Scan on customer c (cost=0.00..9117.79 rows=632079 width=4) (actual rows=632079 loops=1)
  -> Limit (cost=0.42..2.44 rows=1 width=12) (actual rows=1 loops=632079)
        -> Index Only Scan using purchases_3c_idx on purchases (cost=0.42..4.46 rows=2 width=12) (actual rows=1 loops=632079)
              Index Cond: (customer_id = c.customer_id)
              Heap Fetches: 0
Planning Time: 0.263 ms
Execution Time: 1474.733 ms
QUERY PLAN
GroupAggregate (cost=0.42..44096.61 rows=496335 width=12) (actual rows=632079 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.513 ms
Execution Time: 1622.660 ms