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
