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 18612224 18 MB 93
visibility_map 8192 8192 bytes 0
free_space_map 24576 24 kB 0
table_size_incl_toast 18653184 18 MB 93
indexes_size 10838016 10 MB 54
total_size_incl_toast_and_indexes 29491200 28 MB 147
live_rows_in_text_representation 13896716 13 MB 69
------------------------------ null null null
live_tuples 200000 null null
dead_tuples 0 null null
86526 rows affected
customer avg_rows
86526 2.3114439590412130
QUERY PLAN
Subquery Scan on cte (cost=0.42..12088.42 rows=1000 width=12) (actual rows=86526 loops=1)
  Filter: (cte.rn = 1)
  Rows Removed by Filter: 113474
  -> 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.250 ms
Execution Time: 170.872 ms
QUERY PLAN
Subquery Scan on sub (cost=0.42..12088.42 rows=1000 width=12) (actual rows=86526 loops=1)
  Filter: (sub.rn = 1)
  Rows Removed by Filter: 113474
  -> 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.183 ms
Execution Time: 164.091 ms
QUERY PLAN
Result (cost=0.42..6588.42 rows=71389 width=12) (actual rows=86526 loops=1)
  -> Unique (cost=0.42..6588.42 rows=71389 width=12) (actual rows=86526 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.132 ms
Execution Time: 49.795 ms
QUERY PLAN
Sort (cost=57.15..57.40 rows=101 width=12) (actual rows=86526 loops=1)
  Sort Key: cte.customer_id
  Sort Method: external merge Disk: 1872kB
  CTE cte
    -> Recursive Union (cost=0.42..51.77 rows=101 width=12) (actual rows=86526 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=86526)
                -> WorkTable Scan on cte c (cost=0.00..0.20 rows=10 width=4) (actual rows=1 loops=86526)
                -> Limit (cost=0.42..0.45 rows=1 width=12) (actual rows=1 loops=86526)
                      -> Index Only Scan using purchases_3c_idx on purchases purchases_1 (cost=0.42..2199.09 rows=66667 width=12) (actual rows=1 loops=86526)
                            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=86526 loops=1)
Planning Time: 0.259 ms
Execution Time: 877.177 ms
QUERY PLAN
Nested Loop (cost=0.42..156201.91 rows=86526 width=12) (actual rows=86526 loops=1)
  -> Seq Scan on customer c (cost=0.00..1248.26 rows=86526 width=4) (actual rows=86526 loops=1)
  -> Limit (cost=0.42..1.77 rows=1 width=12) (actual rows=1 loops=86526)
        -> Index Only Scan using purchases_3c_idx on purchases (cost=0.42..4.47 rows=3 width=12) (actual rows=1 loops=86526)
              Index Cond: (customer_id = c.customer_id)
              Heap Fetches: 0
Planning Time: 0.211 ms
Execution Time: 202.187 ms
QUERY PLAN
GroupAggregate (cost=0.42..8480.78 rows=71389 width=12) (actual rows=86526 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.208 ms
Execution Time: 242.620 ms