Home > mailing lists

Thread: Xpath Index in PostgreSQL

Xpath Index in PostgreSQL

From

Chris Roffler

Date:

05 March 2010, 11:43:42

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 TABLE time_series

(

id bigint NOT NULL,

"name" character varying NOT NULL,

"timestamp" timestamp with time zone NOT NULL,

start_date timestamp with time zone,

end_date timestamp with time zone,

.............

external_attributes xml,

..............

)

WITH (

OIDS=FALSE

);

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'

Re: Xpath Index in PostgreSQL

From

Tom Lane

Date:

05 March 2010, 12:22:45

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

Re: Xpath Index in PostgreSQL

From

Chris Roffler

Date:

05 March 2010, 13:19:07

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

Re: Xpath Index in PostgreSQL

From

Chris Roffler

Date:

05 March 2010, 14:22:18

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@earthlink.net> 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@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