Postgres 11

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 as select 1 A, 1 B, 1 C union all select 1 A, 1 B , 1 C;
2 rows affected

insert into DEMO select 1,1,1 from DEMO a, DEMO b, DEMO c, DEMO d, DEMO e, DEMO f, DEMO g, DEMO h;
256 rows affected

insert into DEMO values (2,0,0);
1 rows affected

insert into DEMO select a.A, a.B, a.C from DEMO a;
259 rows affected

create index DEMO_AB on DEMO(A,B);
select A,count(*) from DEMO group by A;

a
count

2
2

1
516

explain (analyze,verbose,costs,buffers) select sum(A) from DEMO where A=2;

QUERY PLAN

Aggregate  (cost=7.44..7.45 rows=1 width=8) (actual time=0.115..0.116 rows=1 loops=1)

  Output: sum(a)

  Buffers: shared hit=2 read=2

  ->  Bitmap Heap Scan on public.demo  (cost=4.30..7.44 rows=3 width=4) (actual time=0.102..0.105 rows=2 loops=1)

        Output: a, b, c

        Recheck Cond: (demo.a = 2)

        Heap Blocks: exact=2

        Buffers: shared hit=2 read=2

        ->  Bitmap Index Scan on demo_ab  (cost=0.00..4.30 rows=3 width=0) (actual time=0.089..0.089 rows=2 loops=1)

              Index Cond: (demo.a = 2)

              Buffers: shared read=2

Planning Time: 0.122 ms

Execution Time: 0.173 ms

explain (analyze,verbose,costs,buffers) select sum(B) from DEMO where A=2;

QUERY PLAN

Aggregate  (cost=7.44..7.45 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1)

  Output: sum(b)

  Buffers: shared hit=4

  ->  Bitmap Heap Scan on public.demo  (cost=4.30..7.44 rows=3 width=4) (actual time=0.010..0.012 rows=2 loops=1)

        Output: a, b, c

        Recheck Cond: (demo.a = 2)

        Heap Blocks: exact=2

        Buffers: shared hit=4

        ->  Bitmap Index Scan on demo_ab  (cost=0.00..4.30 rows=3 width=0) (actual time=0.007..0.007 rows=2 loops=1)

              Index Cond: (demo.a = 2)

              Buffers: shared hit=2

Planning Time: 0.053 ms

Execution Time: 0.056 ms

vacuum DEMO;
explain (analyze,verbose,costs,buffers) select sum(B) from DEMO where A=2;

QUERY PLAN

Aggregate  (cost=4.34..4.35 rows=1 width=8) (actual time=0.040..0.041 rows=1 loops=1)

  Output: sum(b)

  Buffers: shared hit=3

  ->  Index Only Scan using demo_ab on public.demo  (cost=0.28..4.33 rows=3 width=4) (actual time=0.034..0.035 rows=2 loops=1)

        Output: a, b

        Index Cond: (demo.a = 2)

        Heap Fetches: 0

        Buffers: shared hit=3

Planning Time: 0.110 ms

Execution Time: 0.080 ms

explain (analyze,verbose,costs,buffers) select sum(C) from DEMO where A=2;

QUERY PLAN

Aggregate  (cost=7.44..7.45 rows=1 width=8) (actual time=0.050..0.051 rows=1 loops=1)

  Output: sum(c)

  Buffers: shared hit=4

  ->  Bitmap Heap Scan on public.demo  (cost=4.30..7.44 rows=3 width=4) (actual time=0.044..0.046 rows=2 loops=1)

        Output: a, b, c

        Recheck Cond: (demo.a = 2)

        Heap Blocks: exact=2

        Buffers: shared hit=4

        ->  Bitmap Index Scan on demo_ab  (cost=0.00..4.30 rows=3 width=0) (actual time=0.037..0.037 rows=2 loops=1)

              Index Cond: (demo.a = 2)

              Buffers: shared hit=2

Planning Time: 0.064 ms

Execution Time: 0.089 ms