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 mytable (
    name text,
    attributes text[9]
);

INSERT INTO mytable VALUES
('Park 1', '{covered, handicap_access, elevator}'),
('Park 2', '{uncovered, always_open, handicap_access}');

SELECT * FROM mytable
2 rows affected



name
attributes


Park 1
{covered,handicap_access,elevator}

Park 2
{uncovered,always_open,handicap_access}


CREATE TABLE codes (
    code int,
    name text
);

INSERT INTO codes VALUES
(1, 'covered'),
(2, 'uncovered'),
(3, 'handicap_access'),
(4, 'XYZ'),
(5, 'always_open'),
(6, 'ABC'),
(7, 'elevator');

SELECT * FROM codes
7 rows affected



code
name


1
covered

2
uncovered

3
handicap_access

4
XYZ

5
always_open

6
ABC

7
elevator


SELECT
    *
FROM mytable,
    unnest(attributes) WITH ORDINALITY as elems(value, index)


name
attributes
value
index


Park 1
{covered,handicap_access,elevator}
covered
1

Park 1
{covered,handicap_access,elevator}
handicap_access
2

Park 1
{covered,handicap_access,elevator}
elevator
3

Park 2
{uncovered,always_open,handicap_access}
uncovered
1

Park 2
{uncovered,always_open,handicap_access}
always_open
2

Park 2
{uncovered,always_open,handicap_access}
handicap_access
3


SELECT
    *
FROM mytable,
    unnest(attributes) WITH ORDINALITY as elems(value, index)
JOIN codes c ON c.name = elems.value


name
attributes
value
index
code
name


Park 2
{uncovered,always_open,handicap_access}
always_open
2
5
always_open

Park 1
{covered,handicap_access,elevator}
covered
1
1
covered

Park 1
{covered,handicap_access,elevator}
elevator
3
7
elevator

Park 1
{covered,handicap_access,elevator}
handicap_access
2
3
handicap_access

Park 2
{uncovered,always_open,handicap_access}
handicap_access
3
3
handicap_access

Park 2
{uncovered,always_open,handicap_access}
uncovered
1
2
uncovered


SELECT
    t.name,
    array_agg(c.code ORDER BY elems.index) as codes
FROM mytable t,
    unnest(attributes) WITH ORDINALITY as elems(value, index)
JOIN codes c ON c.name = elems.value
GROUP BY t.name


name
codes


Park 1
{1,3,7}

Park 2
{2,5,3}

name	attributes
Park 1	{covered,handicap_access,elevator}
Park 2	{uncovered,always_open,handicap_access}

code	name
1	covered
2	uncovered
3	handicap_access
4	XYZ
5	always_open
6	ABC
7	elevator