Postgres 15

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?.
-- Demonstrate pointless, costly RANGE mode for row_number()
CREATE TABLE tbl(tbl_id int, a int, b int);

INSERT INTO tbl
SELECT g, (random() * 5000)::int, (random() * 250000)::int
FROM   generate_series(1, 1000000) g;

ALTER TABLE tbl ADD CONSTRAINT tbl_pkey PRIMARY KEY (tbl_id);
CREATE INDEX tbl_a_b_idx ON tbl (a, b);

CREATE TABLE

INSERT 0 1000000

ALTER TABLE

CREATE INDEX

VACUUM ANALYZE tbl;

VACUUM

SET jit = off;  -- suppress this noise

SET

-- ORDER BY tbl_id
-- prewarm cache
DO
$$
BEGIN
PERFORM tbl_id FROM tbl ORDER BY 1;
PERFORM tbl_id FROM tbl ORDER BY 1;
END
$$

DO

-- default RANGE mode
EXPLAIN ANALYZE
SELECT row_number() OVER (ORDER BY tbl_id) FROM tbl;

QUERY PLAN

WindowAgg  (cost=0.42..40980.43 rows=1000000 width=12) (actual time=0.030..537.493 rows=1000000 loops=1)

  ->  Index Only Scan using tbl_pkey on tbl  (cost=0.42..25980.42 rows=1000000 width=4) (actual time=0.018..134.592 rows=1000000 loops=1)

        Heap Fetches: 0

Planning Time: 0.106 ms

Execution Time: 582.985 ms

EXPLAIN

-- ROWS mode
EXPLAIN ANALYZE
SELECT row_number() OVER (ORDER BY tbl_id ROWS UNBOUNDED PRECEDING) FROM tbl;

QUERY PLAN

WindowAgg  (cost=0.42..40980.43 rows=1000000 width=12) (actual time=0.026..444.302 rows=1000000 loops=1)

  ->  Index Only Scan using tbl_pkey on tbl  (cost=0.42..25980.42 rows=1000000 width=4) (actual time=0.021..140.207 rows=1000000 loops=1)

        Heap Fetches: 0

Planning Time: 0.104 ms

Execution Time: 491.336 ms

EXPLAIN

-- repeat default RANGE mode
EXPLAIN ANALYZE
SELECT row_number() OVER (ORDER BY tbl_id) FROM tbl;

QUERY PLAN

WindowAgg  (cost=0.42..40980.43 rows=1000000 width=12) (actual time=0.025..542.021 rows=1000000 loops=1)

  ->  Index Only Scan using tbl_pkey on tbl  (cost=0.42..25980.42 rows=1000000 width=4) (actual time=0.018..136.208 rows=1000000 loops=1)

        Heap Fetches: 0

Planning Time: 0.114 ms

Execution Time: 587.892 ms

EXPLAIN

-- repeat ROWS mode
EXPLAIN ANALYZE
SELECT row_number() OVER (ORDER BY tbl_id ROWS UNBOUNDED PRECEDING) FROM tbl;

QUERY PLAN

WindowAgg  (cost=0.42..40980.43 rows=1000000 width=12) (actual time=0.025..466.818 rows=1000000 loops=1)

  ->  Index Only Scan using tbl_pkey on tbl  (cost=0.42..25980.42 rows=1000000 width=4) (actual time=0.020..147.411 rows=1000000 loops=1)

        Heap Fetches: 0

Planning Time: 0.109 ms

Execution Time: 516.350 ms

EXPLAIN

-- ORDER BY a, b
-- prewarm cache
DO
$$
BEGIN
PERFORM a, b FROM tbl ORDER BY 1, 2;
PERFORM a, b FROM tbl ORDER BY 1, 2;
END
$$

DO

-- default RANGE mode
EXPLAIN ANALYZE
SELECT row_number() OVER (ORDER BY a, b) FROM tbl;

QUERY PLAN

WindowAgg  (cost=0.42..43480.43 rows=1000000 width=16) (actual time=0.038..580.506 rows=1000000 loops=1)

  ->  Index Only Scan using tbl_a_b_idx on tbl  (cost=0.42..25980.42 rows=1000000 width=8) (actual time=0.026..144.195 rows=1000000 loops=1)

        Heap Fetches: 0

Planning Time: 0.103 ms

Execution Time: 627.200 ms

EXPLAIN

-- ROWS mode
EXPLAIN ANALYZE
SELECT row_number() OVER (ORDER BY a, b ROWS UNBOUNDED PRECEDING) FROM tbl;

QUERY PLAN

WindowAgg  (cost=0.42..43480.43 rows=1000000 width=16) (actual time=0.024..453.886 rows=1000000 loops=1)

  ->  Index Only Scan using tbl_a_b_idx on tbl  (cost=0.42..25980.42 rows=1000000 width=8) (actual time=0.019..141.467 rows=1000000 loops=1)

        Heap Fetches: 0

Planning Time: 0.104 ms

Execution Time: 499.599 ms

EXPLAIN

-- repeat default RANGE mode
EXPLAIN ANALYZE
SELECT row_number() OVER (ORDER BY a, b) FROM tbl;

QUERY PLAN

WindowAgg  (cost=0.42..43480.43 rows=1000000 width=16) (actual time=0.028..609.581 rows=1000000 loops=1)

  ->  Index Only Scan using tbl_a_b_idx on tbl  (cost=0.42..25980.42 rows=1000000 width=8) (actual time=0.021..153.544 rows=1000000 loops=1)

        Heap Fetches: 0

Planning Time: 0.137 ms

Execution Time: 661.260 ms

EXPLAIN

-- repeat ROWS mode
EXPLAIN ANALYZE
SELECT row_number() OVER (ORDER BY a, b ROWS UNBOUNDED PRECEDING) FROM tbl;

QUERY PLAN

WindowAgg  (cost=0.42..43480.43 rows=1000000 width=16) (actual time=0.049..466.140 rows=1000000 loops=1)

  ->  Index Only Scan using tbl_a_b_idx on tbl  (cost=0.42..25980.42 rows=1000000 width=8) (actual time=0.022..145.441 rows=1000000 loops=1)

        Heap Fetches: 0

Planning Time: 0.106 ms

Execution Time: 513.324 ms

EXPLAIN

-- PARTITION BY a ORDER BY b
-- default RANGE mode
EXPLAIN ANALYZE
SELECT row_number() OVER (PARTITION BY a ORDER BY b) FROM tbl;

QUERY PLAN

WindowAgg  (cost=0.42..43480.43 rows=1000000 width=16) (actual time=0.028..653.185 rows=1000000 loops=1)

  ->  Index Only Scan using tbl_a_b_idx on tbl  (cost=0.42..25980.42 rows=1000000 width=8) (actual time=0.022..160.804 rows=1000000 loops=1)

        Heap Fetches: 0

Planning Time: 0.101 ms

Execution Time: 700.927 ms

EXPLAIN

-- ROWS mode
EXPLAIN ANALYZE
SELECT row_number() OVER (PARTITION BY a ORDER BY b ROWS UNBOUNDED PRECEDING) FROM tbl;

QUERY PLAN

WindowAgg  (cost=0.42..43480.43 rows=1000000 width=16) (actual time=0.023..506.921 rows=1000000 loops=1)

  ->  Index Only Scan using tbl_a_b_idx on tbl  (cost=0.42..25980.42 rows=1000000 width=8) (actual time=0.020..160.835 rows=1000000 loops=1)

        Heap Fetches: 0

Planning Time: 0.120 ms

Execution Time: 556.602 ms

EXPLAIN

-- repeat default RANGE mode
EXPLAIN ANALYZE
SELECT row_number() OVER (PARTITION BY a ORDER BY b) FROM tbl;

QUERY PLAN

WindowAgg  (cost=0.42..43480.43 rows=1000000 width=16) (actual time=0.026..659.337 rows=1000000 loops=1)

  ->  Index Only Scan using tbl_a_b_idx on tbl  (cost=0.42..25980.42 rows=1000000 width=8) (actual time=0.021..162.047 rows=1000000 loops=1)

        Heap Fetches: 0

Planning Time: 0.099 ms

Execution Time: 707.532 ms

EXPLAIN

-- repeat ROWS mode
EXPLAIN ANALYZE
SELECT row_number() OVER (PARTITION BY a ORDER BY b ROWS UNBOUNDED PRECEDING) FROM tbl;

QUERY PLAN

WindowAgg  (cost=0.42..43480.43 rows=1000000 width=16) (actual time=0.023..503.685 rows=1000000 loops=1)

  ->  Index Only Scan using tbl_a_b_idx on tbl  (cost=0.42..25980.42 rows=1000000 width=8) (actual time=0.020..160.492 rows=1000000 loops=1)

        Heap Fetches: 0

Planning Time: 0.099 ms

Execution Time: 552.748 ms

EXPLAIN