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?.

select version();

version

PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit

create table devices (
id int primary key, user_id int not null, device varchar(10));

insert into devices values 
(1 , 12 , 'tablet' ),
(2 , 12 , 'pc' ),
(3 , 12 , 'mobile' );

insert into devices values 
(4 , 16 , 'tablet' ),
(5 , 16 , 'pc' );

3 rows affected

2 rows affected

select user_id
from devices
group by user_id 
having count(*) = 2 and max(device) = 'tablet' and min(device) = 'pc'

user_id

16