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.014..0.014 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.008..0.010 rows=11 loops=1)

        Filter: (demo1.n = 1)

        Buffers: shared hit=1

Planning Time: 0.126 ms

Execution Time: 0.038 ms

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

QUERY PLAN

Aggregate  (cost=1.33..1.34 rows=1 width=8) (actual time=0.016..0.016 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.009..0.012 rows=22 loops=1)

        Filter: (demo2.n = 2)

        Buffers: shared hit=1

Planning Time: 0.082 ms

Execution Time: 0.031 ms

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

QUERY PLAN

Aggregate  (cost=1.50..1.51 rows=1 width=8) (actual time=0.018..0.018 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.009..0.013 rows=33 loops=1)

        Filter: (demo3.n = 3)

        Buffers: shared hit=1

Planning Time: 0.073 ms

Execution Time: 0.033 ms

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

QUERY PLAN

Aggregate  (cost=1.66..1.67 rows=1 width=8) (actual time=0.020..0.020 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.009..0.015 rows=44 loops=1)

        Filter: (demo4.n = 4)

        Buffers: shared hit=1

Planning Time: 0.064 ms

Execution Time: 0.034 ms

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

QUERY PLAN

Aggregate  (cost=1.82..1.83 rows=1 width=8) (actual time=0.022..0.023 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.009..0.016 rows=55 loops=1)

        Filter: (demo5.n = 5)

        Buffers: shared hit=1

Planning Time: 0.062 ms

Execution Time: 0.037 ms

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

QUERY PLAN

Aggregate  (cost=1.99..2.00 rows=1 width=8) (actual time=0.025..0.025 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.010..0.018 rows=66 loops=1)

        Filter: (demo6.n = 6)

        Buffers: shared hit=1

Planning Time: 0.188 ms

Execution Time: 0.039 ms

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

QUERY PLAN

Aggregate  (cost=2.15..2.16 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=1

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

        Filter: (demo7.n = 7)

        Buffers: shared hit=1

Planning Time: 0.060 ms

Execution Time: 0.041 ms

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

QUERY PLAN

Aggregate  (cost=2.32..2.33 rows=1 width=8) (actual time=0.029..0.029 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=1

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

        Filter: (demo8.n = 8)

        Buffers: shared hit=1

Planning Time: 0.060 ms

Execution Time: 0.043 ms

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

QUERY PLAN

Aggregate  (cost=0.00..0.01 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

  Output: count(*)

  ->  Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.001..0.001 rows=0 loops=1)

        One-Time Filter: false

Planning Time: 0.033 ms

Execution Time: 0.014 ms