Postgres 16

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 integers AS 
SELECT generate_series(0, 1000000) i;

CREATE TEMP SEQUENCE seq_1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 999999999
    START 1
    CACHE 1
    CYCLE;

SELECT 1000001



CREATE SEQUENCE


-- matching your (undeclared) seuqence state of seq_1 = 46137349
ALTER SEQUENCE seq_1 RESTART WITH 46137349;

SELECT *, nextval('seq_1') AS n2
FROM  (
   SELECT *, n1 - i AS d, sum(i) OVER (ORDER BY i) AS s
   FROM  (
      SELECT i, nextval('seq_1') AS n1
      FROM integers
   )
)
WHERE i = 0 OR i = 1000000;

ALTER SEQUENCE




i
n1
d
s
n2


0
46137349
46137349
0
47137350

1000000
47137349
46137349
500000500000
47137351



SELECT 2


-- starting from seq_1 = 1
ALTER SEQUENCE seq_1 RESTART WITH 1;

SELECT *, nextval('seq_1') AS n2
FROM  (
   SELECT *, n1 - i AS d, sum(i) OVER (ORDER BY i) AS s
   FROM  (
      SELECT i, nextval('seq_1') AS n1
      FROM integers
   )
)
WHERE i = 0 OR i = 1000000;

ALTER SEQUENCE




i
n1
d
s
n2


0
1
1
0
1000002

1000000
1000001
1
500000500000
1000003



SELECT 2


-- query plan
EXPLAIN
SELECT *, nextval('seq_1') AS n2
FROM  (
   SELECT *, n1 - i AS d, sum(i) OVER (ORDER BY i) AS s
   FROM  (
      SELECT i, nextval('seq_1') AS n1
      FROM integers
   )
)
WHERE i = 0 OR i = 1000000;


QUERY PLAN


Subquery Scan on unnamed_subquery  (cost=153232.71..193245.20 rows=11395 width=36)

  Filter: ((unnamed_subquery.i = 0) OR (unnamed_subquery.i = 1000000))

  ->  WindowAgg  (cost=153232.71..176080.71 rows=1142400 width=28)

        ->  Sort  (cost=153232.71..156088.71 rows=1142400 width=12)

              Sort Key: integers.i

              ->  Seq Scan on integers  (cost=0.00..18760.00 rows=1142400 width=12)

JIT:

  Functions: 9

  Options: Inlining false, Optimization false, Expressions true, Deforming true



EXPLAIN

i	n1	d	s	n2
0	46137349	46137349	0	47137350
1000000	47137349	46137349	500000500000	47137351