慢查询性能,只是一个'简单的'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

回到顶部