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