Postgres 15

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,4e5)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 400000

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;

QUERY PLAN

Subquery Scan on cte  (cost=60980.88..63790.76 rows=224790 width=36) (actual time=2290.796..2291.509 rows=3006 loops=1)

  Output: cte.contributor_prescription, cte.date_publication_prescription

  ->  Sort  (cost=60980.88..61542.86 rows=224790 width=44) (actual time=2290.794..2291.140 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: 261kB

        ->  WindowAgg  (cost=30149.23..34083.05 rows=224790 width=44) (actual time=1848.510..2290.007 rows=3006 loops=1)

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

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

              ->  Sort  (cost=30149.23..30711.20 rows=224790 width=36) (actual time=1848.499..2150.286 rows=400008 loops=1)

                    Output: m1.contributor_prescription, m1.date_publication_prescription

                    Sort Key: m1.contributor_prescription

                    Sort Method: external merge  Disk: 7072kB

                    ->  Seq Scan on activite.metadonnee m1  (cost=0.00..4017.90 rows=224790 width=36) (actual time=0.020..179.238 rows=400008 loops=1)

                          Output: m1.contributor_prescription, m1.date_publication_prescription

Planning Time: 0.256 ms

Execution Time: 2292.719 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=30149.23..31851.15 rows=2000 width=36) (actual time=1775.053..2517.814 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=30149.23..31837.65 rows=200 width=64) (actual time=1775.049..2516.847 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=30149.23..30711.20 rows=224790 width=36) (actual time=1774.826..2188.664 rows=400008 loops=1)

              Output: metadonnee.contributor_prescription, metadonnee.date_publication_prescription

              Sort Key: metadonnee.contributor_prescription

              Sort Method: external merge  Disk: 7072kB

              ->  Seq Scan on activite.metadonnee  (cost=0.00..4017.90 rows=224790 width=36) (actual time=0.013..205.876 rows=400008 loops=1)

                    Output: metadonnee.contributor_prescription, metadonnee.date_publication_prescription

Planning Time: 0.111 ms

Execution Time: 2519.105 ms

EXPLAIN

--create index on activite.metadonnee (date_publication_prescription)
--  include(contributor_prescription);

create index on activite.metadonnee (contributor_prescription,
                                     date_publication_prescription);

CREATE INDEX

vacuum analyze activite.metadonnee;

VACUUM

explain analyze verbose
WITH RECURSIVE t AS (
   SELECT contributor_prescription,
          1 as rank_pos,
          max(date_publication_prescription) AS date_publication_prescription
   FROM activite.metadonnee 
   GROUP BY contributor_prescription
   UNION ALL
   SELECT contributor_prescription,
          rank_pos+1,
          (SELECT max(date_publication_prescription) 
           FROM activite.metadonnee 
           WHERE date_publication_prescription < t.date_publication_prescription
           AND contributor_prescription=t.contributor_prescription)
   FROM t 
   WHERE rank_pos<3
   )
SELECT contributor_prescription, date_publication_prescription
FROM t;

QUERY PLAN

CTE Scan on t  (cost=25611.96..26298.56 rows=34330 width=36) (actual time=335.519..539.360 rows=3006 loops=1)

  Output: t.contributor_prescription, t.date_publication_prescription

  CTE t

    ->  Recursive Union  (cost=6414.48..25611.96 rows=34330 width=40) (actual time=335.517..537.996 rows=3006 loops=1)

          ->  Finalize HashAggregate  (cost=6414.48..6424.48 rows=1000 width=11) (actual time=335.515..335.778 rows=1002 loops=1)

                Output: metadonnee_1.contributor_prescription, 1, max(metadonnee_1.date_publication_prescription)

                Group Key: metadonnee_1.contributor_prescription

                Batches: 1  Memory Usage: 193kB

                ->  Gather  (cost=6299.48..6409.48 rows=1000 width=7) (actual time=334.687..335.143 rows=2002 loops=1)

                      Output: metadonnee_1.contributor_prescription, (PARTIAL max(metadonnee_1.date_publication_prescription))

                      Workers Planned: 1

                      Workers Launched: 1

                      ->  Partial HashAggregate  (cost=5299.48..5309.48 rows=1000 width=7) (actual time=297.885..298.049 rows=1001 loops=2)

                            Output: metadonnee_1.contributor_prescription, PARTIAL max(metadonnee_1.date_publication_prescription)

                            Group Key: metadonnee_1.contributor_prescription

                            Batches: 1  Memory Usage: 193kB

                            Worker 0:  actual time=261.401..261.543 rows=1000 loops=1

                              Batches: 1  Memory Usage: 193kB

                            ->  Parallel Seq Scan on activite.metadonnee metadonnee_1  (cost=0.00..4122.99 rows=235299 width=7) (actual time=0.019..96.697 rows=200004 loops=2)

                                  Output: metadonnee_1.contributor_prescription, metadonnee_1.date_publication_prescription

                                  Worker 0:  actual time=0.033..13.562 rows=173557 loops=1

          ->  WorkTable Scan on t t_1  (cost=0.00..1850.09 rows=3333 width=40) (actual time=0.070..67.107 rows=668 loops=3)

                Output: t_1.contributor_prescription, (t_1.rank_pos + 1), (SubPlan 2)

                Filter: (t_1.rank_pos < 3)

                Rows Removed by Filter: 334

                SubPlan 2

                  ->  Result  (cost=0.48..0.49 rows=1 width=4) (actual time=0.099..0.099 rows=1 loops=2004)

                        Output: $3

                        InitPlan 1 (returns $3)

                          ->  Limit  (cost=0.42..0.48 rows=1 width=4) (actual time=0.098..0.099 rows=1 loops=2004)

                                Output: metadonnee.date_publication_prescription

                                ->  Index Only Scan Backward using metadonnee_contributor_prescription_date_publication_prescr_idx on activite.metadonnee  (cost=0.42..7.42 rows=133 width=4) (actual time=0.098..0.098 rows=1 loops=2004)

                                      Output: metadonnee.date_publication_prescription

                                      Index Cond: ((metadonnee.contributor_prescription = t_1.contributor_prescription) AND (metadonnee.date_publication_prescription IS NOT NULL) AND (metadonnee.date_publication_prescription < t_1.date_publication_prescription))

                                      Heap Fetches: 0

Planning Time: 0.281 ms

Execution Time: 539.826 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