Postgres 14

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 test (clientid int, subclientid varchar(100));
insert into test values
(44, '1'),
(44, '9'),
(44, 'BASE'),
(44, '2'),
(45, 'BASE'),
(45, 'BASE'),
(45, '2'),
(46, 'BASE'),
(46, 'BASE'),
(46, '1');
10 rows affected

select clientid
from (
  select clientid,
    case when subclientid in ('BASE', '1') then 1 else 2 end as score
  from (select distinct clientid, subclientid from test) t
) x
group by clientid
having sum(score) = 2;


clientid


46


  select clientid,
    case when subclientid in ('BASE', '1') then 1 else 2 end as score
  from (select distinct clientid, subclientid from test) t


clientid
score


45
2

44
1

46
1

45
1

44
2

46
1

44
2

44
1