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 t (fruit TEXT, color TEXT);

INSERT INTO t (fruit, color) VALUES
('banana', 'green'),
('banana', 'yellow'),
('apple', 'green'),
('apple', 'red'),
('apple', 'yellow'),
('strawberry', 'green'),
('strawberry', 'red');

SELECT * FROM t
7 rows affected



fruit
color


banana
green

banana
yellow

apple
green

apple
red

apple
yellow

strawberry
green

strawberry
red


SELECT
    fruit
FROM t
GROUP BY fruit
HAVING ARRAY_AGG(color) @> '{yellow, green}'
   AND ARRAY_AGG(color) <@ '{yellow, green}'


fruit


banana


SELECT
    fruit
FROM t
GROUP BY fruit
HAVING ARRAY_AGG(color ORDER BY color) = 
   (SELECT ARRAY_AGG(unnest ORDER BY unnest) FROM unnest('{yellow, green}'::text[]))
  


fruit


banana

fruit	color
banana	green
banana	yellow
apple	green
apple	red
apple	yellow
strawberry	green
strawberry	red