postgresql - 如何优化此 postgresql 查询？

Question

下面是一个 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)

Answer 1

当您说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。然后它将转到索引，找出所有匹配 both 表单实例和字段 id 的 field_instances，然后它会准确找到感兴趣的结果.因为索引更具体，所以数据库将处理更少的数据行来处理您的查询。