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 93224960 89 MB 93
visibility_map 8192 8192 bytes 0
free_space_map 40960 40 kB 0
table_size_incl_toast 93282304 89 MB 93
indexes_size 54009856 52 MB 54
total_size_incl_toast_and_indexes 147292160 140 MB 147
live_rows_in_text_representation 67134193 64 MB 67
------------------------------ null null null
live_tuples 1000000 null null
dead_tuples 0 null null
101 rows affected
customer avg_rows
101 9901
QUERY PLAN
Subquery Scan on cte (cost=0.42..60392.43 rows=5000 width=12) (actual rows=101 loops=1)
  Filter: (cte.rn = 1)
  Rows Removed by Filter: 999899
  -> 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.136 ms
Execution Time: 916.708 ms
QUERY PLAN
Subquery Scan on sub (cost=0.42..60392.43 rows=5000 width=12) (actual rows=101 loops=1)
  Filter: (sub.rn = 1)
  Rows Removed by Filter: 999899
  -> 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.151 ms
Execution Time: 686.420 ms
QUERY PLAN
Result (cost=0.42..32892.43 rows=101 width=12) (actual rows=101 loops=1)
  -> Unique (cost=0.42..32892.43 rows=101 width=12) (actual rows=101 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.463 ms
Execution Time: 179.162 ms
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..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=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.299 ms
Execution Time: 2.347 ms
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.236 ms
Execution Time: 0.942 ms
QUERY PLAN
GroupAggregate (cost=0.42..37893.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..30392.42 rows=1000000 width=12) (actual rows=1000000 loops=1)
        Heap Fetches: 0
Planning Time: 0.178 ms
Execution Time: 481.586 ms