PostgreSQL中如何为XML列的元素名称创建索引

问题核心说明 #

你的思路是正确的，为<book>标签下的子元素名称创建索引，确实可以大幅提升统计包含指定元素的书籍数量这类查询的效率。你遇到的报错是因为PostgreSQL不允许在索引表达式中使用unnest这类返回多行结果的集合返回函数，原有写法不符合索引的语法规则。

实现方案 #

你可以先将所有子元素名称提取为文本数组，再基于数组创建GIN索引，有两种常用实现方式：

方案1：基于自定义不可变函数建索引 #

首先创建一个不可变函数，用于提取XML中<book>的所有子元素名称并组装为数组：

CREATE OR REPLACE FUNCTION get_book_child_elements(xml_data xml)
RETURNS text[] AS $$
SELECT ARRAY(
    SELECT xpath('local-name(.)', node)::text
    FROM unnest(xpath('//book/*', xml_data)) AS node
);
$$ LANGUAGE sql IMMUTABLE;

之后基于该函数创建GIN索引：

CREATE INDEX test2_element_name_idx ON test2 USING GIN(get_book_child_elements(xml_data));

需要查询包含title元素的书籍数量时，使用数组包含操作符即可命中索引：

SELECT COUNT(*) FROM test2 WHERE get_book_child_elements(xml_data) @> ARRAY['title']::text[];

方案2：基于存储生成列建索引（PostgreSQL 12+ 支持） #

如果使用PostgreSQL 12及以上版本，可以预先将元素名称数组生成为存储列，后续查询和维护更方便：

-- 添加存储生成列
ALTER TABLE test2 ADD COLUMN book_child_elements text[] 
GENERATED ALWAYS AS (
    ARRAY(SELECT xpath('local-name(.)', node)::text FROM unnest(xpath('//book/*', xml_data)) AS node)
) STORED;
-- 在生成列上建GIN索引
CREATE INDEX test2_element_name_idx ON test2 USING GIN(book_child_elements);

查询时直接调用生成列即可：

SELECT COUNT(*) FROM test2 WHERE book_child_elements @> ARRAY['title']::text[];

内容的提问来源于stack exchange，提问作者bharathp