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;
产量：
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
如何暂时优化我有以下相关索引： />
#1 楼
而不是使用巨大的IN -list，而是使用VALUES表达式进行连接，或者如果列表足够大，则使用临时表，对其进行索引，然后对其进行连接。PostgreSQL可以在内部自动执行此操作，但是此时计划人员不知道如何操作。
类似的主题：
https://stackoverflow.com/ q / 24647503/398670
https://stackoverflow.com/q/17813492/398670
#2 楼
实际上，Postgres中的IN构造有两种不同的变体。一个使用子查询表达式（返回一个集合），另一个使用值列表，这只是expression = value1
OR
expression = value2
OR
...
的缩写，您正在使用第二种形式，短名单比较好，但长名单要慢得多。而是提供值列表作为子查询表达式。最近，我知道了这个变体：
WHERE "Post"."userId" IN (VALUES (201486), (1825186), (998608), ... )
我喜欢传递一个数组，嵌套并加入它。性能相似，但语法更短：只要提供的集合/数组中没有重复项，则等效。否则，带有
JOIN的第二种形式返回重复的行，而带有IN的第二种形式仅返回单个实例。这种细微的差别也会导致不同的查询计划。这允许对两个表进行组合的位图索引扫描，这通常会更快，因为每个数据页上有多个元组要从磁盘中获取。相关的内容： >如何在Rails的WHERE子句中使用ANY代替IN？
此外：您的命名约定不是很有用，会使您的代码冗长且难以阅读。而是使用合法的，小写的，未加引号的标识符。