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 IF NOT EXISTS "user" (
    id serial PRIMARY KEY,
    user_name VARCHAR (50) UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS project (
    id serial PRIMARY KEY,
    project_name VARCHAR (50) UNIQUE NOT NULL,
    owner BIGINT NOT NULL REFERENCES "user"(id),
    collaborators BIGINT[] DEFAULT array[]::bigint[]
);

INSERT INTO "user" VALUES
 (1 ,'Mike')
,(2 ,'Peter')
,(3 ,'Roger');

INSERT INTO project VALUES
 (1 ,'project1', 1, array[2, 3])
,(2 ,'project2', 1, array[]::integer[]);
3 rows affected

2 rows affected

SELECT
    s.id,
    s.project_name,
    COALESCE((
        SELECT to_json(array_agg(c.*))
        FROM "user" as c
        WHERE c.id = ANY(s.collaborators)
    ), to_json(array[]::json[])) as collaborators,
    json_build_object(
        'id', u.id,
        'user_name', u.user_name
    ) as user
FROM project s
INNER JOIN "user" u
    ON s.owner = u.id
WHERE s.id = 2


id
project_name
collaborators
user


2
project2
[]
{"id" : 1, "user_name" : "Mike"}


SELECT
    s.id,
    s.project_name,
    COALESCE((
        SELECT to_json(array_agg(c.*))
        FROM "user" as c
        WHERE c.id = ANY(s.collaborators)
    ), to_json(array[]::json[])) as collaborators,
    json_build_object(
        'id', u.id,
        'user_name', u.user_name
    ) as user
FROM project s
INNER JOIN "user" u
    ON s.owner = u.id
WHERE s.id = 1


id
project_name
collaborators
user


1
project1
[{"id":2,"user_name":"Peter"},{"id":3,"user_name":"Roger"}]
{"id" : 1, "user_name" : "Mike"}