Re: [GENERAL] Xpath Index in PostgreSQL

[Chris Roffler]

Figured it out , thanks for all your help ( had missing brackets )

Here is the sql statement

CREATE INDEX xml_index
  ON time_series
  USING btree
  ((
  (xpath('/AttributeList/Attributes/Attribute/Name/text()',
external_attributes))[1]::text));


Thanks
Chris

On Fri, Mar 5, 2010 at 4:18 PM, Chris Roffler wrote:

> 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  wrote:
>
>> Chris Roffler  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
>>
>
>

Re: [GENERAL] Xpath Index in PostgreSQL

[Chris Roffler]

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  wrote:

> Chris Roffler  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
>

Re: [GENERAL] Xpath Index in PostgreSQL

[Tom Lane]

Chris Roffler  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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general