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