[PERFORM] Slow select times on select with xpath

[astro77]

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.
-- 
View this message in context: 
http://www.nabble.com/Slow-select-times-on-select-with-xpath-tp25259351p25259351.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


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