Postgres 16

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?.
--https://stackoverflow.com/q/78140606/5298879
create schema activite;
create table activite.metadonnee(
  contributor_prescription text,
  date_publication_prescription date
);
insert into activite.metadonnee values
('john','22/10/2023'),
('john','21/10/2023'),
('john','20/10/2023'),
('john','19/10/2023'),
  
('paul','24/10/2023'),
('paul','23/10/2023'),
('paul','21/10/2023'),
('paul','18/10/2023')
returning *;

CREATE SCHEMA



CREATE TABLE




contributor_prescription
date_publication_prescription


john
2023-10-22

john
2023-10-21

john
2023-10-20

john
2023-10-19

paul
2023-10-24

paul
2023-10-23

paul
2023-10-21

paul
2023-10-18



INSERT 0 8


--Lateral Join returns too many rows

--I try to understand LATERAL JOIN with this query:

select m1.contributor_prescription, 
       derniere_publication.date_publication_prescription 
from activite.metadonnee m1
left join lateral 
(   select  date_publication_prescription, 
            m2.contributor_prescription 
    from activite.metadonnee m2 
    where m1.contributor_prescription = m2.contributor_prescription 
    order by date_publication_prescription desc
    limit 3) as derniere_publication
ON true
order by m1.contributor_prescription

--The goal is to have for each contributor_prescription the 3 latest date_publication_prescription.

--For now, the result is all contributor_prescription rows and the last date_publication_prescription for all.
/*
contributor_prescription	date_publication_prescription
john	22/10/2023
john	22/10/2023
(as many times as john exists in the table)	
paul	24/10/2023
paul	24/10/2023
(as many times as paul exists in the table)	
expected:

contributor_prescription	date_publication_prescription
john	22/10/2023
john	21/10/2023
john	20/10/2023
paul	24/10/2023
paul	23/10/2023
paul	21/10/2023
*/


contributor_prescription
date_publication_prescription


john
2023-10-22

john
2023-10-21

john
2023-10-20

john
2023-10-22

john
2023-10-21

john
2023-10-20

john
2023-10-22

john
2023-10-21

john
2023-10-20

john
2023-10-22

john
2023-10-21

john
2023-10-20

paul
2023-10-24

paul
2023-10-23

paul
2023-10-21

paul
2023-10-24

paul
2023-10-23

paul
2023-10-21

paul
2023-10-24

paul
2023-10-23

paul
2023-10-21

paul
2023-10-24

paul
2023-10-23

paul
2023-10-21



SELECT 24


with cte as (
    select contributor_prescription, 
           date_publication_prescription,
           row_number()over w1 as rn
    from activite.metadonnee m1
    window w1 as (partition by contributor_prescription
                      order by date_publication_prescription desc) )
select contributor_prescription,
       date_publication_prescription
from cte 
where rn<=3
order by 1,2 desc;


contributor_prescription
date_publication_prescription


john
2023-10-22

john
2023-10-21

john
2023-10-20

paul
2023-10-24

paul
2023-10-23

paul
2023-10-21



SELECT 6


select m1.contributor_prescription, 
       derniere_publication.date_publication_prescription 
from (select distinct contributor_prescription from activite.metadonnee) m1
left join lateral 
(   select  date_publication_prescription, 
            m2.contributor_prescription 
    from activite.metadonnee m2 
    where m1.contributor_prescription = m2.contributor_prescription 
    order by date_publication_prescription desc
    limit 3) as derniere_publication
ON true
order by m1.contributor_prescription


contributor_prescription
date_publication_prescription


john
2023-10-22

john
2023-10-21

john
2023-10-20

paul
2023-10-24

paul
2023-10-23

paul
2023-10-21



SELECT 6


select contributor_prescription,unnest(arr)
from (
select contributor_prescription, 
       (array_agg(date_publication_prescription 
                 order by date_publication_prescription desc))[:3] arr
from activite.metadonnee
group by 1) a;


contributor_prescription
unnest


john
2023-10-22

john
2023-10-21

john
2023-10-20

paul
2023-10-24

paul
2023-10-23

paul
2023-10-21



SELECT 6
contributor_prescription	date_publication_prescription
john	2023-10-22
john	2023-10-21
john	2023-10-20
john	2023-10-19
paul	2023-10-24
paul	2023-10-23
paul	2023-10-21
paul	2023-10-18