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 69003182 66 MB 69
------------------------------ null null null
live_tuples 1000000 null null
dead_tuples 0 null null
10001 rows affected
customer avg_rows
10001 100
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
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
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
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
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
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