sql - 为什么加入需要花费大量时间来执行？

Question

为什么加入这么慢？我该如何解决？

=> 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)

Answer 1

基于数字字段进行JOINS，并尝试使用子查询来减少项目