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 users (id bigint PRIMARY KEY);
CREATE TABLE user_photo (
id bigint PRIMARY KEY
, url text NOT NULL
, user_id bigint REFERENCES users(id)
);
INSERT INTO users VALUES
(100)
, (101)
, (102) -- no photo!
;
INSERT INTO user_photo VALUES
(1, 'https://1.com', 100)
, (3, 'https://3.com', 100)
, (4, 'https://4.com', 101)
, (2, 'https://2.com', 100)
;
CREATE INDEX ON user_photo (user_id, id DESC) INCLUDE (url);
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 4
CREATE INDEX
-- 1 row per user
SELECT json_build_object('id', u.id, 'url', p.url) AS "user"
FROM users u
LEFT JOIN (
SELECT DISTINCT ON (user_id)
user_id, url
FROM user_photo
ORDER BY user_id, id DESC
) p ON p.user_id = u.id;
| user |
|---|
| {"id" : 100, "url" : "https://3.com"} |
| {"id" : 101, "url" : "https://4.com"} |
| {"id" : 102, "url" : null} |
SELECT 3
-- summary array
SELECT json_agg(sub) AS "users"
FROM (
SELECT u.id, p.url
FROM users u
LEFT JOIN (
SELECT DISTINCT ON (user_id)
user_id, url
FROM user_photo
ORDER BY user_id, id DESC
) p ON p.user_id = u.id
) sub;
| users |
|---|
| [{"id":100,"url":"https://3.com"}, {"id":101,"url":"https://4.com"}, {"id":102,"url":null}] |
SELECT 1
-- also skip null values
SELECT json_strip_nulls(json_agg(sub)) AS "users"
FROM (
SELECT u.id, p.url
FROM users u
LEFT JOIN (
SELECT DISTINCT ON (user_id)
user_id, url
FROM user_photo
ORDER BY user_id, id DESC
) p ON p.user_id = u.id
) sub;
| users |
|---|
| [{"id":100,"url":"https://3.com"},{"id":101,"url":"https://4.com"},{"id":102}] |
SELECT 1
-- for a small selection
SELECT json_build_object('id', u.id, 'url', p.url) AS "user"
FROM users u
LEFT JOIN LATERAL (
SELECT url
FROM user_photo up
WHERE up.user_id = u.id
ORDER BY id DESC
LIMIT 1
) p ON true
WHERE u.id IN (100, 101, 102); -- small selection
| user |
|---|
| {"id" : 100, "url" : "https://3.com"} |
| {"id" : 101, "url" : "https://4.com"} |
| {"id" : 102, "url" : null} |
SELECT 3
-- summary array for a small selection, skipping null values
SELECT json_strip_nulls(json_agg(sub)) AS "users"
FROM (
SELECT u.id, p.url
FROM users u
LEFT JOIN LATERAL (
SELECT url
FROM user_photo up
WHERE up.user_id = u.id
ORDER BY id DESC
LIMIT 1
) p ON true
WHERE u.id IN (100, 101, 102) -- small selection
) sub;
| users |
|---|
| [{"id":100,"url":"https://3.com"},{"id":101,"url":"https://4.com"},{"id":102}] |
SELECT 1