Postgres 16

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?.

select version();

version

PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

SELECT c1.oid, c1.relname, relkind, relpersistence, relreplident
FROM pg_class as c1
WHERE relkind NOT IN ('r', 'i', 'S', 't', 'v', 'm', 'c', 'f', 'p') OR
    relpersistence NOT IN ('p', 'u', 't') OR
    relreplident NOT IN ('d', 'n', 'f', 'i');

oid
relname
relkind
relpersistence
relreplident

SELECT 0

CREATE TABLE test_partition (
	id int4range,
	valid_at daterange,
  	name text,
	CONSTRAINT test_partition_uq1 UNIQUE (id, valid_at )
) PARTITION BY LIST (id);

CREATE TABLE

SELECT c1.oid, c1.relname, relkind, relpersistence, relreplident
FROM pg_class as c1
WHERE relkind NOT IN ('r', 'i', 'S', 't', 'v', 'm', 'c', 'f', 'p') OR
    relpersistence NOT IN ('p', 'u', 't') OR
    relreplident NOT IN ('d', 'n', 'f', 'i');

oid
relname
relkind
relpersistence
relreplident

16387
test_partition_uq1
I
p
n

SELECT 1