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 t (grp int, usr char);
insert into t (grp, usr) values
(1, 'X'),
(1, 'Y'),
(1, 'Z'),
(2, 'X'),
(2, 'Y'),
(2, 'Z'),
(3, 'X'),
(3, 'Z'),
(4, 'X');
9 rows affected
select count(*)
from t a
join t b on b.usr = a.usr
where a.grp = 1 and b.grp = 2
| count |
|---|
| 3 |
select count(*)
from t a
join t b on b.usr = a.usr
join t c on c.usr = a.usr
where a.grp = 1 and b.grp = 2 and c.grp = 3
| count |
|---|
| 2 |
select count(*)
from t a
join t b on b.usr = a.usr
join t c on c.usr = a.usr
join t d on d.usr = a.usr
where a.grp = 1 and b.grp = 2 and c.grp = 3 and d.grp = 4
| count |
|---|
| 1 |