PostgreSQL:索引优化 xmlexists on xml with arrays

Question

我正在将我的应用程序从 MS SQL 移植到 PostgreSQL 10.1，但我在处理 XML 字段时遇到了困难。我在查询中将“exist()”更改为“xmlexists()”，因此典型查询现在如下所示:

SELECT t."id", t."fullname"
FROM "candidates" t
WHERE xmlexists('//assignments/assignment/project_id[.=''6512779208625374885'']'
           PASSING BY REF t.assignments );

假设“assignment”列包含具有以下结构的 XML 数据:

<assignments>
<assignment>
    <project_id>6512779208625374885</project_id>
    <start_date>2018-02-05T14:30:06+00:00</start_date>
    <state_id>1</state_id>
</assignment>
<assignment>
    <project_id>7512979208625374996</project_id>
    <start_date>2017-12-01T15:30:00+00:00</start_date>
    <state_id>0</state_id>
</assignment>
<assignment>
    <project_id>5522979707625370402</project_id>
    <start_date>2017-12-15T10:00:00+00:00</start_date>
    <state_id>1</state_id>
</assignment>

问题是如何为这种类型的查询建立一个高效的索引。 A 我知道没有像 MS SQL 那样的通用 xpath 索引，所以我需要构建一个特定的索引。但是我设法找到的所有示例(例如 Postgresql 9.x: Index to optimize `xpath_exists` (XMLEXISTS) queries)都是关于嵌套字段的，而不是数组。

附言我尝试从 XML 切换到 JSONB，但这需要使用 jsonb_array_elements() 和连接重写很多查询，我想避免这种情况。

Answer 1

您可以利用 xpath() 返回数组的事实。

下面的表达式:

xpath('/assignments/assignment/project_id/text()', assignments)::text[]

返回包含所有项目 ID 的字符串数组。这个表达式可以被索引:

create index on candidates using gin ((xpath('/assignments/assignment/project_id/text()', assignments)::text[]));

并且该索引可以被以下查询使用:

select *
from candidates
where xpath('/assignments/assignment/project_id/text()', assignments)::text[] @> array['6512779208625374885'];

@> 是 GIN 索引支持的数组“包含”运算符。

您可以使用它来检查具有单个条件的多个 ID:

select *
from candidates
where xpath('/assignments/assignment/project_id/text()', assignments)::text[] @> array['6512779208625374885', '6512779208625374886'];

以上将返回在 XML 中包含两个 project_ids 的行。

如果您使用“重叠”运算符 &&，您还可以搜索包含任何元素的行:

select *
from candidates
where xpath('/assignments/assignment/project_id/text()', assignments)::text[] && array['6512779208625374885', '6512779208625374886'];

以上返回的行至少包含 XML 中的那些 project_ids 之一。

有关数组运算符的更多详细信息，请参阅 the manual

缺点是 GIN 索引比 BTree 索引更大且维护成本更高。

---

我用以下测试设置验证了这一点:

create table candidates
(
  id integer,
  assignments  xml
);

insert into candidates
select i, format('<assignments>
                    <assignment>
                        <project_id>%s</project_id>
                        <start_date>2018-02-05T14:30:06+00:00</start_date>
                        <state_id>1</state_id>
                    </assignment>
                    <assignment>
                        <project_id>%s</project_id>
                        <start_date>2017-12-01T15:30:00+00:00</start_date>
                        <state_id>0</state_id>
                    </assignment>
                    <assignment>
                        <project_id>%s</project_id>
                        <start_date>2017-12-15T10:00:00+00:00</start_date>
                        <state_id>1</state_id>
                    </assignment></assignments>', i, 10000000 + i, 20000000 + i)::xml
from generate_series(1,1000000) as i;

所以 candidates 表现在包含一百万行，每行有 3 个不同的 project_ids。

explain (analyze, buffers)
select *
from candidates
where xpath('/assignments/assignment/project_id/text()', assignments)::text[] @> array['10000042'];

显示以下计划:

QUERY PLAN                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test.candidates  (cost=29.25..6604.48 rows=5000 width=473) (actual time=0.032..0.032 rows=1 loops=1)                             
  Output: id, assignments                                                                                                                             
  Recheck Cond: ((xpath('/assignments/assignment/project_id/text()'::text, candidates.assignments, '{}'::text[]))::text[] @> '{10000047}'::text[])    
  Heap Blocks: exact=1                                                                                                                                
  Buffers: shared hit=5                                                                                                                               
  ->  Bitmap Index Scan on candidates_xpath_idx  (cost=0.00..28.00 rows=5000 width=0) (actual time=0.028..0.028 rows=1 loops=1)                       
        Index Cond: ((xpath('/assignments/assignment/project_id/text()'::text, candidates.assignments, '{}'::text[]))::text[] @> '{10000047}'::text[])
        Buffers: shared hit=4                                                                                                                         
Planning time: 0.162 ms                                                                                                                               
Execution time: 0.078 ms                                                                                                                              

用不到十分之一毫秒的时间搜索一百万个 XML 文档似乎还不错。