Re: Slow select times on select with xpath

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-performance(at)postgresql(dot)org>, "astro77" <astro_coder(at)yahoo(dot)com>
Subject: Re: Slow select times on select with xpath
Date: 2009-09-03 15:27:50
Message-ID: 4A9F9A26020000250002AA3A@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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 would try to minimize how many XML values it had to read, parse, and
search. The best approach that comes to mind would be to use tsearch2
techniques (with a GIN or GiST index on the tsvector) to identify
which rows contain 'fdc3da1f-060f-4c34-9c30-d9334d9272ae', and use AND
to combine that with your xpath search.

-Kevin

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kenneth Cox 2009-09-03 16:13:36 Re: partition query using Seq Scan even when index is present
Previous Message Ivan Voras 2009-09-03 15:11:02 Re: Seeking performance advice and explanation for high I/O on 8.3