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.046..0.047 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.014..0.042 rows=11 loops=1)

        Output: n

        Filter: (demo.n = 1)

        Rows Removed by Filter: 385

        Buffers: shared hit=2

Planning Time: 0.128 ms

Execution Time: 3.848 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.069..0.070 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.035..0.064 rows=22 loops=1)

        Output: n

        Filter: (demo.n = 2)

        Rows Removed by Filter: 374

        Buffers: shared hit=2

Planning Time: 0.066 ms

Execution Time: 0.089 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.048..0.048 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.015..0.043 rows=33 loops=1)

        Output: n

        Filter: (demo.n = 3)

        Rows Removed by Filter: 363

        Buffers: shared hit=2

Planning Time: 0.047 ms

Execution Time: 0.064 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.049..0.049 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.017..0.043 rows=44 loops=1)

        Output: n

        Filter: (demo.n = 4)

        Rows Removed by Filter: 352

        Buffers: shared hit=2

Planning Time: 0.046 ms

Execution Time: 0.065 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.050..0.051 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.020..0.044 rows=55 loops=1)

        Output: n

        Filter: (demo.n = 5)

        Rows Removed by Filter: 341

        Buffers: shared hit=2

Planning Time: 0.067 ms

Execution Time: 0.066 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.054..0.054 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.024..0.047 rows=66 loops=1)

        Output: n

        Filter: (demo.n = $1)

        Rows Removed by Filter: 330

        Buffers: shared hit=2

Planning Time: 0.045 ms

Execution Time: 0.070 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.055..0.056 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.032..0.048 rows=77 loops=1)

        Output: n

        Filter: (demo.n = $1)

        Rows Removed by Filter: 319

        Buffers: shared hit=2

Planning Time: 0.007 ms

Execution Time: 0.071 ms

EXPLAIN

show plan_cache_mode

plan_cache_mode

auto

SHOW

set plan_cache_mode=force_custom_plan

SET

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

QUERY PLAN

Aggregate  (cost=7.17..7.18 rows=1 width=8) (actual time=0.054..0.055 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=2

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

        Output: n

        Filter: (demo.n = 8)

        Rows Removed by Filter: 308

        Buffers: shared hit=2

Planning Time: 0.047 ms

Execution Time: 0.070 ms

EXPLAIN

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

QUERY PLAN

Aggregate  (cost=6.95..6.96 rows=1 width=8) (actual time=0.042..0.042 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=2

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

        Output: n

        Filter: (demo.n = 9)

        Rows Removed by Filter: 396

        Buffers: shared hit=2

Planning Time: 0.046 ms

Execution Time: 0.057 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