Postgres 13

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 items (
  id serial PRIMARY KEY
, name text
);

CREATE TABLE properties (
  id serial PRIMARY KEY
, name text
);

CREATE TABLE item_property (
  id serial PRIMARY KEY
, item_id integer REFERENCES items(id)
, property_id integer REFERENCES properties(id)
);

INSERT INTO items (name) VALUES
  ('item1')
, ('item2')
, ('item3')
, ('item4')
, ('item5')
;
INSERT INTO properties (name) VALUES
  ('property1')
, ('property2')
, ('property3')
, ('property4')
;

INSERT INTO item_property ( item_id, property_id) VALUES
  (1, 1)
, (1, 2)
, (1, 4)
, (2, 1)
, (3, 2)
, (3, 3)
, (4, 3)
, (4, 4)
, (5, 2)
;

CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 5



INSERT 0 4



INSERT 0 9


SELECT sub.properties, i.name AS item
FROM  (
   SELECT item_id, array_agg(p.name) AS properties
   FROM  (
      SELECT DISTINCT item_id
      FROM   item_property
      WHERE  property_id = ANY(SELECT id FROM properties WHERE name = ANY ('{"property1","property2"}'))
      ) ip0
   JOIN   item_property ip USING (item_id)
   JOIN   properties    p ON p.id = ip.property_id
   GROUP  BY item_id
   ) sub
JOIN   items i ON i.id = sub.item_id
ORDER  BY i.name;


properties
item


{property1,property2,property4}
item1

{property1}
item2

{property2,property3}
item3

{property2}
item5



SELECT 4


-- Alternative with IN
SELECT sub.properties, i.name AS item
FROM  (
   SELECT item_id, array_agg(p.name) AS properties
   FROM   item_property ip
   JOIN   properties p ON p.id = ip.property_id
   WHERE  item_id IN (
      SELECT item_id
      FROM   item_property
      WHERE  property_id = ANY(SELECT id FROM properties WHERE name = ANY ('{"property1","property2"}'))
      )
   GROUP  BY item_id
   ) sub
JOIN   items i ON i.id = sub.item_id
ORDER  BY i.name;


properties
item


{property1,property2,property4}
item1

{property1}
item2

{property2,property3}
item3

{property2}
item5



SELECT 4

properties	item
{property1,property2,property4}	item1
{property1}	item2
{property2,property3}	item3
{property2}	item5