Postgres 13

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 s (sid int primary key, bezeichnung text);
create table p (sid int not null references s, jahr1 int, jahr2 int, a numeric, b numeric);
create index on p (sid, jahr1, jahr2);
create index on p (jahr1, jahr2, sid);

insert into s (sid, bezeichnung) 
select g.id, concat('Bezeichnung ', g.id)
from generate_series(1,1000) as g(id);

insert into p(sid, jahr1, jahr2, a, b)
select sid, 
       jahr1, 
       jahr1 + (random() * 15)::int as jahr2, 
       random() * 100 + 1 as a,
       random() * 100 + 1 as b
from (
  select (random() * 999 + 1)::int as sid, 
         (random() + 1990)::int as jahr1
  from generate_series(1,5000)
) t;
1000 rows affected

5000 rows affected

create function get_data(p_jahr int)
  returns table (sid int, bezeichnung text, flaeche numeric)
as
$$
  SELECT s.sid,
         s.bezeichnung,
         sum(p.a * p.b) AS flaeche
  FROM s
    JOIN p ON s.sid = p.sid
  WHERE p.jahr1 <= p_jahr 
    and p.jahr2 >= p_jahr
  GROUP BY s.sid,s.bezeichnung;
$$
language sql
stable;
explain (analyze)
  SELECT s.sid,
         s.bezeichnung,
         sum(p.a * p.b) AS flaeche
  FROM s
    JOIN p ON s.sid = p.sid
  WHERE p.jahr1 <= 2004
    and p.jahr2 >= 2004
  GROUP BY s.sid,s.bezeichnung;


QUERY PLAN


HashAggregate  (cost=133.40..137.95 rows=364 width=68) (actual time=1.721..1.900 rows=479 loops=1)

  Group Key: s.sid

  Batches: 1  Memory Usage: 297kB

  ->  Hash Join  (cost=38.58..130.67 rows=364 width=100) (actual time=0.309..1.226 rows=647 loops=1)

        Hash Cond: (p.sid = s.sid)

        ->  Seq Scan on p  (cost=0.00..91.14 rows=364 width=68) (actual time=0.018..0.735 rows=647 loops=1)

              Filter: ((jahr1 <= 2004) AND (jahr2 >= 2004))

              Rows Removed by Filter: 4353

        ->  Hash  (cost=22.70..22.70 rows=1270 width=36) (actual time=0.282..0.282 rows=1000 loops=1)

              Buckets: 2048  Batches: 1  Memory Usage: 67kB

              ->  Seq Scan on s  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.012..0.154 rows=1000 loops=1)

Planning Time: 0.551 ms

Execution Time: 2.018 ms


explain (analyze)
select *
from get_data(2004);


QUERY PLAN


HashAggregate  (cost=133.40..137.95 rows=364 width=68) (actual time=1.413..1.590 rows=479 loops=1)

  Group Key: s.sid

  Batches: 1  Memory Usage: 297kB

  ->  Hash Join  (cost=38.58..130.67 rows=364 width=100) (actual time=0.233..0.964 rows=647 loops=1)

        Hash Cond: (p.sid = s.sid)

        ->  Seq Scan on p  (cost=0.00..91.14 rows=364 width=68) (actual time=0.013..0.540 rows=647 loops=1)

              Filter: ((jahr1 <= 2004) AND (jahr2 >= 2004))

              Rows Removed by Filter: 4353

        ->  Hash  (cost=22.70..22.70 rows=1270 width=36) (actual time=0.213..0.213 rows=1000 loops=1)

              Buckets: 2048  Batches: 1  Memory Usage: 67kB

              ->  Seq Scan on s  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.008..0.090 rows=1000 loops=1)

Planning Time: 0.218 ms

Execution Time: 1.641 ms