Re: Slow select times on select with xpath - Mailing list pgsql-performance

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 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