I am trying to setup an index on an xpath expression but the query never uses the index.
Could someone enlighten me please ?
Here is the setup :
CREATE TABLE time_series
(
id bigint NOT NULL,
"name" character varying NOT NULL,
"timestamp" timestamp with time zone NOT NULL,
start_date timestamp with time zone,
end_date timestamp with time zone,
.............
external_attributes xml,
..............
)
WITH (
OIDS=FALSE
);
CREATE INDEX xml_index
ON time_series
USING btree
((xpath('/AttributeList/Attributes/Attribute/Name/text()'::text, external_attributes)::text[]));
And here is the query :
select id, name
from
time_series
where
(xpath('/AttributeList/Attributes/Attribute/Name/text()', external_attributes))[1]::text='Attribute100'