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

create table public.test(
	id integer generated always as identity primary key,
	str text unique,
	amount numeric);
	
create index on public.test(str, amount);

CREATE TABLE



CREATE INDEX


select *
from pg_indexes
where tablename = 'test'


schemaname
tablename
indexname
tablespace
indexdef


public
test
test_pkey
null
CREATE UNIQUE INDEX test_pkey ON public.test USING btree (id)

public
test
test_str_key
null
CREATE UNIQUE INDEX test_str_key ON public.test USING btree (str)

public
test
test_str_amount_idx
null
CREATE INDEX test_str_amount_idx ON public.test USING btree (str, amount)



SELECT 3


select 
	c.relnamespace::regnamespace as schema_name,
	c.relname as table_name,
	i.indexrelid::regclass as index_name,
	i.indisprimary as is_pk,
	i.indisunique as is_unique
from pg_index i
join pg_class c on c.oid = i.indrelid
where c.relname = 'test'


schema_name
table_name
index_name
is_pk
is_unique


public
test
test_pkey
t
t

public
test
test_str_key
f
t

public
test
test_str_amount_idx
f
f



SELECT 3

schemaname	tablename	indexname	tablespace	indexdef
public	test	test_pkey	null	CREATE UNIQUE INDEX test_pkey ON public.test USING btree (id)
public	test	test_str_key	null	CREATE UNIQUE INDEX test_str_key ON public.test USING btree (str)
public	test	test_str_amount_idx	null	CREATE INDEX test_str_amount_idx ON public.test USING btree (str, amount)