add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE
INSERT 0 200000
ALTER TABLE
CREATE INDEX
VACUUM
metric bytes bytes_pretty bytes_per_row
core_relation_size 18644992 18 MB 93
visibility_map 8192 8192 bytes 0
free_space_map 24576 24 kB 0
table_size_incl_toast 18685952 18 MB 93
indexes_size 10838016 10 MB 54
total_size_incl_toast_and_indexes 29523968 28 MB 147
live_rows_in_text_representation 13730858 13 MB 68
------------------------------ null null null
live_tuples 200000 null null
dead_tuples 0 null null
SELECT 10
SELECT 10001
VACUUM
customer avg_rows
10001 20
SELECT 1
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.188 ms
Execution Time: 102.810 ms
EXPLAIN
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.117 ms
Execution Time: 99.210 ms
EXPLAIN
QUERY PLAN
Result (cost=0.42..6588.42 rows=9929 width=12) (actual rows=10001 loops=1)
  -> Unique (cost=0.42..6588.42 rows=9929 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.096 ms
Execution Time: 23.616 ms
EXPLAIN
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.202 ms
Execution Time: 65.927 ms
EXPLAIN
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.156 ms
Execution Time: 23.371 ms
EXPLAIN
QUERY PLAN
GroupAggregate (cost=0.42..7712.53 rows=9929 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.128 ms
Execution Time: 80.805 ms
EXPLAIN