Postgres 15

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 items (item text, factid text, value text, arrayindex integer);
insert into items (item, factid, value, arrayindex) values ('urn:drug:3812', 'urn:drug:170', 'a', 1);
insert into items (item, factid, value, arrayindex) values ('urn:drug:3812', 'urn:drug:170', 'b', 9);
insert into items (item, factid, value, arrayindex) values ('urn:drug:3812', 'urn:drug:170', 'c', 9);
insert into items (item, factid, value, arrayindex) values ('urn:drug:3812', 'urn:drug:170', 'd', 11);
insert into items (item, factid, value, arrayindex) values ('urn:drug:3812', 'urn:drug:170', 'e', 11);
insert into items (item, factid, value, arrayindex) values ('urn:drug:3812', 'urn:drug:170', 'e', 11);
insert into items (item, factid, value, arrayindex) values ('urn:drug:3812', 'urn:drug:170', 'e', 7);
insert into items (item, factid, value, arrayindex) values ('urn:drug:3812', 'urn:drug:170', 'e', 8);
insert into items (item, factid, value, arrayindex) values ('urn:drug:3812', 'urn:drug:171', 'false', 9);
insert into items (item, factid, value, arrayindex) values ('urn:drug:3812', 'urn:drug:171', 'g', 10);
insert into items (item, factid, value, arrayindex) values ('urn:drug:3812', 'urn:drug:171', 'false', 11);
insert into items (item, factid, value, arrayindex) values ('urn:drug:3812', 'urn:drug:171', 'i', 12);

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select * from items;


item
factid
value
arrayindex


urn:drug:3812
urn:drug:170
a
1

urn:drug:3812
urn:drug:170
b
9

urn:drug:3812
urn:drug:170
c
9

urn:drug:3812
urn:drug:170
d
11

urn:drug:3812
urn:drug:170
e
11

urn:drug:3812
urn:drug:170
e
11

urn:drug:3812
urn:drug:170
e
7

urn:drug:3812
urn:drug:170
e
8

urn:drug:3812
urn:drug:171
false
9

urn:drug:3812
urn:drug:171
g
10

urn:drug:3812
urn:drug:171
false
11

urn:drug:3812
urn:drug:171
i
12



SELECT 12


-- find unique arrayindex values for (factid = 'urn:drug:171' and value = 'false')
select distinct arrayindex
from items
where factid = 'urn:drug:171' and value = 'false';


arrayindex


9

11



SELECT 2


-- combine 2 queries
with t as (
  select distinct arrayindex
  from items
  where factid = 'urn:drug:171' and value = 'false'
)
select * from items
where arrayindex in (select * from t)
  and (factid = 'urn:drug:170' or factid = 'urn:drug:171') -- optional condition;


item
factid
value
arrayindex


urn:drug:3812
urn:drug:170
b
9

urn:drug:3812
urn:drug:170
c
9

urn:drug:3812
urn:drug:170
d
11

urn:drug:3812
urn:drug:170
e
11

urn:drug:3812
urn:drug:170
e
11

urn:drug:3812
urn:drug:171
false
9

urn:drug:3812
urn:drug:171
false
11



SELECT 7

item	factid	value	arrayindex
urn:drug:3812	urn:drug:170	a	1
urn:drug:3812	urn:drug:170	b	9
urn:drug:3812	urn:drug:170	c	9
urn:drug:3812	urn:drug:170	d	11
urn:drug:3812	urn:drug:170	e	11
urn:drug:3812	urn:drug:170	e	11
urn:drug:3812	urn:drug:170	e	7
urn:drug:3812	urn:drug:170	e	8
urn:drug:3812	urn:drug:171	false	9
urn:drug:3812	urn:drug:171	g	10
urn:drug:3812	urn:drug:171	false	11
urn:drug:3812	urn:drug:171	i	12