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?.

select version();


version


PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1


CREATE TABLE test (
    date DATE NOT NULL,
    number INT NOT NULL
);

CREATE TABLE


INSERT INTO test (date, number) VALUES
('2024-11-03', 500),
('2024-11-02', 1000),
('2024-11-05', 1000),
('2024-11-06', 1000),
('2024-11-07', 1000),
('2024-11-08', 500),
('2024-11-14', 1000),
('2024-11-15', 1000);

INSERT 0 8


select * from test order by date ;


date
number


2024-11-02
1000

2024-11-03
500

2024-11-05
1000

2024-11-06
1000

2024-11-07
1000

2024-11-08
500

2024-11-14
1000

2024-11-15
1000



SELECT 8


WITH streaks AS (
    SELECT 
        t.date,
        t.number,
        ROW_NUMBER() OVER (ORDER BY t.date) AS rn
    FROM test t
    WHERE t.number >= 1000
)
  ,

  -- select * from streaks;
consecutive_dates AS (
    SELECT
        s1.date,
        COUNT(*) AS streak_length
    FROM streaks s1
    LEFT JOIN streaks s2 
        ON s2.rn <= s1.rn AND s2.date = s1.date - INTERVAL '1 day' * (s1.rn - s2.rn)
    GROUP BY s1.date
)
SELECT
    t.date,
    COALESCE(cd.streak_length, 0) AS streak_length
FROM test t
LEFT JOIN consecutive_dates cd ON t.date = cd.date
ORDER BY t.date;


date
streak_length


2024-11-02
1

2024-11-03
0

2024-11-05
1

2024-11-06
2

2024-11-07
3

2024-11-08
0

2024-11-14
1

2024-11-15
2



SELECT 8


WITH streaks AS (
    SELECT 
        t.date,
        t.number,
        ROW_NUMBER() OVER (ORDER BY t.date) AS rn
    FROM test t
    WHERE t.number >= 1000
),
consecutive_dates AS (
    SELECT
        s1.date,
        COUNT(*) AS streak_length
    FROM streaks s1
    LEFT JOIN streaks s2 
        ON s2.rn <= s1.rn AND s2.date = s1.date - INTERVAL '1 day' * (s1.rn - s2.rn)
    GROUP BY s1.date
)
SELECT
    i.date,
    COALESCE(cd.streak_length, 0) AS streak_length
FROM (SELECT '2024-11-13'::DATE AS date) i 
LEFT JOIN consecutive_dates cd ON i.date = cd.date;


date
streak_length


2024-11-13
0



SELECT 1

date	number
2024-11-02	1000
2024-11-03	500
2024-11-05	1000
2024-11-06	1000
2024-11-07	1000
2024-11-08	500
2024-11-14	1000
2024-11-15	1000