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 TYPE card AS (year int, cardname text);
CREATE TABLE my_friends_cards (id int, name text, rare_cards_composite card[]);
INSERT INTO my_friends_cards VALUES
(1, 'timmy', '{"(1923,baberuth)","(1999,jeter)"}')
, (2, 'jimmy', '{"(1955,Joey D)","(1995,juice_head)"}')
, (3, 'bob' , '{"(2001,mo_jeter)"}')
;
CREATE TYPE
CREATE TABLE
INSERT 0 3
SELECT t.name, c.cardname
FROM my_friends_cards t
, unnest(t.rare_cards_composite) c
WHERE t.name = ANY('{timmy,jimmy}')
AND c.year > 1990;
| name | cardname |
|---|---|
| timmy | jeter |
| jimmy | juice_head |
SELECT 2