Postgres 12

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 DEMO(n int) partition by list(n);
create table DEMO1 partition of DEMO for values in (1);
create table DEMO2 partition of DEMO for values in (2);
create table DEMO3 partition of DEMO for values in (3);
create table DEMO4 partition of DEMO for values in (4);
create table DEMO5 partition of DEMO for values in (5);
create table DEMO6 partition of DEMO for values in (6);
create table DEMO7 partition of DEMO for values in (7);
create table DEMO8 partition of DEMO for values in (8);
insert into DEMO     select 1 n from generate_series(1,11)
           union all select 2   from generate_series(1,22)
           union all select 3   from generate_series(1,33)
           union all select 4   from generate_series(1,44)
           union all select 5   from generate_series(1,55)
           union all select 6   from generate_series(1,66)
           union all select 7   from generate_series(1,77)
           union all select 8   from generate_series(1,88)
           ;
396 rows affected

analyze DEMO;
select count(*),count(distinct n) from DEMO;

count
count

396
8

prepare myselect (int) as select count(*) from DEMO where n=$1;
explain (analyze,verbose,costs,buffers) execute myselect(1);

QUERY PLAN

Aggregate  (cost=1.17..1.18 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=1

  ->  Seq Scan on public.demo1  (cost=0.00..1.14 rows=11 width=0) (actual time=0.023..0.024 rows=11 loops=1)

        Filter: (demo1.n = 1)

        Buffers: shared hit=1

Planning Time: 0.106 ms

Execution Time: 0.055 ms

explain (analyze,verbose,costs,buffers) execute myselect(2);

QUERY PLAN

Aggregate  (cost=1.33..1.34 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=1

  ->  Seq Scan on public.demo2  (cost=0.00..1.27 rows=22 width=0) (actual time=0.008..0.010 rows=22 loops=1)

        Filter: (demo2.n = 2)

        Buffers: shared hit=1

Planning Time: 0.063 ms

Execution Time: 0.026 ms

explain (analyze,verbose,costs,buffers) execute myselect(3);

QUERY PLAN

Aggregate  (cost=1.50..1.51 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=1

  ->  Seq Scan on public.demo3  (cost=0.00..1.41 rows=33 width=0) (actual time=0.008..0.011 rows=33 loops=1)

        Filter: (demo3.n = 3)

        Buffers: shared hit=1

Planning Time: 0.053 ms

Execution Time: 0.027 ms

explain (analyze,verbose,costs,buffers) execute myselect(4);

QUERY PLAN

Aggregate  (cost=1.66..1.67 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=1

  ->  Seq Scan on public.demo4  (cost=0.00..1.55 rows=44 width=0) (actual time=0.008..0.012 rows=44 loops=1)

        Filter: (demo4.n = 4)

        Buffers: shared hit=1

Planning Time: 0.051 ms

Execution Time: 0.028 ms

explain (analyze,verbose,costs,buffers) execute myselect(5);

QUERY PLAN

Aggregate  (cost=1.82..1.83 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=1

  ->  Seq Scan on public.demo5  (cost=0.00..1.69 rows=55 width=0) (actual time=0.008..0.013 rows=55 loops=1)

        Filter: (demo5.n = 5)

        Buffers: shared hit=1

Planning Time: 0.050 ms

Execution Time: 0.029 ms

explain (analyze,verbose,costs,buffers) execute myselect(6);

QUERY PLAN

Aggregate  (cost=1.99..2.00 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=1

  ->  Seq Scan on public.demo6  (cost=0.00..1.83 rows=66 width=0) (actual time=0.008..0.014 rows=66 loops=1)

        Filter: (demo6.n = 6)

        Buffers: shared hit=1

Planning Time: 0.175 ms

Execution Time: 0.032 ms

set plan_cache_mode=force_generic_plan
explain (analyze,verbose,costs,buffers) execute myselect(7);

QUERY PLAN

Aggregate  (cost=15.92..15.93 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=1

  ->  Append  (cost=0.00..14.93 rows=396 width=0) (actual time=0.008..0.023 rows=77 loops=1)

        Buffers: shared hit=1

        Subplans Removed: 7

        ->  Seq Scan on public.demo7  (cost=0.00..1.96 rows=77 width=0) (actual time=0.008..0.016 rows=77 loops=1)

              Filter: (demo7.n = $1)

              Buffers: shared hit=1

Planning Time: 0.006 ms

Execution Time: 0.054 ms

explain (analyze,verbose,costs,buffers) execute myselect(8);

QUERY PLAN

Aggregate  (cost=15.92..15.93 rows=1 width=8) (actual time=0.032..0.032 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=1

  ->  Append  (cost=0.00..14.93 rows=396 width=0) (actual time=0.009..0.025 rows=88 loops=1)

        Buffers: shared hit=1

        Subplans Removed: 7

        ->  Seq Scan on public.demo8  (cost=0.00..2.10 rows=88 width=0) (actual time=0.008..0.018 rows=88 loops=1)

              Filter: (demo8.n = $1)

              Buffers: shared hit=1

Planning Time: 0.006 ms

Execution Time: 0.048 ms