Postgres 12

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 mytable (created_at timestamp, user_id int);
insert into mytable values 
	('2020-01-01T22:22:22', 1), 
	('2020-01-01T22:22:44', 1), 
	('2020-01-01T22:22:22', 2), 
	('2020-01-01T22:24:44', 2);
4 rows affected

select * from mytable;


created_at
user_id


2020-01-01 22:22:22
1

2020-01-01 22:22:44
1

2020-01-01 22:22:22
2

2020-01-01 22:24:44
2


select distinct on (user_id) 
    user_id,
    created_at 
        <= lag(created_at) over(partition by user_id order by created_at) + interval '1' minute as flag
from mytable
order by user_id, created_at desc


user_id
flag


1
t

2
f

created_at	user_id
2020-01-01 22:22:22	1
2020-01-01 22:22:44	1
2020-01-01 22:22:22	2
2020-01-01 22:24:44	2