Postgres 15

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 test(a int) partition by list(a);
create table test_default partition of test default;
create table test1 partition of test for values in (1);
explain analyze verbose insert into test values (1);

CREATE TABLE

CREATE TABLE

CREATE TABLE

QUERY PLAN

Insert on public.test  (cost=0.00..0.01 rows=0 width=0) (actual time=0.345..0.346 rows=0 loops=1)

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

        Output: 1

Planning Time: 0.019 ms

Execution Time: 0.607 ms

EXPLAIN

do $f$
declare i int;
begin
for i in 3..2001 loop
  execute format('create table test%1$s partition of test for values in (%1$s);
                  insert into test%1$s values (%1$s);',i);
  end loop;
end $f$;

DO

explain analyze verbose insert into test values (555);

QUERY PLAN

Insert on public.test  (cost=0.00..0.01 rows=0 width=0) (actual time=0.096..0.096 rows=0 loops=1)

  ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)

        Output: 555

Planning Time: 0.030 ms

Execution Time: 12.318 ms

EXPLAIN

do $f$
declare i int;
begin
for i in 2002..4003 loop
  execute format('create table test%1$s partition of test for values in (%1$s);
                  insert into test%1$s values (%1$s);',i);
  end loop;
end $f$;

DO

explain analyze verbose insert into test values (555);

QUERY PLAN

Insert on public.test  (cost=0.00..0.01 rows=0 width=0) (actual time=0.128..0.129 rows=0 loops=1)

  ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1)

        Output: 555

Planning Time: 0.030 ms

Execution Time: 26.504 ms

EXPLAIN

explain analyze verbose insert into test values (555);

QUERY PLAN

Insert on public.test  (cost=0.00..0.01 rows=0 width=0) (actual time=0.016..0.016 rows=0 loops=1)

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

        Output: 555

Planning Time: 0.037 ms

Execution Time: 0.054 ms

EXPLAIN

explain analyze verbose insert into test values (777);

QUERY PLAN

Insert on public.test  (cost=0.00..0.01 rows=0 width=0) (actual time=0.079..0.080 rows=0 loops=1)

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

        Output: 777

Planning Time: 0.024 ms

Execution Time: 0.099 ms

EXPLAIN

analyze test;

ANALYZE

explain analyze verbose insert into test values (888);

QUERY PLAN

Insert on public.test  (cost=0.00..0.01 rows=0 width=0) (actual time=0.122..0.123 rows=0 loops=1)

  ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)

        Output: 888

Planning Time: 0.029 ms

Execution Time: 23.234 ms

EXPLAIN

explain analyze verbose insert into test values (999);

QUERY PLAN

Insert on public.test  (cost=0.00..0.01 rows=0 width=0) (actual time=0.064..0.064 rows=0 loops=1)

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

        Output: 999

Planning Time: 0.024 ms

Execution Time: 0.081 ms

EXPLAIN