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.
200000 rows affected
metric bytes bytes_pretty bytes_per_row
core_relation_size 18604032 18 MB 93
visibility_map 8192 8192 bytes 0
free_space_map 24576 24 kB 0
table_size_incl_toast 18644992 18 MB 93
indexes_size 10838016 10 MB 54
total_size_incl_toast_and_indexes 29483008 28 MB 147
live_rows_in_text_representation 13693342 13 MB 68
------------------------------ null null null
live_tuples 200000 null null
dead_tuples 0 null null
10001 rows affected
customer avg_rows
10001 20
QUERY PLAN
Subquery Scan on cte (cost=0.42..12088.42 rows=1000 width=12) (actual rows=10001 loops=1)
  Filter: (cte.rn = 1)
  Rows Removed by Filter: 189999
  -> WindowAgg (cost=0.42..9588.42 rows=200000 width=20) (actual rows=200000 loops=1)
        -> Index Only Scan using purchases_3c_idx on purchases (cost=0.42..6088.42 rows=200000 width=12) (actual rows=200000 loops=1)
              Heap Fetches: 0
Planning Time: 0.116 ms
Execution Time: 99.945 ms
QUERY PLAN
Subquery Scan on sub (cost=0.42..12088.42 rows=1000 width=12) (actual rows=10001 loops=1)
  Filter: (sub.rn = 1)
  Rows Removed by Filter: 189999
  -> WindowAgg (cost=0.42..9588.42 rows=200000 width=20) (actual rows=200000 loops=1)
        -> Index Only Scan using purchases_3c_idx on purchases (cost=0.42..6088.42 rows=200000 width=12) (actual rows=200000 loops=1)
              Heap Fetches: 0
Planning Time: 0.114 ms
Execution Time: 99.471 ms
QUERY PLAN
Result (cost=0.42..6588.42 rows=9972 width=12) (actual rows=10001 loops=1)
  -> Unique (cost=0.42..6588.42 rows=9972 width=12) (actual rows=10001 loops=1)
        -> Index Only Scan using purchases_3c_idx on purchases (cost=0.42..6088.42 rows=200000 width=12) (actual rows=200000 loops=1)
              Heap Fetches: 0
Planning Time: 0.090 ms
Execution Time: 22.794 ms
QUERY PLAN
Sort (cost=57.15..57.40 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..51.77 rows=101 width=12) (actual rows=10001 loops=1)
          -> Limit (cost=0.42..0.45 rows=1 width=12) (actual rows=1 loops=1)
                -> Index Only Scan using purchases_3c_idx on purchases (cost=0.42..6088.42 rows=200000 width=12) (actual rows=1 loops=1)
                      Heap Fetches: 0
          -> Nested Loop (cost=0.42..4.93 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.45 rows=1 width=12) (actual rows=1 loops=10001)
                      -> Index Only Scan using purchases_3c_idx on purchases purchases_1 (cost=0.42..2199.09 rows=66667 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.189 ms
Execution Time: 67.862 ms
QUERY PLAN
Nested Loop (cost=0.42..6720.67 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.64 rows=1 width=12) (actual rows=1 loops=10001)
        -> Index Only Scan using purchases_3c_idx on purchases (cost=0.42..4.77 rows=20 width=12) (actual rows=1 loops=10001)
              Index Cond: (customer_id = c.customer_id)
              Heap Fetches: 0
Planning Time: 0.149 ms
Execution Time: 23.102 ms
QUERY PLAN
GroupAggregate (cost=0.42..7713.07 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..6088.42 rows=200000 width=12) (actual rows=200000 loops=1)
        Heap Fetches: 0
Planning Time: 0.133 ms
Execution Time: 80.159 ms