我现在有一个查询：

SELECT(xpath('/document/uuid/text()',xmldata))[1]::text,(xpath('/document/title/text()',xmldata))[1]::text
FROM xmltable
WHERE(xpath('/document/uuid/text()',xmldata))[1]::text = 'some-uuid-xxxx-xxxx'

和索引：

CREATE INDEX idx_covering_index on xmltable using btree (
    ((xpath('/document/uuid/text()',xmldata))[1]::text),((xpath('/document/title/text()',xmldata))[1]::text)
)

这个索引是逻辑地查看覆盖索引,并应该启用仅索引扫描,因为所有查询的值都包含在索引(uuid和title)中

我现在知道,如果函数调用中使用的列也包含在内,那么Postgres只会识别函数索引的覆盖索引

例如.：

SELECT to_upper(column1) from table where id >10

1)不能被这个索引所涵盖：

CREATE INDEX idx_covering_index on xmltable using btree (id,to_upper(column1));

2)但可以被这个覆盖：

CREATE INDEX idx_covering_index on xmltable using btree (column1,id,to_upper(column1));

从而导致仅索引扫描.

如果我现在尝试使用我的xml设置：

CREATE INDEX idx_covering_index on xmltable using btree (xmldata,((xpath('/document/uuid/text()',xmldata))[1]::text)
)

我收到一个错误：

data type xml has no default operator class for access method “btree”

不幸的是,通常使用的“text_ops”或“text_pattern_ops”不接受“xml”作为输入 – 从而渲染我的索引 – 虽然它将覆盖所有值 – 无法支持仅索引扫描.

这可以以提供仅索引扫描的可能性来处理吗？

@ EDIT1：

我知道postgres不能使用1)中的索引作为覆盖索引,但可以使用索引,如2)

我也尝试用非常简单的表来验证这个行为,我也记得要读这个 – 但是我不能忘记我的生活.

create table test (
    id serial primary key,quote text
)



insert into test (number,quote) values ('I do not know any clever quotes');
insert into test (number,quote) values ('I am sorry');



CREATE INDEX idx_test_functional on test using btree ((regexp_replace(quote,'^I ','BillDoor ')));
set enable_seqscan = off;

analyze test;

explain select quote from test where regexp_replace(quote,'BillDoor ') = 'BillDoor do not know any clever quotes'

--> "Index Scan using idx_test_functional on test  (cost=0.13..8.15 rows=1 width=27)"

drop index idx_test_functional;
CREATE INDEX idx_test_functional on test using btree (quote,(regexp_replace(quote,'BillDoor ')));

analyze test;

explain select quote from test where regexp_replace(quote,'BillDoor ') = 'BillDoor do not know any clever quotes'

--> "Index Only Scan using idx_test_functional on test  (cost=0.13..12.17 rows=1 width=27)"

@ EDIT2：

xmltable的全表定义：

id serial primary key (clustered),xmldata xml (only data used to filter queries)
history xml (never queried or read,just kept in case of legal inquiry)
fileinfo text (seldom quieried,sometimes retrieved)
"timestamp" timestamp (mainly for legal inquiries too)

该表包含大约：500.000条记录,xmldata的大小在350到800字节之间,历史要大得多,但很少被检索,从未在过滤器中使用

为了记录,确保得到真正的结果,我总是在创建或删除索引后分析xmltable

查询的完整执行计划：

explain analyze select (xpath('/document/uuid/text()',d.xmldata))[1]::text as uuid
from xmltable as d
where
(xpath('/document/uuid/text()',d.xmldata))[1]::text = 'some-uuid-xxxx-xxxx' and (xpath('/document/genre/text()',d.xmldata))[1]::text = 'bio'

被这些印度所覆盖：

create index idx_genre on xmltable using btree (((xpath('/document/genre/text()',xmldata))[1]::text));

create index idx_uuid on xmltable using btree (((xpath('/document/uuid/text()',xmldata))[1]::text)); 

create index idx_uuid_genre on xmltable using btree (((xpath('/document/uuid/text()',((xpath('/document/genre/text()',xmldata))[1]::text));

首先导致：

"Index Scan using idx_genre on xmldata d  (cost=0.42..6303.05 rows=18154 width=32)"
"  Index Cond: (((xpath('/document/genre/text()'::text,xmldata,'{}'::text[]))[1])::text = 'bio'::text)"
"  Filter: (((xpath('/document/uuid/text()'::text,'{}'::text[]))[1])::text = 'some-uuid-xxxx-xxxx'::text)"

公平的我以为,只是为了测试我会强迫它使用 – 在我的脑海 – 覆盖索引：

drop index idx_uuid;
drop index idx_genre;

现在我得到：

"Bitmap Heap Scan on xmltable d  (cost=551.13..16025.51 rows=18216 width=32)"
"  Recheck Cond: ((((xpath('/document/genre/text()'::text,'{}'::text[]))[1])::text = 'bio'::text) AND (((xpath('/document/uuid/text()'::text,'{}'::text[]))[1])::text = 'some-uuid-xxxx-xxxx'::text))"
"  ->  Bitmap Index Scan on idx_uuid_genre  (cost=0.00..546.58 rows=18216 width=0)"
"        Index Cond: ((((xpath('/document/genre/text()'::text,'{}'::text[]))[1])::text = 'some-uuid-xxxx-xxxx'::text))"

我也尝试在索引中切换uuid和类型的位置,同样的执行计划.