On Wed, Sep 2, 2009 at 11:04 AM, astro77<astro_coder@yahoo.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
