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 as 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)
           ;

SELECT 396

analyze DEMO;

ANALYZE

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

count
count

396
8

SELECT 1

prepare myselect (int) as select count(*) from DEMO where n=$1;

PREPARE

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

QUERY PLAN

Aggregate  (cost=6.98..6.99 rows=1 width=8) (actual time=0.041..0.042 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=2

  ->  Seq Scan on public.demo  (cost=0.00..6.95 rows=11 width=0) (actual time=0.011..0.038 rows=11 loops=1)

        Output: n

        Filter: (demo.n = 1)

        Rows Removed by Filter: 385

        Buffers: shared hit=2

Planning Time: 0.087 ms

Execution Time: 0.066 ms

EXPLAIN

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

QUERY PLAN

Aggregate  (cost=7.00..7.01 rows=1 width=8) (actual time=0.042..0.043 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=2

  ->  Seq Scan on public.demo  (cost=0.00..6.95 rows=22 width=0) (actual time=0.013..0.039 rows=22 loops=1)

        Output: n

        Filter: (demo.n = 2)

        Rows Removed by Filter: 374

        Buffers: shared hit=2

Planning Time: 0.045 ms

Execution Time: 0.057 ms

EXPLAIN

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

QUERY PLAN

Aggregate  (cost=7.03..7.04 rows=1 width=8) (actual time=0.043..0.044 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=2

  ->  Seq Scan on public.demo  (cost=0.00..6.95 rows=33 width=0) (actual time=0.014..0.039 rows=33 loops=1)

        Output: n

        Filter: (demo.n = 3)

        Rows Removed by Filter: 363

        Buffers: shared hit=2

Planning Time: 0.043 ms

Execution Time: 0.058 ms

EXPLAIN

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

QUERY PLAN

Aggregate  (cost=7.06..7.07 rows=1 width=8) (actual time=0.044..0.045 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=2

  ->  Seq Scan on public.demo  (cost=0.00..6.95 rows=44 width=0) (actual time=0.015..0.039 rows=44 loops=1)

        Output: n

        Filter: (demo.n = 4)

        Rows Removed by Filter: 352

        Buffers: shared hit=2

Planning Time: 0.042 ms

Execution Time: 0.059 ms

EXPLAIN

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

QUERY PLAN

Aggregate  (cost=7.09..7.10 rows=1 width=8) (actual time=0.045..0.046 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=2

  ->  Seq Scan on public.demo  (cost=0.00..6.95 rows=55 width=0) (actual time=0.018..0.040 rows=55 loops=1)

        Output: n

        Filter: (demo.n = 5)

        Rows Removed by Filter: 341

        Buffers: shared hit=2

Planning Time: 0.042 ms

Execution Time: 0.060 ms

EXPLAIN

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

QUERY PLAN

Aggregate  (cost=7.08..7.08 rows=1 width=8) (actual time=0.049..0.050 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=2

  ->  Seq Scan on public.demo  (cost=0.00..6.95 rows=50 width=0) (actual time=0.022..0.043 rows=66 loops=1)

        Output: n

        Filter: (demo.n = $1)

        Rows Removed by Filter: 330

        Buffers: shared hit=2

Planning Time: 0.041 ms

Execution Time: 0.064 ms

EXPLAIN

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

QUERY PLAN

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

  Output: count(*)

  Buffers: shared hit=2

  ->  Seq Scan on public.demo  (cost=0.00..6.95 rows=50 width=0) (actual time=0.030..0.043 rows=77 loops=1)

        Output: n

        Filter: (demo.n = $1)

        Rows Removed by Filter: 319

        Buffers: shared hit=2

Planning Time: 0.006 ms

Execution Time: 0.065 ms

EXPLAIN

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

QUERY PLAN

Aggregate  (cost=7.08..7.08 rows=1 width=8) (actual time=0.075..0.075 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=2

  ->  Seq Scan on public.demo  (cost=0.00..6.95 rows=50 width=0) (actual time=0.057..0.067 rows=88 loops=1)

        Output: n

        Filter: (demo.n = $1)

        Rows Removed by Filter: 308

        Buffers: shared hit=2

Planning Time: 0.006 ms

Execution Time: 0.089 ms

EXPLAIN

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

QUERY PLAN

Aggregate  (cost=7.08..7.08 rows=1 width=8) (actual time=0.041..0.041 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=2

  ->  Seq Scan on public.demo  (cost=0.00..6.95 rows=50 width=0) (actual time=0.039..0.039 rows=0 loops=1)

        Output: n

        Filter: (demo.n = $1)

        Rows Removed by Filter: 396

        Buffers: shared hit=2

Planning Time: 0.006 ms

Execution Time: 0.055 ms

EXPLAIN

select count(n),count(distinct n),count(n)::numeric/count(distinct n) "avg rows per value" from DEMO;

count
count
avg rows per value

396
8
49.5000000000000000

SELECT 1