Re: Slow select times on select with xpath

I was receiving an error that an XML field does not support the various
indexes available in postgresql. Is there an example of how to do this
properly?

Robert Haas wrote:
>
> 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
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>

--
View this message in context: http://www.nabble.com/Slow-select-times-on-select-with-xpath-tp25259351p25283175.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.