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