postgresql - 帮助改进查询。尝试使用 EXPLAIN

Question

我有一个实际上并不复杂的查询。它需要接近 250 毫秒才能运行，这非常慢。我已经使用 EXPLAIN 分析了查询并注意到了 seq 扫描。我为该查询中使用的所有列设置了适当的索引。所以我不确定从这里去哪里。

这是我所拥有的:

cl_production=# EXPLAIN SELECT count(DISTINCT events.id) AS count_distinct_events_id FROM "events" INNER JOIN "events_tickets" ON "events_tickets".event_id = "events".id INNER JOIN "tickets" ON "tickets".id = "events_tickets".ticket_id WHERE ((events.occurs_at > '2011-08-20 07:00:00.000000') AND (tickets.company_id = 175));
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=15735.79..15735.80 rows=1 width=4)
   ->  Hash Join  (cost=10540.01..15651.89 rows=33562 width=4)
         Hash Cond: (events_tickets.event_id = events.id)
         ->  Hash Join  (cost=3510.07..7516.61 rows=33562 width=4)
               Hash Cond: (events_tickets.ticket_id = tickets.id)
               ->  Seq Scan on events_tickets  (cost=0.00..1803.80 rows=124980 width=8)
               ->  Hash  (cost=3058.63..3058.63 rows=27475 width=4)
                     ->  Bitmap Heap Scan on tickets  (cost=521.19..3058.63 rows=27475 width=4)
                           Recheck Cond: (company_id = 175)
                           ->  Bitmap Index Scan on index_tickets_on_company_id  (cost=0.00..514.33 rows=27475 width=0)
                                 Index Cond: (company_id = 175)
         ->  Hash  (cost=5963.87..5963.87 rows=64965 width=4)
               ->  Index Scan using index_events_on_occurs_at on events  (cost=0.00..5963.87 rows=64965 width=4)
                     Index Cond: (occurs_at > '2011-08-20 07:00:00'::timestamp without time zone)

如前所述，这是我从模式文件中获得的索引:

add_index "events_tickets", ["event_id", "ticket_id"], :name => "index_events_tickets_on_event_id_and_ticket_id", :unique => true
add_index "events_tickets", ["event_id"], :name => "index_events_tickets_on_event_id"
add_index "events_tickets", ["ticket_id"], :name => "index_events_tickets_on_ticket_id"
add_index "events", ["occurs_at"], :name => "index_events_on_occurs_at"
add_index "tickets", ["company_id"], :name => "index_tickets_on_company_id"

我假设序列扫描是杀死这个查询的原因。我在那张 table 上有非常详尽的索引。所以我迷路了。任何帮助将不胜感激。

谢谢。

Answer 1

您应该执行 EXPLAIN ANALYZE 以获得计划每个节点的实际时间，而不仅仅是成本估算。

也许这种使用半连接而不是 count-distinct 聚合的查询形式会有所帮助:

SELECT COUNT(*)
FROM events
WHERE EXISTS (SELECT 1
              FROM events_tickets
                   JOIN tickets ON tickets.id = events_tickets.ticket_id
              WHERE tickets.company_id = 175
                    AND events_tickets.event_id = events.id)
      AND events.occurs_at > '2011-08-20 07:00:00'::timestamp