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 |