Xpath Index in PostgreSQL

Chris Roffler <croffler@earthlink.net> writes:

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

Doesn't work that way --- subscripting isn't an indexable operation.
To make that query fast with a standard index, you'd need the index to
be on
(xpath('/AttributeList/Attributes/Attribute/Name/text()',
external_attributes))[1]::text

regards, tom lane

Thanks for your fast response

I have the following sql statement now :

CREATE INDEX xml_index
ON time_series
USING btree

(xpath('/AttributeList/Attributes/Attribute/Name/text()',external_attributes))[1]::text

And I am getting the following error :

ERROR: syntax error at or near "["
LINE 6: ...butes/Attribute/Name/text()',external_attributes))[1]::text;

Any idea ?

Thanks
Chris

^

On Fri, Mar 5, 2010 at 3:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Chris Roffler <croffler@earthlink.net> writes:

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

Doesn't work that way --- subscripting isn't an indexable operation.
To make that query fast with a standard index, you'd need the index to
be on
(xpath('/AttributeList/Attributes/Attribute/Name/text()',
external_attributes))[1]::text

regards, tom lane