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 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
begin;
create temp table testuser(name text, week_no int, year_no int);
insert into testuser values
('fb', 8, 2022),
('fb', 10, 2022);
insert into testuser values
('fb', 5, 2021),
('fb', 6, 2021),
('fb', 7, 2022),
('twitter', 1 , 2022),
('twitter', 2 , 2022),
('twitter', 3 , 2022),
('twitter' , 7 , 2022),
('misc1' , 7 , 2022),
('misc1' , 8 , 2022),
('misc1' , 9 , 2022),
('misc1' , 10 , 2022),
('youtube' , 21 , 2022);
end;
BEGIN
CREATE TABLE
INSERT 0 2
INSERT 0 12
COMMIT
table testuser;
| name | week_no | year_no |
|---|---|---|
| fb | 8 | 2022 |
| fb | 10 | 2022 |
| fb | 5 | 2021 |
| fb | 6 | 2021 |
| fb | 7 | 2022 |
| 1 | 2022 | |
| 2 | 2022 | |
| 3 | 2022 | |
| 7 | 2022 | |
| misc1 | 7 | 2022 |
| misc1 | 8 | 2022 |
| misc1 | 9 | 2022 |
| misc1 | 10 | 2022 |
| youtube | 21 | 2022 |
SELECT 14
with cte as (
select
name
,week_no
,year_no
,lag(week_no) over(partition by name, year_no order by year_no,week_no) as lag
,lead(week_no) over(partition by name, year_no order by year_no,week_no) as lead
from testuser)
select name, year_no from cte where lead + lag = 2 * week_no;
| name | year_no |
|---|---|
| misc1 | 2022 |
| misc1 | 2022 |
| 2022 |
SELECT 3
with cte as (
select
name
,week_no
,year_no
,lag(week_no) over(partition by name, year_no order by year_no,week_no) as lag
,lead(week_no) over(partition by name, year_no order by year_no,week_no) as lead
from testuser)
select distinct name, year_no from cte where lead + lag = 2 * week_no;
| name | year_no |
|---|---|
| misc1 | 2022 |
| 2022 |
SELECT 2