Postgres 12

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 tablename (
  sku INTEGER,
  properties VARCHAR(9),
  value INTEGER
);

INSERT INTO tablename
  (sku, properties, value)
VALUES
  ('1', 'Family_ID', '21'),
  ('1', 'Class_ID', '21'),
  ('2', 'Family_ID', '20'),
  ('2', 'Class_ID', '21'),
  ('3', 'Family_ID', '21'),
  ('3', 'Class_ID', '21');
6 rows affected

select * from tablename
where sku in (
  select sku
  from tablename
  group by sku
  having 
    max(case when properties = 'Family_ID' then value end) = 21
    and
    max(case when properties = 'Class_ID' then value end) = 21
)


sku
properties
value


1
Family_ID
21

1
Class_ID
21

3
Family_ID
21

3
Class_ID
21


select * from tablename
where sku in (
  select sku
  from tablename
  group by sku
  having 
    max(case when properties = 'Family_ID' then value end) = 20
    and
    max(case when properties = 'Class_ID' then value end) = 21
)


sku
properties
value


2
Family_ID
20

2
Class_ID
21


select t.sku, t.properties, t.value
from (
  select *,
    max(case when properties = 'Family_ID' then value end) over (partition by sku) family_id,
    max(case when properties = 'Class_ID' then value end) over (partition by sku) class_id
  from tablename  
) t  
where t.family_id = 21 and t.class_id = 21


sku
properties
value


1
Family_ID
21

1
Class_ID
21

3
Family_ID
21

3
Class_ID
21


select t.sku, t.properties, t.value
from (
  select *,
    max(case when properties = 'Family_ID' then value end) over (partition by sku) family_id,
    max(case when properties = 'Class_ID' then value end) over (partition by sku) class_id
  from tablename  
) t  
where t.family_id = 20 and t.class_id = 21


sku
properties
value


2
Family_ID
20

2
Class_ID
21