Postgres 9.5

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 9.5.25 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit



SELECT 1


/*

create table devices (id INT, user_id INT, device varchar(255));
insert into devices values
(1, 12,'tablet'),
(2,12,'pc'),
(2,12,'mobile');

*/




create table devices (id INT, user_id INT, device varchar(255));
insert into devices values
(1, 12,'tablet'),
(2,12,'pc'),
(2,13,'pc'),
(2,15,'tablet'),
(2,14,'pc'),
(2,14,'tablet'),
(2,12,'mobile');

INSERT 0 7


select * from devices


id
user_id
device


1
12
tablet

2
12
pc

2
13
pc

2
15
tablet

2
14
pc

2
14
tablet

2
12
mobile



SELECT 7


SELECT DISTINCT user_id
FROM devices
WHERE user_id NOT IN 
      (
      SELECT DISTINCT user_id
      FROM devices
      WHERE device IN
          (
          SELECT DISTINCT device
          FROM devices
          WHERE device NOT IN ('tablet', 'pc')
          )
      )
AND user_id IN
      (
      SELECT t.user_id
      FROM devices AS t
      INNER JOIN (SELECT user_id FROM devices WHERE device='pc') AS p
      ON t.user_id=p.user_id
      WHERE device='tablet'
      )


user_id


14



SELECT 1