2

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 the user's friends have recently posted, the query is still reasonably fast (query plan). But if the user's friends haven't recently posted or the user has no friends, it quickly deteriorates (query plan).

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?

I've uploaded the schema and the queries I've tried to dbfiddle. The output of SELECT version() is PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit for me.

  • 1
    I think that your model was missing date field, because the mostly recently activity can be an edition in one post. Try to add a create and update date add an index an try to filter and order in those fileds. – Krismorte Feb 7 '18 at 13:31
  • Perhaps I've misunderstood your comment, but the id column in posts is guaranteed to be strictly increasing. As a primary key it is also indexed. Ordering by it already returns the most recent rows, regardless of the missing date field. – mkslaf Feb 7 '18 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.