Search Postgresql Archives

Re: Xpath Index in PostgreSQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 <croffler@xxxxxxxxxxxxx> 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 <tgl@xxxxxxxxxxxxx> wrote:
Chris Roffler <croffler@xxxxxxxxxxxxx> 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux