Re: Xpath Index in PostgreSQL

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Chris Roffler <croffler(at)earthlink(dot)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
>