Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?

"Exists" can be quite slow. So can "not exists"

See if you can re-write it using a sub-select - just replace the "exists
...." with "(select ...) is not null"

Surprisingly this often results in a MUCH better query plan under
Postgresql. Why the planner evaluates it "better" eludes me (it
shouldn't) but the differences are often STRIKING - I've seen
factor-of-10 differences in execution performance.

Kevin Grittner wrote:
> Bryce Nesbitt <bryce2(at)obviously(dot)com> wrote:
>
>
>> I've got a very slow query, which I can make faster by doing
>> something seemingly trivial.
>>
>
> Out of curiosity, what kind of performance do you get with?:
>
> EXPLAIN ANALYZE
> SELECT contexts.context_key
> FROM contexts
> JOIN articles ON (articles.context_key = contexts.context_key)
> JOIN matview_82034 ON (matview_82034.context_key =
> contexts.context_key)
> WHERE EXISTS
> (
> SELECT *
> FROM article_words
> JOIN words using (word_key)
> WHERE context_key = contexts.context_key
> AND word = 'insider'
> )
> AND EXISTS
> (
> SELECT *
> FROM article_words
> JOIN words using (word_key)
> WHERE context_key = contexts.context_key
> AND word = 'trading'
> )
> AND EXISTS
> (
> SELECT *
> FROM virtual_ancestors a
> JOIN bp_categories ON (bp_categories.context_key =
> a.ancestor_key)
> WHERE a.context_key = contexts.context_key
> AND lower(bp_categories.category) = 'law'
> )
> AND articles.indexed
> ;
>
> (You may have to add some table aliases in the subqueries.)
>
> If you are able to make a copy on 8.4 and test the various forms,
> that would also be interesting. I suspect that the above might do
> pretty well in 8.4.
>
> -Kevin
>
>