Re: Slow select times on select with xpath

On Wed, Sep 2, 2009 at 11:04 AM, astro77<astro_coder(at)yahoo(dot)com> wrote:
>
> I've got a table set up with an XML field that I would like to search on with
> 2.5 million records. The xml are serialized objects from my application
> which are too complex to break out into separate tables. I'm trying to run a
> query similar to this:
>
>        SELECT  serialized_object as outVal
>         from object  where
>        (
>        array_to_string(xpath('/a:root/a:Identification/b:ObjectId/text()',
> serialized_object,
>             ARRAY
>             [
>             ARRAY['a', 'http://schemas.datacontract.org/2004/07/Objects'],
>             ARRAY['b', 'http://schemas.datacontract.org/2004/07/Security']
>
>             ]), ' ') = 'fdc3da1f-060f-4c34-9c30-d9334d9272ae'
>
>        )
>        limit 1000;
>
> I've also set up an index on the xpath query like this...
>
> CREATE INDEX concurrently
> idx_object_nodeid
> ON
> object
> USING
> btree(
>
>  cast(xpath('/a:root/a:Identification/b:ObjectId/text()', serialized_object,
>             ARRAY
>             [
>             ARRAY['a', 'http://schemas.datacontract.org/2004/07/Objects'],
>             ARRAY['b', 'http://schemas.datacontract.org/2004/07/Security']
>
>             ])as text[])
> );
>
> The query takes around 30 minutes to complete with or without the index in
> place and does not cache the query. Additionally the EXPLAIN say that the
> index is not being used. I've looked everywhere but can't seem to find solid
> info on how to achieve this. Any ideas would be greatly appreciated.

Why do you have a cast in the index definition?

...Robert