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 t1(i int, j int, k int);

insert into t1 select i, j, k
from   generate_series(1, 100) as i
     , generate_series(1, 100) as j
     , generate_series(1, 100) as k;

create index on t1 using gist(i, j, k);

CREATE TABLE

INSERT 0 1000000

CREATE INDEX

explain select * from t1 where k = 54;

QUERY PLAN

Bitmap Heap Scan on t1  (cost=199.03..5780.51 rows=5000 width=12)

  Recheck Cond: (k = 54)

  ->  Bitmap Index Scan on t1_i_j_k_idx  (cost=0.00..197.78 rows=5000 width=0)

        Index Cond: (k = 54)

EXPLAIN

explain select * from t1 where j = 54;

QUERY PLAN

Bitmap Heap Scan on t1  (cost=199.03..5780.51 rows=5000 width=12)

  Recheck Cond: (j = 54)

  ->  Bitmap Index Scan on t1_i_j_k_idx  (cost=0.00..197.78 rows=5000 width=0)

        Index Cond: (j = 54)

EXPLAIN

explain select * from t1 where i = 54;

QUERY PLAN

Bitmap Heap Scan on t1  (cost=199.03..5780.51 rows=5000 width=12)

  Recheck Cond: (i = 54)

  ->  Bitmap Index Scan on t1_i_j_k_idx  (cost=0.00..197.78 rows=5000 width=0)

        Index Cond: (i = 54)

EXPLAIN