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 *;

select setseed(.42);
insert into activite.metadonnee 
select n%1e3, current_date-random()*'1 year'::interval
from generate_series(1,1e5)n;

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

setseed

SELECT 1

INSERT 0 100000

explain analyze verbose
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

QUERY PLAN

Sort  (cost=231168.32..231169.82 rows=600 width=36) (actual time=9522.341..9522.491 rows=3006 loops=1)

  Output: metadonnee.contributor_prescription, m2.date_publication_prescription

  Sort Key: metadonnee.contributor_prescription

  Sort Method: quicksort  Memory: 190kB

  ->  Nested Loop Left Join  (cost=2296.16..231140.64 rows=600 width=36) (actual time=70.457..9518.653 rows=3006 loops=1)

        Output: metadonnee.contributor_prescription, m2.date_publication_prescription

        ->  HashAggregate  (cost=1146.26..1148.26 rows=200 width=32) (actual time=61.306..64.422 rows=1002 loops=1)

              Output: metadonnee.contributor_prescription

              Group Key: metadonnee.contributor_prescription

              Batches: 1  Memory Usage: 145kB

              ->  Seq Scan on activite.metadonnee  (cost=0.00..1005.61 rows=56261 width=32) (actual time=0.019..10.419 rows=100008 loops=1)

                    Output: metadonnee.contributor_prescription, metadonnee.date_publication_prescription

        ->  Limit  (cost=1149.89..1149.90 rows=3 width=36) (actual time=9.430..9.431 rows=3 loops=1002)

              Output: m2.date_publication_prescription, NULL::text

              ->  Sort  (cost=1149.89..1150.60 rows=281 width=36) (actual time=9.426..9.426 rows=3 loops=1002)

                    Output: m2.date_publication_prescription, NULL::text

                    Sort Key: m2.date_publication_prescription DESC

                    Sort Method: top-N heapsort  Memory: 25kB

                    ->  Seq Scan on activite.metadonnee m2  (cost=0.00..1146.26 rows=281 width=36) (actual time=0.052..9.388 rows=100 loops=1002)

                          Output: m2.date_publication_prescription, NULL::text

                          Filter: (metadonnee.contributor_prescription = m2.contributor_prescription)

                          Rows Removed by Filter: 99908

Planning Time: 1.149 ms

JIT:

  Functions: 13

  Options: Inlining false, Optimization false, Expressions true, Deforming true

  Timing: Generation 1.180 ms, Inlining 0.000 ms, Optimization 1.723 ms, Emission 28.593 ms, Total 31.496 ms

Execution Time: 9710.880 ms

EXPLAIN

explain analyze verbose
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;

QUERY PLAN

Sort  (cost=16010.29..16150.94 rows=56261 width=36) (actual time=97.507..97.652 rows=3006 loops=1)

  Output: cte.contributor_prescription, cte.date_publication_prescription

  Sort Key: cte.contributor_prescription, cte.date_publication_prescription DESC

  Sort Method: quicksort  Memory: 190kB

  ->  Subquery Scan on cte  (cost=10868.08..11571.34 rows=56261 width=36) (actual time=94.680..95.275 rows=3006 loops=1)

        Output: cte.contributor_prescription, cte.date_publication_prescription

        ->  Sort  (cost=10868.08..11008.73 rows=56261 width=44) (actual time=94.677..94.908 rows=3006 loops=1)

              Output: m1.contributor_prescription, m1.date_publication_prescription, (row_number() OVER (?))

              Sort Key: m1.date_publication_prescription DESC

              Sort Method: quicksort  Memory: 214kB

              ->  WindowAgg  (cost=5444.56..6429.13 rows=56261 width=44) (actual time=72.083..93.883 rows=3006 loops=1)

                    Output: m1.contributor_prescription, m1.date_publication_prescription, row_number() OVER (?)

                    Run Condition: (row_number() OVER (?) <= 3)

                    ->  Sort  (cost=5444.56..5585.21 rows=56261 width=36) (actual time=72.064..84.027 rows=100008 loops=1)

                          Output: m1.contributor_prescription, m1.date_publication_prescription

                          Sort Key: m1.contributor_prescription

                          Sort Method: external merge  Disk: 1768kB

                          ->  Seq Scan on activite.metadonnee m1  (cost=0.00..1005.61 rows=56261 width=36) (actual time=0.009..7.590 rows=100008 loops=1)

                                Output: m1.contributor_prescription, m1.date_publication_prescription

Planning Time: 0.153 ms

Execution Time: 98.209 ms

EXPLAIN

explain analyze verbose
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;

QUERY PLAN

ProjectSet  (cost=5444.56..5880.52 rows=2000 width=36) (actual time=109.722..142.462 rows=3006 loops=1)

  Output: metadonnee.contributor_prescription, unnest(((array_agg(metadonnee.date_publication_prescription ORDER BY metadonnee.date_publication_prescription DESC))[:3]))

  ->  GroupAggregate  (cost=5444.56..5869.02 rows=200 width=64) (actual time=109.705..141.753 rows=1002 loops=1)

        Output: metadonnee.contributor_prescription, (array_agg(metadonnee.date_publication_prescription ORDER BY metadonnee.date_publication_prescription DESC))[:3]

        Group Key: metadonnee.contributor_prescription

        ->  Sort  (cost=5444.56..5585.21 rows=56261 width=36) (actual time=109.623..125.885 rows=100008 loops=1)

              Output: metadonnee.contributor_prescription, metadonnee.date_publication_prescription

              Sort Key: metadonnee.contributor_prescription, metadonnee.date_publication_prescription DESC

              Sort Method: external merge  Disk: 1768kB

              ->  Seq Scan on activite.metadonnee  (cost=0.00..1005.61 rows=56261 width=36) (actual time=0.009..7.538 rows=100008 loops=1)

                    Output: metadonnee.contributor_prescription, metadonnee.date_publication_prescription

Planning Time: 0.112 ms

Execution Time: 142.953 ms

EXPLAIN
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