时间戳

首页 > 解决方案 > 在 Postgres 中使用子查询重构执行缓慢的查询

postgresql - 在 Postgres 中使用子查询重构执行缓慢的查询

问题描述

我有一个执行时间过长（600 毫秒）的查询。这主要是由于有 Distinct 关键字，没有它它会在大约 20 毫秒内执行。我试图取出 Distinct 并在 visit.participation_id 上使用 Group By，但我没有看到任何性能改进。这是原始查询：

SELECT COUNT(*) FROM "participations" WHERE "participations"."event_id" = $1 AND "participations"."is_preview" = $2 AND ("participations"."id" NOT IN (SELECT DISTINCT "visits"."participation_id" FROM "visits" INNER JOIN "ahoy_events" ON "ahoy_events"."visit_id" = "visits"."id" WHERE "visits"."event_id" = $3 AND "visits"."participation_id" IN (SELECT "participations"."id" FROM "participations" WHERE "participations"."event_id" = $4 AND "participations"."is_preview" = $5)))

我已经有关于访问参与者 ID 和事件 ID 的索引。

如何将其重构为不使用 Distinct 操作，或者还能做什么？在这里使用物化视图是否有意义？

标签： postgresqlactiverecordquery-optimization

解决方案

你可以重写它EXISTS：

SELECT COUNT(*) 
FROM "participations" p2
WHERE p2."event_id" = $1 
   AND p2."is_preview" = $2 
   AND NOT EXISTS (SELECT 1
                   FROM "visits" 
                   JOIN "ahoy_events" ON "ahoy_events"."visit_id" = "visits"."id" 
                   JOIN  "participations" ON "visits"."participation_id" = "participations"."id"
                   WHERE "visits"."event_id" = $3 
                     AND "participations"."event_id" = $4  
                     AND "participations"."is_preview" = $5
                     AND p2."participation_id" = "participations"."id"))

推荐阅读