慢查询性能,只是一个'简单的'Postgres查询
我有一个简单的SQL查询表现不是很好。 数据集也非常简单,只是一个任务表和一个user_tasks表。 在数据库中目前只有50.000条记录存在慢查询性能,只是一个'简单的'Postgres查询
我有任务字段和user_tasks字段的索引。
查询完成需要> 300ms。
SELECT DISTINCT ON (t0."id") * FROM "tasks" t0 INNER JOIN
"user_tasks" u1
ON TRUE
WHERE (t0."id" = u1."task_id") AND
(((u1."user_id" = 'f4325fd5-8862-4563-a534-957d76ac98b9') AND t0."delegate_id" IS NULL)
OR ((t0."delegate_id" = 'f4325fd5-8862-4563-a534-957d76ac98b9') AND
(u1."user_id" != 'f4325fd5-8862-4563-a534-957d76ac98b9')))
ORDER BY t0."id", t0."start_at"
这里是输出的讲解分析
Unique (cost=75397.69..75398.18 rows=98 width=2979) (actual time=479.995..480.041 rows=100 loops=1) -> Sort (cost=75397.69..75397.93 rows=98 width=2979) (actual time=479.994..480.004 rows=100 loops=1)
Sort Key: t0.id, t0.start_at
Sort Method: quicksort Memory: 51kB
-> Gather (cost=1000.85..75394.45 rows=98 width=2979) (actual time=17.529..479.734 rows=100 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Merge Join (cost=0.84..74384.65 rows=41 width=2979) (actual time=64.989..475.499 rows=33 loops=3)
Merge Cond: (t0.id = u1.task_id)
Join Filter: (((u1.user_id = 'f4325fd5-8862-4563-a534-957d76ac98b9'::uuid) AND (t0.delegate_id IS NULL)) OR ((t0.delegate_id = 'f4325fd5-8862-4563-a534-957d76ac98b9'::uuid) AND (u1.user_id <> 'f4325fd5-8862-4563-a534-957d76ac98b9'::uuid)))
Rows Removed by Join Filter: 123267
-> Parallel Index Scan using tasks_id_index on tasks t0 (cost=0.42..44772.95 rows=154209 width=2919) (actual time=0.023..125.719 rows=123300 loops=3)
-> Index Scan using user_tasks_task_id_index on user_tasks u1 (cost=0.42..25604.24 rows=369900 width=36) (actual time=0.042..253.000 rows=369755 loops=3)
Planning time: 0.506 ms
Execution time: 488.518 ms
我条纹下来询问,直到我发现它变快。
EXPLAIN ANALYZE SELECT DISTINCT ON (t0."id") * FROM "tasks" AS t0 INNER JOIN "user_tasks" AS u1 ON (t0."id" = u1."task_id")
WHERE t0."delegate_id" = 'f4325fd5-8862-4563-a534-957d76ac98b9'
OR u1."user_id" = 'f4325fd5-8862-4563-a534-957d76ac98b9'
如果删除或和声明它变快〜0.7毫秒,如果我在哪里U1。“USER_ID”查询=和它快,如果我在哪里T0。“delegate_id”查询=其快速
任务对delegate_id user_tasks一个指标对user_ID的指数& TASK_ID & user_task_id
回答:
钉它
EXPLAIN ANALYZE SELECT *
FROM "tasks" AS t0 INNER JOIN
(select *
from user_tasks ut
where user_id = 'f4325fd5-8862-4563-a534-957d76ac98b9'
) u1
ON (t0."id" = u1."task_id")
WHERE t0."delegate_id" = 'f4325fd5-8862-4563-a534-957d76ac98b9'
以上是 慢查询性能,只是一个'简单的'Postgres查询 的全部内容, 来源链接: utcz.com/qa/264891.html