xml - 如何使用 xpath 表达式在 PostgreSQL 中的 XML 列上创建索引？

Question

尝试在 AuroraDB - PostgreSQL 9.6 上使用 xpath 表达式的 XML 数据类型列上创建 btree 索引时遇到此错误:

ERROR:  could not identify a comparison function for type xml
SQL state: 42883

这个没有明确解决方案的 2009 线程是我发现的唯一一个讨论关于为更早版本的 PostgreSQL 创建基于 xpath 的索引的错误消息: https://www.postgresql-archive.org/Slow-select-times-on-select-with-xpath-td2074839.html

在我的例子中，我也确实需要指定命名空间，并且该线程中的原始发布者将 xpath 表达式的结果转换为 text[]，这对我来说也确实会出错 - 但为什么甚至需要这样做？即使我有数千行要处理，我也没有看到 PostgreSQL 使用我的索引。

所以我尝试了一个更简单的案例，但错误仍然存​​在 - 如果可以，请说明原因:

CREATE TABLE test
(
    id integer NOT NULL,
    xml_data xml NOT NULL,
    CONSTRAINT test_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;



CREATE INDEX test_idx
    ON test USING btree 
    (xpath('/book/title', xml_data))

结果消息是:

ERROR:  could not identify a comparison function for type xml
SQL state: 42883

数据库编码为UTF8。排序规则和字符类型为 en_US.UTF-8。

还有一些示例插入语句:

insert into source_data.test(id, xml_data) 
values(1, XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>1</chapter><chapter>2</chapter></book>'))

insert into source_data.test(id, xml_data) 
values(2, XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Apropos</title><chapter>1</chapter><chapter>2</chapter></book>'))

Answer 1

你得到这个错误是因为 XML data type不提供任何 comparison operators ，因此您不能在 xpath() 的结果上创建索引，因为它返回 an array of XML values .

因此在创建索引时需要将 XPath 表达式转换为文本数组:

CREATE INDEX test_idx
ON test USING BTREE 
    (cast(xpath('/book/title', xml_data) as text[])) ;

然后在查询表时使用该索引:

EXPLAIN ANALYZE
SELECT * FROM test where
cast(xpath('/book/title', xml_data) as text[]) = '{<title>Apropos</title>}';

给予

                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
Index Scan using test_idx on test  (cost=0.13..8.15 rows=1 width=36) (actual time=0.034..0.038 rows=1 loops=1)
    Index Cond: ((xpath('/book/title'::text, xml_data, '{}'::text[]))::text[] = '{<title>Apropos</title>}'::text[])
Planning time: 0.168 ms
Execution time: 0.073 ms (4 rows)

这在使用 text() 时是一样的:

CREATE INDEX test_idx
ON test USING BTREE 
    (cast(xpath('/book/title/text()', xml_data) as text[])) ;

explain analyze select * from test where
cast(xpath('/book/title/text()', xml_data) as text[]) = '{Apropos}';

给予

                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Index Scan using test_idx on test  (cost=0.13..8.15 rows=1 width=36) (actual time=0.034..0.038 rows=1 loops=1)
   Index Cond: ((xpath('/book/title/text()'::text, xml_data, '{}'::text[]))::text[] = '{Apropos}'::text[])
 Planning time: 0.166 ms
 Execution time: 0.076 ms
(4 rows)

请注意，我通过以下命令强制使用索引，因为我创建的测试表中只有 4 行。

SET enable_seqscan TO off;