Postgres 13

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 t as
    select 1 as id, 1000 as itemid, '2000-10-12'::date as date, 'a' as data union all
    select 2 as id, 1000 as itemid, '2000-10-12'::date as date, 'b' as data union all
    select 3 as id, 1002 as itemid, '2000-09-12'::date as date, 'c' as data union all
    select 4 as id, 1001 as itemid, '2000-07-12'::date as date, 'd' as data union all
    select 5 as id, 1000 as itemid, '2000-05-12'::date as date, 'e' as data union all
    select 6 as id, 1005 as itemid, '2000-03-12'::date as date, 'f' as data union all
    select 7 as id, 1003 as itemid, '2000-03-12'::date as date, 'g' as data 

SELECT 7


-- error as expected
select t.*
from (select t.*, 
             count(distinct itemid) over (order by date) as cnt_itemid
      from t
     ) t
where cnt_itemid <= 3;

ERROR:  DISTINCT is not implemented for window functions
LINE 4:              count(distinct itemid) over (order by date) as ...
                     ^


-- This gets all recent rows that have the most recent id
select t.*
from (select t.*, 
             count(*) filter (where id = min_id) over (order by date desc) as cnt_itemid
      from (select t.*,
                   min(id) over (partition by itemid order by date desc) as min_id
            from t
           ) t
     ) t
where cnt_itemid <= 3;


id
itemid
date
data
min_id
cnt_itemid


1
1000
2000-10-12
a
1
1

2
1000
2000-10-12
b
1
1

3
1002
2000-09-12
c
3
2

4
1001
2000-07-12
d
4
3

5
1000
2000-05-12
e
1
3



SELECT 5


select t.*
from (select t.*, min(id) filter (where cnt_itemid = 3) over () as min_cnt_itemid_3
      from (select t.*, 
                   count(*) filter (where id = min_id) over (order by date desc) as cnt_itemid
            from (select t.*,
                         min(id) over (partition by itemid order by date desc) as min_id
                  from t
                 ) t
           ) t
     ) t
where id <= min_cnt_itemid_3


id
itemid
date
data
min_id
cnt_itemid
min_cnt_itemid_3


1
1000
2000-10-12
a
1
1
4

2
1000
2000-10-12
b
1
1
4

3
1002
2000-09-12
c
3
2
4

4
1001
2000-07-12
d
4
3
4



SELECT 4


select t.*
from t join
     (select itemid, min(max_date) over () as min_max_date
      from (select t.itemid, max(date) as max_date
            from t
            group by t.itemid
            order by max(t.date) desc
            limit 3
           ) t
      ) tt
      on t.itemid = tt.itemid and t.date >= tt.min_max_date


id
itemid
date
data


1
1000
2000-10-12
a

2
1000
2000-10-12
b

3
1002
2000-09-12
c

4
1001
2000-07-12
d



SELECT 4


select t.itemid, max(t.date)
                              from t
                              group by t.itemid
                              order by max(t.date) desc
                              limit 3


itemid
max


1000
2000-10-12

1002
2000-09-12

1001
2000-07-12



SELECT 3

id	itemid	date	data	min_id	cnt_itemid
1	1000	2000-10-12	a	1	1
2	1000	2000-10-12	b	1	1
3	1002	2000-09-12	c	3	2
4	1001	2000-07-12	d	4	3
5	1000	2000-05-12	e	1	3