Hello,
I have the following schema:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE friends (
user_id BIGINT NOT NULL REFERENCES users,
friend_user_id BIGINT NOT NULL REFERENCES users,
UNIQUE (user_id, friend_user_id)
);
CREATE TABLE posts (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users,
content TEXT NOT NULL
);
CREATE INDEX posts_user_id_id_index ON posts(user_id, id);
Each user can unilaterally follow any number of friends. The posts table has a large number of rows and is rapidly growing.
My goal is to retrieve the 10 most recent posts of a user's friends. This query gives the correct result, but is inefficient:
SELECT posts.id, users.name, posts.content
FROM posts JOIN users ON posts.user_id = users.id
WHERE posts.user_id IN (SELECT friend_user_id FROM friends WHERE user_id = 1)
ORDER BY posts.id DESC LIMIT 10;
If I match only a single post author (e.g. WHERE posts.user_id = 5), Postgres uses the index posts_user_id_id_index. But if I use IN, the index doesn't appear to be used at all.
How can I get these results more efficiently?
Thank you in advance for any insights, pointers or suggestions you are able to give me.
Regards,
Milo
