如何优化这个PostgreSQL查询？

Question

如何优化这个PostgreSQL查询？

4

以下是一个Postgres查询，似乎比我预期的要花费更长的时间。field_instances表在form_instance_id和field_id上都建有索引，而form_instances表在workflow_state上有索引。所以我认为这应该是一个快速查询，但它却需要很长时间。请问有人可以帮助我解释查询计划以及应该添加哪些索引来加快速度吗？谢谢。

explain analyze
select form_id,form_instance_id,answer,field_id
from form_instances,field_instances
where workflow_state = 'DRqueued'
    and form_instance_id = form_instances.id
    and field_id = 'Book_EstimatedDueDate';
                                                                               QUERY PLAN                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=8733.85..95692.90 rows=9277 width=29) (actual time=2550.000..15430.000 rows=11431 loops=1)
   Hash Cond: (field_instances.form_instance_id = form_instances.id)
   ->  Bitmap Heap Scan on field_instances  (cost=2681.11..89071.72 rows=47567 width=25) (actual time=850.000..13690.000 rows=51726 loops=1)
         Recheck Cond: ((field_id)::text = 'Book_EstimatedDueDate'::text)
         ->  Bitmap Index Scan on index_field_instances_on_field_id  (cost=0.00..2669.22 rows=47567 width=0) (actual time=830.000..830.000 rows=51729 loops=1)
               Index Cond: ((field_id)::text = 'Book_EstimatedDueDate'::text)
   ->  Hash  (cost=5911.34..5911.34 rows=11312 width=8) (actual time=1590.000..1590.000 rows=11431 loops=1)
         ->  Bitmap Heap Scan on form_instances  (cost=511.94..5911.34 rows=11312 width=8) (actual time=720.000..1570.000 rows=11431 loops=1)
               Recheck Cond: ((workflow_state)::text = 'DRqueued'::text)
               ->  Bitmap Index Scan on index_form_instances_on_workflow_state  (cost=0.00..509.11 rows=11312 width=0) (actual time=650.000..650.000 rows=11509 loops=1)
                     Index Cond: ((workflow_state)::text = 'DRqueued'::text)
 Total runtime: 15430.000 ms
(12 rows)

- zippy

你可以尝试像这样输入 set enable_hashjoin = 0;，看看是否能得到更快的执行计划。如果是，那么我们将继续检查为什么一开始没有使用该计划。 - sayap

有几件事情。这是哪个版本的pg？你尝试过将work_mem增加一点吗（比如16MB左右）？哦，我们能否得到表的模式或完全限定的列名，当我不知道哪些列来自哪些表时，这有点令人困惑。此外，你尝试过使用显式连接语法吗？（即从a join b on (a.x=b.y)） - Scott Marlowe

3个回答

1

http://explain.depesz.com 是一个非常棒的在线工具，帮助你通过可视化方式识别热点。我将你的结果复制到该工具中，并得到了这个分析：http://explain.depesz.com/s/VIk

没有看到你的表和索引，很难给出具体建议。

- Andy Lester

我们被告知了关于表和索引的所有所需信息。同时，知道所有时间都花在哈希连接上并不能告诉我们如何摆脱这个哈希连接。 - btilly

0

需要知道您的表中具有哪些数据，但仅从SQL和列名中观察，我建议：

假设其中的元素不能非常唯一，在workflow_state上真的需要索引吗？这可能不会改善选择，但会插入或更新...
尝试使field_id检查where语句中的第一个条件。

- ramsinb

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- btilly · Accepted Answer

当你说“field_instances表在form_instance_id和field_id上都有索引”时，你指的是该表上有单独的form_instance_id和field_id索引吗？

试着删除form_instance_id上的索引，并在(form_instance_id, field_id)上放置一个联合索引。

索引的作用是给你一个快速的查找，告诉你哪些行与你的索引匹配。然后它必须通过这些行来完成你想要的操作。因此，你总是希望你的索引越具体越好。如果你在表上放置了两个索引，你将有两种不同的查找方式，但查询通常只会利用其中一种。如果你在表上放置一个联合索引，你将能够高效地查找索引中的第一个字段、前两个字段等。（因此，一个( a, b )的联合索引为你提供了快速的对a的查找，更快的同时对a和b的查找，但对b的查找不起作用）

现在它正在计算所有在form_instances中可能存在的合适状态。它分别计算所有符合字段id的field_instances。然后进行哈希连接。对于这个，从一个结果集中生成一个查找哈希，然后在另一个结果集中扫描匹配项。

按照我的建议，它应该找出所有感兴趣的form_instances。然后去索引中，找出与form实例和字段id都匹配的所有field_instances，并找到感兴趣的结果。因为索引更加具体，所以数据库需要处理的数据行数更少，能够更快地处理查询。