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 1000000
ALTER TABLE
CREATE INDEX
VACUUM
metric bytes bytes_pretty bytes_per_row
core_relation_size 93036544 89 MB 93
visibility_map 8192 8192 bytes 0
free_space_map 40960 40 kB 0
table_size_incl_toast 93093888 89 MB 93
indexes_size 54009856 52 MB 54
total_size_incl_toast_and_indexes 147103744 140 MB 147
live_rows_in_text_representation 66942887 64 MB 66
------------------------------ null null null
live_tuples 1000000 null null
dead_tuples 0 null null
SELECT 10
SELECT 101
VACUUM
customer avg_rows
101 9901
SELECT 1
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.154 ms
Execution Time: 595.033 ms
EXPLAIN
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.117 ms
Execution Time: 543.665 ms
EXPLAIN
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.112 ms
Execution Time: 143.127 ms
EXPLAIN
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.217 ms
Execution Time: 1.762 ms
EXPLAIN
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.160 ms
Execution Time: 0.782 ms
EXPLAIN
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.168 ms
Execution Time: 410.847 ms
EXPLAIN