70

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others.

With this style of query, the obvious optimization would be to cache the "Post" ids, but unfortunately I do not have the time for that right now.

EXPLAIN ANALYZE SELECT
    "Post"."id",
    "Post"."actionId",
    "Post"."commentCount",
    ...
FROM
    "Posts" AS "Post"
INNER JOIN "Users" AS "user" ON "Post"."userId" = "user"."id"
LEFT OUTER JOIN "ActivityLogs" AS "activityLog" ON "Post"."activityLogId" = "activityLog"."id"
LEFT OUTER JOIN "WeightLogs" AS "weightLog" ON "Post"."weightLogId" = "weightLog"."id"
LEFT OUTER JOIN "Workouts" AS "workout" ON "Post"."workoutId" = "workout"."id"
LEFT OUTER JOIN "WorkoutLogs" AS "workoutLog" ON "Post"."workoutLogId" = "workoutLog"."id"
LEFT OUTER JOIN "Workouts" AS "workoutLog.workout" ON "workoutLog"."workoutId" = "workoutLog.workout"."id"
WHERE
"Post"."userId" IN (
    201486,
    1825186,
    998608,
    340844,
    271909,
    308218,
    341986,
    216893,
    1917226,
    ...  -- many more
)
AND "Post"."private" IS NULL
ORDER BY
    "Post"."createdAt" DESC
LIMIT 10;

Yields:

Limit  (cost=3.01..4555.20 rows=10 width=2601) (actual time=7923.011..7973.138 rows=10 loops=1)
  ->  Nested Loop Left Join  (cost=3.01..9019264.02 rows=19813 width=2601) (actual time=7923.010..7973.133 rows=10 loops=1)
        ->  Nested Loop Left Join  (cost=2.58..8935617.96 rows=19813 width=2376) (actual time=7922.995..7973.063 rows=10 loops=1)
              ->  Nested Loop Left Join  (cost=2.15..8821537.89 rows=19813 width=2315) (actual time=7922.984..7961.868 rows=10 loops=1)
                    ->  Nested Loop Left Join  (cost=1.71..8700662.11 rows=19813 width=2090) (actual time=7922.981..7961.846 rows=10 loops=1)
                          ->  Nested Loop Left Join  (cost=1.29..8610743.68 rows=19813 width=2021) (actual time=7922.977..7961.816 rows=10 loops=1)
                                ->  Nested Loop  (cost=0.86..8498351.81 rows=19813 width=1964) (actual time=7922.972..7960.723 rows=10 loops=1)
                                      ->  Index Scan using posts_createdat_public_index on "Posts" "Post"  (cost=0.43..8366309.39 rows=20327 width=261) (actual time=7922.869..7960.509 rows=10 loops=1)
                                            Filter: ("userId" = ANY ('{201486,1825186,998608,340844,271909,308218,341986,216893,1917226, ... many more ...}'::integer[]))
                                            Rows Removed by Filter: 218360
                                      ->  Index Scan using "Users_pkey" on "Users" "user"  (cost=0.43..6.49 rows=1 width=1703) (actual time=0.005..0.006 rows=1 loops=10)
                                            Index Cond: (id = "Post"."userId")
                                ->  Index Scan using "ActivityLogs_pkey" on "ActivityLogs" "activityLog"  (cost=0.43..5.66 rows=1 width=57) (actual time=0.107..0.107 rows=0 loops=10)
                                      Index Cond: ("Post"."activityLogId" = id)
                          ->  Index Scan using "WeightLogs_pkey" on "WeightLogs" "weightLog"  (cost=0.42..4.53 rows=1 width=69) (actual time=0.001..0.001 rows=0 loops=10)
                                Index Cond: ("Post"."weightLogId" = id)
                    ->  Index Scan using "Workouts_pkey" on "Workouts" workout  (cost=0.43..6.09 rows=1 width=225) (actual time=0.001..0.001 rows=0 loops=10)
                          Index Cond: ("Post"."workoutId" = id)
              ->  Index Scan using "WorkoutLogs_pkey" on "WorkoutLogs" "workoutLog"  (cost=0.43..5.75 rows=1 width=61) (actual time=1.118..1.118 rows=0 loops=10)
                    Index Cond: ("Post"."workoutLogId" = id)
        ->  Index Scan using "Workouts_pkey" on "Workouts" "workoutLog.workout"  (cost=0.43..4.21 rows=1 width=225) (actual time=0.004..0.004 rows=0 loops=10)
              Index Cond: ("workoutLog"."workoutId" = id)
Total runtime: 7974.524 ms

How can this be optimized for the time being?

I have the following relevant indexes:

-- Gets used
CREATE INDEX  "posts_createdat_public_index" ON "public"."Posts" USING btree("createdAt" DESC) WHERE "private" IS null;
-- Don't get used
CREATE INDEX  "posts_userid_fk_index" ON "public"."Posts" USING btree("userId");
CREATE INDEX  "posts_following_index" ON "public"."Posts" USING btree("userId", "createdAt" DESC) WHERE "private" IS null;

Perhaps this requires a large partial composite index with createdAt and userId where private IS NULL?

Improve this question

3 Answers 3

Reset to default
62

Instead of using a huge IN-list, join on a VALUES expression, or if the list is large enough, use a temp table, index it, then join on it.

It'd be nice if PostgreSQL could do this internally & automatically but at this point the planner doesn't know how.

Similar topics:

Improve this answer
61

There are actually two different variants of the IN construct in Postgres. One works with a subquery expression (returning a set), the other one with a list of values, which is just shorthand for 

expression = value1
OR
expression = value2
OR
...

You are using the second form, which is fine for a short list, but much slower for long lists. Provide your list of values as subquery expression instead. I was recently made aware of this variant:

WHERE "Post"."userId" IN (VALUES (201486), (1825186), (998608), ... )

I like to pass an array, unnest and join to it. Similar performance, but the syntax is shorter:

...
FROM   unnest('{201486,1825186,998608, ...}'::int[]) "userId"
JOIN   "Posts" "Post" USING ("userId")

Equivalent as long as there are no duplicates in the provided set / array. Else the second form with a JOIN returns duplicate rows, while the first with IN only returns a single instance. This subtle difference causes different query plans, too.

Obviously, you need an index on "Posts"."userId".
For very long lists (thousands), go with an indexed temp table like @Craig suggested. This allows combined bitmap index scans over both tables, which is typically faster as soon as there are multiple tuples per data page to fetch from disk.

Related:

Aside: your naming convention is not very helpful, makes your code verbose and hard to read. Rather use legal, lower-case, unquoted identifiers.

Improve this answer
1
  • 3
    This is the best answer, thanks! Better than the accepted answer, IMHO. Great explanation and very helpful SQL snippet showing how to convert from a list to a subquery. I was able to reduce my page fetch cost by over 90% using a subquery! And, you won't believe this, but that was on an unindexed column (!)
    – Bad Request
    Commented Oct 12, 2021 at 2:41
0

You can disable nested loops to force a hash join indexing the values. similar to what Craig Ringer said

set enable_nestloop to off;

This setting only applies to your current db session, if you need to turn it back on after that query, just execute

set enable_nestloop to on;

at the end.

You can expect improvement in several orders of magnitude in execution time.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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