Re: Slow select times on select with xpath

astro77 <astro_coder(at)yahoo(dot)com> writes:
> Kevin Grittner wrote:
>> 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.
>
> Thanks Kevin. I thought about using tsearch2 but I need to be able to select
> exact values on other numerical queries and cannot use "contains" queries.
> It's got to be fast so I cannot have lots of records returned and have to do
> secondary processing on the xml for the records which contain the exact
> value I'm looking for. This is one of the reasons I moved from using Lucene
> for searching. I hope this makes sense.

I think he meant something following this skeleton:

SELECT ...
FROM ( SELECT ...
FROM ...
WHERE /* insert preliminary filtering here */
)

WHERE /* insert xpath related filtering here */

Hopefully you have a preliminary filtering available that's restrictive
enough for the xpath filtering to only have to check few rows. Kevin
proposes that this preliminary filtering be based on Tsearch with an
adequate index (GiST for data changing a lot, GIN for pretty static
set).

As you can see the two-steps filtering can be done in a single SQL query.

Regards,
--
dim