Postgres 14

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 myth_table (
  id SERIAL UNIQUE NOT NULL,
  code_2 int NOT NULL,
  code_100000 int NOT NULL
);
insert into myth_table (
    code_2,code_100000
)
select
    mod(i,2),
    mod(i,100000)
from generate_series(1, 1000000) s(i);
1000000 rows affected

select count(*), count(distinct code_2), count(distinct code_100000)
from myth_table;

count
count
count

1000000
2
100000

create index myth_table_ix01 on myth_table (code_2, code_100000);
EXPLAIN (ANALYZE, BUFFERS)
select * from myth_table where code_100000 = 10 and code_2 = 0;

QUERY PLAN

Bitmap Heap Scan on myth_table  (cost=4.68..99.96 rows=25 width=12) (actual time=0.057..0.174 rows=10 loops=1)

  Recheck Cond: ((code_2 = 0) AND (code_100000 = 10))

  Heap Blocks: exact=10

  Buffers: shared read=13

  ->  Bitmap Index Scan on myth_table_ix01  (cost=0.00..4.67 rows=25 width=0) (actual time=0.040..0.040 rows=10 loops=1)

        Index Cond: ((code_2 = 0) AND (code_100000 = 10))

        Buffers: shared read=3

Planning:

  Buffers: shared hit=19 read=7

Planning Time: 0.468 ms

Execution Time: 0.220 ms

drop index myth_table_ix01;
create index myth_table_ix02 on myth_table (code_100000, code_2);
EXPLAIN (ANALYZE, BUFFERS)
select * from myth_table where code_100000 = 10 and code_2 = 0;

QUERY PLAN

Bitmap Heap Scan on myth_table  (cost=4.68..99.96 rows=25 width=12) (actual time=0.052..0.069 rows=10 loops=1)

  Recheck Cond: ((code_100000 = 10) AND (code_2 = 0))

  Heap Blocks: exact=10

  Buffers: shared hit=10 read=3

  ->  Bitmap Index Scan on myth_table_ix02  (cost=0.00..4.67 rows=25 width=0) (actual time=0.043..0.043 rows=10 loops=1)

        Index Cond: ((code_100000 = 10) AND (code_2 = 0))

        Buffers: shared read=3

Planning:

  Buffers: shared hit=20 read=1 dirtied=1

Planning Time: 0.330 ms

Execution Time: 0.108 ms

drop index myth_table_ix02;