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 TYPE complex AS (
  r double precision
, i double precision
);

CREATE TABLE tbl (tbl_id serial, co complex);

INSERT INTO tbl(co)
SELECT (random()*100, random()*100)::complex FROM generate_series(1,10000);

CREATE INDEX tbl_co_r_idx ON tbl (((co).r));  -- note the parenthesis!

CREATE TYPE

CREATE TABLE

INSERT 0 10000

CREATE INDEX

VACUUM ANALYZE tbl;

VACUUM

EXPLAIN ANALYZE
SELECT * FROM tbl
WHERE (co).r BETWEEN 9.65 and 9.67;

QUERY PLAN

Bitmap Heap Scan on tbl  (cost=4.32..14.75 rows=3 width=41) (actual time=0.390..0.391 rows=0 loops=1)

  Recheck Cond: (((co).r >= '9.65'::double precision) AND ((co).r <= '9.67'::double precision))

  ->  Bitmap Index Scan on tbl_co_r_idx  (cost=0.00..4.32 rows=3 width=0) (actual time=0.224..0.225 rows=0 loops=1)

        Index Cond: (((co).r >= '9.65'::double precision) AND ((co).r <= '9.67'::double precision))

Planning Time: 2.948 ms

Execution Time: 1.410 ms

EXPLAIN