PostgreSQL9.x:优化`xpath_exists` (XMLEXISTS)查询的索引

Question

我们对表格有疑问。

select sum(acol)
where xpath_exists('/Root/KeyValue[Key="val"]/Value//text()', xmlcol)

可以建立什么索引来加快where子句的速度？

创建的btree索引使用

create index idx_01 using btree(xpath_exists('/Root/KeyValue[Key="val"]/Value//text()', xmlcol))

似乎根本不被使用。

编辑

将enable_seqscan设置为off，使用xpath_exists的查询要快得多(一个数量级)，并清楚地显示使用相应的索引(用xpath_exists构建的btree索引)。

PostgreSQL为什么不使用索引并尝试慢得多的顺序扫描呢？

由于我不想禁用顺序扫描全球，我回到原点，我很高兴欢迎建议。

编辑2-解释计划

见下文-第一个计划的成本(seqscan off)稍高，但处理时间要快得多。

b2box=# set enable_seqscan=off;
SET
b2box=# explain analyze
Select count(*) 
from B2HEAD.item
where cluster = 'B2BOX' and (  ( xpath_exists('/MessageInfo[FinalRecipient="ABigBank"]//text()', content) )  )  offset 0 limit 1;
                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=22766.63..22766.64 rows=1 width=0) (actual time=606.042..606.042 rows=1 loops=1)
   ->  Aggregate  (cost=22766.63..22766.64 rows=1 width=0) (actual time=606.039..606.039 rows=1 loops=1)
         ->  Bitmap Heap Scan on item  (cost=1058.65..22701.38 rows=26102 width=0) (actual time=3.290..603.823 rows=4085 loops=1)
               Filter: (xpath_exists('/MessageInfo[FinalRecipient="ABigBank"]//text()'::text, content, '{}'::text[]) AND ((cluster)::text = 'B2BOX'::text))
               ->  Bitmap Index Scan on item_counter_01  (cost=0.00..1052.13 rows=56515 width=0) (actual time=2.283..2.283 rows=4085 loops=1)
                     Index Cond: (xpath_exists('/MessageInfo[FinalRecipient="ABigBank"]//text()'::text, content, '{}'::text[]) = true)
 Total runtime: 606.136 ms
(7 rows)

explain.depesz.com计划

b2box=# set enable_seqscan=on;
SET
b2box=# explain analyze
Select count(*) 
from B2HEAD.item
where cluster = 'B2BOX' and (  ( xpath_exists('/MessageInfo[FinalRecipient="ABigBank"]//text()', content) )  )  offset 0 limit 1;
                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=22555.71..22555.72 rows=1 width=0) (actual time=10864.163..10864.163 rows=1 loops=1)
   ->  Aggregate  (cost=22555.71..22555.72 rows=1 width=0) (actual time=10864.160..10864.160 rows=1 loops=1)
         ->  Seq Scan on item  (cost=0.00..22490.45 rows=26102 width=0) (actual time=33.574..10861.672 rows=4085 loops=1)
               Filter: (xpath_exists('/MessageInfo[FinalRecipient="ABigBank"]//text()'::text, content, '{}'::text[]) AND ((cluster)::text = 'B2BOX'::text))
               Rows Removed by Filter: 108945
 Total runtime: 10864.242 ms
(6 rows)

explain.depesz.com计划

Answer 1

计划成本参数

第一个计划的成本(seqscan off)稍高，但处理时间要快得多。

这告诉我，您的random_page_cost和seq_page_cost可能是错误的。您可能使用快速随机I/O存储--要么是因为大部分数据库都缓存在RAM中，要么是因为您使用的是SSD、SAN和缓存，或者是其他随机I/O本身就快的存储。

尝试：

SET random_page_cost = 1;
SET seq_page_cost = 1.1;

大大降低了成本差异，然后重新运行。如果这样做的话，请考虑更改postgresql.conf.中的这些参数。

您的行计数估计是合理的，因此它看起来不像一个规划师错误估计问题或一个与坏的表统计问题。

不正确查询

您的查询也不正确。没有OFFSET 0 LIMIT 1的ORDER BY会产生不可预测的结果，除非保证只有一个匹配，在这种情况下，OFFSET ... LIMIT ...子句是不必要的，可以完全删除。

在可能的情况下，使用SELECT max(...)或SELECT min(...)这样的查询的措辞通常要好得多；PostgreSQL通常能够使用索引来提取所需的值，而无需进行昂贵的表扫描或索引扫描和排序。

提示

顺便说一句，对于未来的问题，PostgreSQL wiki在绩效范畴中有一些很好的信息，还有一个询问慢速查询问题的指南。