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@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
> 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.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
