0

I'm creating a social network, and I need a query to fetch the posts for a user's feed. I have the following (simplified) tables:

CREATE TABLE users (
  user_id bigint NOT NULL (primary key),
  username character varying(32)
);

CREATE TABLE posts (
  post_id bigint NOT NULL (primary key),
  repost_id bigint,
  text text,
  media jsonb,
  timestamp timestamp with time zone DEFAULT NOW() NOT NULL,
  user_id bigint
);

CREATE TABLE followers (
  follower_id bigint NOT NULL (primary key),
  user_id bigint NOT NULL,
  following_user_id bigint NOT NULL
);

Users kan follow other users, and their posts will appear on the feed. Users kan repost another post, I want to include these reposts in the feed.

I have the following query (for now). This query works, but is has a lot of CASE statements and joins, and I don't know if this query is the best way to go. I'm a postgresql beginner and I want to know if somebody has some tips to perfect this query.

SELECT
  a.post_id::int,
  a.repost_id::int,
  CASE WHEN a.repost_id IS NULL THEN a.text ELSE b.text END,
  CASE WHEN a.repost_id IS NULL THEN a.media ELSE b.media END,
  CASE WHEN a.repost_id IS NULL THEN a.timestamp ELSE b.timestamp END,
  c.username,
  d.username as original_username
FROM
  posts a
LEFT JOIN
  posts b
  ON a.repost_id = b.post_id
JOIN
  users c
  ON a.user_id = c.user_id
LEFT JOIN
  users d
  ON b.user_id = d.user_id
WHERE
  a.user_id IN (SELECT following_user_id FROM followers WHERE user_id = $1)
  AND
  post_id < $2
ORDER BY
  post_id DESC
LIMIT
  10

Explanation:

I select all posts from the table posts (a) where the user_id is contained in the results of the query to select all the following_user_id's of a user from the table followers (subquery in WHERE). I also do a LEFT JOIN on the table posts (b) to select the information from the reposted post. In the SELECT with the CASE statements, I check if the repost_id is NULL. If it is null, I need the post information from (a), otherwise, I need the post information from (b). The same thing with the usernames. username from (c) is the username from the post itself, the username from (d) is the username of the reposted post (if this is a reposted post), or NULL if it's an original post itself.

A second option is to copy the row op the original post when someone reposts a post, but I want to know if my initial option is OK to?

8
  • How quickly does it run right now? How much data does it return? – J.D. Mar 17 at 2:50
  • The case statements are involved in the output rather than in the query processing / filtering - isn't that what EXPLAIN is showing?? – Mr R Mar 17 at 10:35
  • @J.D. currently it's pretty fast, but I only have 25 users and 200 posts in my database yet. – yesterday Mar 17 at 10:57
  • @MrR: I will check it out! thank you for your answer! – yesterday Mar 17 at 10:57
  • 1
    @yesterday How fast is "pretty fast"? lol. If for that amount of data if it's running in a few milliseconds, then I doubt you have anything to worry about even when the data grows, if it's running in a few seconds then you probably have an issue. – J.D. Mar 17 at 11:17
1

Moved from a comment because getting lengthy:

I think if your query is executing in only 4ms currently you're fine. The relationship between data growth and execution time is not linear, so if your data even grew 10x what it currently is and you re-ran your query, it'll probably still run in under 10ms, roughly speaking. I don't see much issues in your query itself as long as your indexing everything properly.

The only recommendation I could make is to avoid sub-queries when not necessary, especially as part of predicates, such as in your WHERE clause. This can lead to slight inefficiencies, and there's a better relational alternative by just doing an INNER JOIN to the followers table from your subquery on the user_id = following_user_id field. You may never run into a performance issue as you currently wrote it, but just a heads up a better alternative exists.

Example:

SELECT
  a.post_id::int,
  a.repost_id::int,
  CASE WHEN a.repost_id IS NULL THEN a.text ELSE b.text END,
  CASE WHEN a.repost_id IS NULL THEN a.media ELSE b.media END,
  CASE WHEN a.repost_id IS NULL THEN a.timestamp ELSE b.timestamp END,
  c.username,
  d.username as original_username
FROM
  posts a
INNER JOIN followers f
    ON a.user_id = f.following_user_id
    AND f.user_id = $1
LEFT JOIN
  posts b
      ON a.repost_id = b.post_id
JOIN
  users c
      ON a.user_id = c.user_id
LEFT JOIN
  users d
      ON b.user_id = d.user_id
WHERE
  post_id < $2
ORDER BY
  post_id DESC
LIMIT
  10
5
  • thank you for your answer, it's really helpful! one last question if possible: based on my original query, what would you index? I find it pretty dificult to determine the best indexes when using JOINs, bigger WHERE clauses, ... – yesterday Mar 17 at 11:46
  • @yesterday To keep it simple, if you have the flexibility to create any indexes you want, and you're starting from day 0 with no indexes currently, then you could add an index for every composite of fields in your predicates (JOIN, WHERE, and HAVING clauses) of the above query. So for example, the posts table could have one index on (post_id, repost_id, user_id) since all 3 fields are used in the predicates of your query, this is called a composite index (it's one index composed of multiple fields), similarly the users table should have an index on user_id, etc. Testing is... – J.D. Mar 17 at 11:56
  • ...important here too, because you may find the composite index on the posts table is better as (post_id, user_id) and a second index on (repost_id) is better. Or maybe a separate index for each of the 3 fields is best (though I'd start with the first suggestion, then test the second, as that's my best guess from looking at the query). – J.D. Mar 17 at 11:58
  • 1
    Sorry for my belated answer @JD. I want to thank you for your time for explaining this. I will search for some articles/other topics about this stuff because I do not really understand everything yet, but now I know what to look for. Have a nice day! – yesterday Mar 17 at 16:34
  • @yesterday sure thing, no problem! Best of luck and have a good day! 🙂 – J.D. Mar 17 at 17:07

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.