代码之家 › 专栏 › 技术社区 › Khorkrak

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

aws-aurora indexing xpath postgresql xml

Khorkrak · 技术社区 · 6 年前

尝试在使用AuroraDB-postgresql9.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))

结果是:

SQL状态:42883

数据库编码是UTF8。排序规则和字符类型是en_美国.UTF-8.

还有一些insert语句示例:

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>'))

1 回复 | 直到 6 年前

Markus 6 年前

你得到这个错误是因为 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)

SET enable_seqscan TO off;