Postgres 14

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 orders (id_user char(1), ordered_at timestamp);

CREATE TABLE


INSERT INTO orders(id_user, ordered_at) VALUES
('1',  '2021-09-20 02:00:00'::timestamp),
('1',  '2021-10-06 02:00:00'::timestamp),
('2',  '2021-10-01 02:00:00'::timestamp),
('3',  '2021-10-06 02:00:00'::timestamp),
('3',  '2021-08-24 03:00:00'::timestamp),
('4',  '2021-08-20 02:00:00'::timestamp),
('4',  '2021-09-17 02:00:00'::timestamp),
('4',  '2021-09-20 02:00:00'::timestamp),
('4',  '2021-10-04 02:00:00'::timestamp);

INSERT 0 9


with t as (
    select id_user, ordered_at
         , lead(ordered_at) over (partition by id_user order by ordered_at) as ld_ordered_at
    from orders
)
select id_user, avg(ld_ordered_at - ordered_at) 
from t
where ld_ordered_at is not null
group by id_user
;


id_user
avg


1
16 days

3
42 days 23:00:00

4
15 days



SELECT 3


select id_user, (max(ordered_at)-min(ordered_at)) / (count(1)-1)
from orders
group by id_user
having count(1) > 1
order by 1;


id_user
?column?


1
16 days

3
42 days 23:00:00

4
15 days



SELECT 3