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 t(
  a integer NOT NULL,
  b integer NOT NULL,
  d integer NOT NULL
);
 
create unique index t_a_include_b on t(a) include (b);
-- I'd expecd index above to behave the same as index below for this query
--create unique index on t(a,b);

CREATE TABLE

CREATE INDEX

insert into t(
  SELECT random() * 100000000 as a, 
         random() * 3 as b,
         generate_series as d FROM generate_series(1,200000)
) ON CONFLICT DO NOTHING;

select count(*) from t;

SELECT i.relname "Table Name",indexrelname "Index Name",
 pg_size_pretty(pg_relation_size(indexrelid)) "Index Size"
 FROM pg_stat_all_indexes i JOIN pg_class c ON i.relid=c.oid 
 WHERE i.relname='t';

INSERT 0 199799

count

199799

SELECT 1

Table Name
Index Name
Index Size

t
t_a_include_b
5408 kB

SELECT 1

analyze t;

ANALYZE

-- index shows many "Buffers: shared hit", meanting it was fetching rows
-- from heap to filter them on `b` column, instead of using `b` available in the index

set enable_bitmapscan to off;
set enable_seqscan to off;
SET max_parallel_workers_per_gather = 0;

explain (analyze,verbose, buffers) select * from t where a > 1000000 and b = 4 order by a asc limit 10;

SET

SET

SET

QUERY PLAN

Limit  (cost=0.42..10955.01 rows=1 width=12) (actual time=84.283..84.284 rows=0 loops=1)

  Output: a, b, d

  Buffers: shared hit=198307

  ->  Index Scan using t_a_include_b on public.t  (cost=0.42..10955.01 rows=1 width=12) (actual time=84.280..84.281 rows=0 loops=1)

        Output: a, b, d

        Index Cond: (t.a > 1000000)

        Filter: (t.b = 4)

        Rows Removed by Filter: 197805

        Buffers: shared hit=198307

Planning:

  Buffers: shared hit=30

Planning Time: 0.201 ms

Execution Time: 84.303 ms

EXPLAIN