Как создать индекс для столбца XML в PostgreSQL с помощью выражения xpath?

Question

Я сталкиваюсь с этой ошибкой при попытке создать индекс btree для столбца с типом данных XML, который использует выражение xpath в AuroraDB - PostgreSQL 9.6:

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

Этот поток 2009 без четкого разрешения является единственным, который я нашел, обсуждая это сообщение об ошибке в отношении создания индекса на основе xpath для намного более ранней версии PostgreSQL: 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 не предоставляет никаких операторов сравнения, следовательно, вы не можете создать индекс по результату xpath(), потому что он возвращает массив значений XML.

Поэтому вам нужно привести выражение 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;