Postgres 13

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 attributes(id serial primary key,
object_id int, 
attribute_id text,
text_data text,
int_data int,
timestamp_data timestamp,
state text default 'active'
);

create index type_id_text_data on attributes(attribute_id,state)
/*
attribute_id and name 
1 = Name
2 = Sun Needs
3 = Soil Needs
4 = Zone
*/
insert into attributes(object_id,attribute_id,text_data)
select 1,1,'Aconite'
union 
select 1,2,'Partial Shade'
union
select 1,3,'Well-drained'
union
select 2,1,'Ageratum';

insert into attributes(object_id,attribute_id,int_data)
select 1,4,9
4 rows affected

1 rows affected

select * from attributes


id
object_id
attribute_id
text_data
int_data
timestamp_data
state


1
1
2
Partial Shade
null
null
active

2
1
1
Aconite
null
null
active

3
2
1
Ageratum
null
null
active

4
1
3
Well-drained
null
null
active

5
1
4
null
9
null
active

id	object_id	attribute_id	text_data	int_data	timestamp_data	state
1	1	2	Partial Shade	null	null	active
2	1	1	Aconite	null	null	active
3	2	1	Ageratum	null	null	active
4	1	3	Well-drained	null	null	active
5	1	4	null	9	null	active