PostgreSQL查询优化问题

Question

以下是两个几乎相同的Postgres查询，但会产生截然不同的查询计划和执行时间。我假设第一个查询很快，因为只有196个form_id为“W40”的form_instance记录，而form_id为“W30L”的记录则有7000个。但是为什么从200条记录跳到7000条记录（在我看来似乎相对较小）会导致查询时间如此惊人地增加呢？我尝试过以各种方式索引数据以加快速度，但基本上束手无策。如何加速查询呢？（请注意，两个表的模式已包含在底部。）

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 in ('Book_EstimatedDueDate','H_SubmittedDate','H_Ccode','miscarriage','miscarriage_of_multiple','stillbirth','AP_IUFD_of_multiple','maternal_death','birth_includes_transport','newborn_death','H_Pid','H_Mid1','H_Mid2','H_Mid3')
and (form_id = 'W40');

QUERY PLAN                                                                                                                                
 Nested Loop  (cost=0.00..70736.14 rows=4646 width=29) (actual time=0.000..20.000 rows=2399 loops=1)
   ->  Index Scan using form_id_and_workflow_state on form_instances  (cost=0.00..1041.42 rows=507 width=8) (actual time=0.000..0.000 rows=196 loops=1)
         Index Cond: (((form_id)::text = 'W40'::text) AND ((workflow_state)::text = 'DRqueued'::text))
   ->  Index Scan using index_field_instances_on_form_instance_id on field_instances  (cost=0.00..137.25 rows=17 width=25) (actual time=0.000..0.102 rows=12 loops=196)
         Index Cond: (field_instances.form_instance_id = form_instances.id)
         Filter: ((field_instances.field_id)::text = ANY ('{Book_EstimatedDueDate,H_SubmittedDate,H_Ccode,miscarriage,miscarriage_of_multiple,stillbirth,AP_IUFD_of_multiple,maternal_death,birth_includes_transport,newborn_death,H_Pid,H_Mid1,H_Mid2,H_Mid3}'::text[]))
 Total runtime: 30.000 ms
(7 rows)

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 in ('Book_EstimatedDueDate','H_SubmittedDate','H_Ccode','miscarriage','miscarriage_of_multiple','stillbirth','AP_IUFD_of_multiple','maternal_death','birth_includes_transport','newborn_death','H_Pid','H_Mid1','H_Mid2','H_Mid3') 
and (form_id = 'W30L');

QUERY PLAN                                                                                                                             
 Hash Join  (cost=34300.46..160865.40 rows=31045 width=29) (actual time=65670.000..74960.000 rows=102777 loops=1)
   Hash Cond: (field_instances.form_instance_id = form_instances.id)
   ->  Bitmap Heap Scan on field_instances  (cost=29232.57..152163.82 rows=531718 width=25) (actual time=64660.000..72800.000 rows=526842 loops=1)
         Recheck Cond: ((field_id)::text = ANY ('{Book_EstimatedDueDate,H_SubmittedDate,H_Ccode,miscarriage,miscarriage_of_multiple,stillbirth,AP_IUFD_of_multiple,maternal_death,birth_includes_transport,newborn_death,H_Pid,H_Mid1,H_Mid2,H_Mid3}'::text[]))
         ->  Bitmap Index Scan on index_field_instances_on_field_id  (cost=0.00..29099.64 rows=531718 width=0) (actual time=64630.000..64630.000 rows=594515 loops=1)
               Index Cond: ((field_id)::text = ANY ('{Book_EstimatedDueDate,H_SubmittedDate,H_Ccode,miscarriage,miscarriage_of_multiple,stillbirth,AP_IUFD_of_multiple,maternal_death,birth_includes_transport,newborn_death,H_Pid,H_Mid1,H_Mid2,H_Mid3}'::text[]))
   ->  Hash  (cost=5025.54..5025.54 rows=3388 width=8) (actual time=980.000..980.000 rows=10457 loops=1)
         ->  Bitmap Heap Scan on form_instances  (cost=90.99..5025.54 rows=3388 width=8) (actual time=10.000..950.000 rows=10457 loops=1)
               Recheck Cond: (((form_id)::text = 'W30L'::text) AND ((workflow_state)::text = 'DRqueued'::text))
               ->  Bitmap Index Scan on form_id_and_workflow_state  (cost=0.00..90.14 rows=3388 width=0) (actual time=0.000..0.000 rows=10457 loops=1)
                     Index Cond: (((form_id)::text = 'W30L'::text) AND ((workflow_state)::text = 'DRqueued'::text))
 Total runtime: 75080.000 ms

# \d form_instances                                        Table "public.form_instances"     Column      |            Type             |                          Modifiers                          
-----------------+-----------------------------+-------------------------------------------------------------
 id              | integer                     | not null default nextval('form_instances_id_seq'::regclass)
 form_id         | character varying(255)      | 
 created_at      | timestamp without time zone | 
 updated_at      | timestamp without time zone | 
 created_by_id   | integer                     | 
 updated_by_id   | integer                     | 
 workflow        | character varying(255)      | 
 workflow_state  | character varying(255)      | 
 validation_data | text                        | 
Indexes:
    "form_instances_pkey" PRIMARY KEY, btree (id)
    "form_id_and_workflow_state" btree (form_id, workflow_state)
    "index_form_instances_on_form_id" btree (form_id)
    "index_form_instances_on_workflow_state" btree (workflow_state)

# \d field_instances
                                        Table "public.field_instances"
      Column      |            Type             |                          Modifiers                           
------------------+-----------------------------+--------------------------------------------------------------
 id               | integer                     | not null default nextval('field_instances_id_seq'::regclass)
 form_instance_id | integer                     | 
 created_at       | timestamp without time zone | 
 updated_at       | timestamp without time zone | 
 created_by_id    | integer                     | 
 updated_by_id    | integer                     | 
 field_id         | character varying(255)      | 
 answer           | text                        | 
 state            | character varying(255)      | 
 explanation      | text                        | 
 idx              | integer                     | not null default 0
Indexes:
    "field_instances_pkey" PRIMARY KEY, btree (id)
    "field_instances__lower_answer" btree (lower(answer))
    "index_field_instances_on_answer" btree (answer)
    "index_field_instances_on_field_id" btree (field_id)
    "index_field_instances_on_field_id_and_answer" btree (field_id, answer)
    "index_field_instances_on_form_instance_id" btree (form_instance_id)
    "index_field_instances_on_idx" btree (idx)

- zippy

---

当然，系统对于可能存在的行数的估计是错误的。我们可以从第二个查询中看到，它从位图索引扫描中估计了3388行，但实际上得到了10457行。你可能需要执行vacuum full analyze;、reindex和/或cluster操作，以查看它们能否帮助解决问题。 - Seth Robertson

我执行了 vacuum full analyze，但没有任何效果，但是reindex产生了巨大的差异。谢谢。 - zippy

你使用的PG版本是哪个？ - Kuberchaun

Answer 1

我不确定你总结中的数字是从哪里来的，因为你发布的第二个查询计划输出了102777行，而第一个查询计划只输出了2399行。这是43倍的差距，所以选择非常不同的查询计划并不令人惊讶。至于为什么运行时间的差异甚至更大，优化器在估计对form_id和workflow_state过滤器的敏感度时犯了一个中等错误。如果您使用的是PostgreSQL 8.3，其中默认值相当低，您可能需要增加此数据库的default_statistics_target值并再次运行ANALYZE。有关该参数的更多信息，请参见Tuning Your PostgreSQL Server。

很可能两者之间的差异如此之大，仅仅是因为回答小查询所需的所有数据已经存在于内存中，而更大的查询涉及更多的磁盘访问来回答。多次运行每个查询可能会揭示运行时是否在将数据读入缓存后改善。您执行的REINDEX可能已经将索引缩小到足以适合两种情况的缓存中，从而暂时解决了问题。但是，该索引可能会再次变得“膨胀”。

- Greg Smith

Answer 2

之前是一条评论，但由于似乎已经解决了问题，我将其提升为实际答案。

系统对可能有多少行的估计是错误的。我们可以在第二个查询中看到它从位图索引扫描中估计了3388行，但实际上得到了10457行。

因此，您可能需要执行vacuum full analyze;

此外，其他可以极大帮助的命令包括reindex和/或cluster。

OP表示vacuum没有帮助，但reindex有用。

- Seth Robertson

---

虽然它确实起作用了，但奇怪的是它甚至选择了同样的查询计划，仍然错误地估算了行值。Bitmap Heap Scan在form_instances上（成本=92.09..3597.16行=3496宽度=8）（实际时间=60.000..100.000行=10462循环=1）。所以我不知道为什么这个方法可行。 - zippy

“vacuum full”与此问题无关，在大多数情况下，它实际上会使性能变差。请参见http://wiki.postgresql.org/wiki/VACUUM_FULL了解详情。更新统计信息所需的仅是简单的“ANALYZE”。 - Greg Smith

@Greg：你的说法既正确又无关紧要。维基页面讨论的是操作时的性能和更新/写入性能——它应该严格提高只读性能（除了索引问题）。索引未被更新的说法同样正确，但请注意我确实建议重新创建索引，并且事实上解决了他的问题（不受vacuum影响）。然而，提醒一下，vacuum full在理解后才应使用，这对大多数命令也是适用的。 - Seth Robertson