为什么加入这么慢?我怎么能修好它?
=> EXPLAIN ANALYZE SELECT "acts".*
FROM "acts"
LEFT OUTER JOIN "tasks" ON "acts"."id" = "tasks"."act_id"
WHERE "acts"."state" IN (0,
1)
AND ((tasks.id IS NULL
AND acts.id IN
(SELECT "act_participants"."act_id"
FROM "act_participants"
WHERE "act_participants"."user_id" = 2
AND "act_participants"."nature" = 0))
OR (acts.id IN
(SELECT "tasks"."act_id"
FROM "tasks"
INNER JOIN "task_participants" ON "task_participants"."task_id" = "tasks"."id"
WHERE (tasks.state != 1)
AND (tasks.state != 2)
AND "task_participants"."user_id" = 2
AND "task_participants"."state" = 0
AND "task_participants"."nature" = 0)))
ORDER BY acts.created_at ASC
LIMIT 50;
这是查询计划
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=786.65..967.73 rows=50 width=87) (actual time=951.623..1466.844 rows=1 loops=1)
-> Nested Loop Left Join (cost=786.65..329027.62 rows=90638 width=87) (actual time=951.620..1466.839 rows=1 loops=1)
Filter: (((tasks.id IS NULL) AND (hashed SubPlan 1)) OR (hashed SubPlan 2))
Rows Removed by Filter: 178275
-> Index Scan using index_acts_on_created_at on acts (cost=0.43..62194.86 rows=181276 width=87) (actual time=0.022..714.109 rows=175433 loops=1)
Filter: (state = ANY ('{0,1}'::integer[]))
Rows Removed by Filter: 956367
-> Index Scan using index_tasks_on_act_id on tasks (cost=0.43..1.45 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=175433)
Index Cond: (acts.id = act_id)
SubPlan 1
-> Index Scan using index_act_participants_on_user_id on act_participants (cost=0.43..219.34 rows=44 width=4) (actual time=0.019..0.117 rows=37 loops=1)
Index Cond: (user_id = 2)
Filter: (nature = 0)
Rows Removed by Filter: 22
SubPlan 2
-> Nested Loop (cost=0.85..566.32 rows=12 width=4) (actual time=0.099..0.099 rows=0 loops=1)
-> Index Scan using index_task_participants_on_user_id on task_participants (cost=0.43..371.68 rows=23 width=4) (actual time=0.097..0.097 rows=0 loops=1)
Index Cond: (user_id = 2)
Filter: ((state = 0) AND (nature = 0))
Rows Removed by Filter: 50
-> Index Scan using tasks_pkey on tasks tasks_1 (cost=0.43..8.45 rows=1 width=8) (never executed)
Index Cond: (id = task_participants.task_id)
Filter: ((state <> 1) AND (state <> 2))
Total runtime: 1466.947 ms
(24 rows)
