Postgres 9.6

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 nodes (
    id bigint,
    nodes bigint[]
);

INSERT INTO nodes VALUES
(1, '{200,300,400}'),
(2, '{200,300,400}');

SELECT * FROM nodes;

id
nodes

1
{200,300,400}

2
{200,300,400}

SELECT 2

SELECT node, array_agg(id) as ids
FROM (
    SELECT id, unnest(nodes) as node
    FROM nodes
)s 
GROUP BY node
ORDER BY node

node
ids

200
{1,2}

300
{1,2}

400
{1,2}

SELECT 3

SELECT unnest(nodes) as node, array_agg(id) as ids 
FROM nodes
GROUP BY node
ORDER BY node

node
ids

200
{1,2}

300
{1,2}

400
{1,2}

SELECT 3