From: Karl Denninger <karl@denninger.net>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Bryce Nesbitt <bryce2@obviously.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
Date: Fri, 12 Feb 2010 10:05:45 -0600
Message-ID: <4B757C59.8070500@denninger.net> (raw)
In-Reply-To: <4B751AD5020000250002F248@gw.wicourts.gov>
References: <4B723042.7090406@obviously.com>
<4B751AD5020000250002F248@gw.wicourts.gov>
"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@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
>
>
Attachments:karl.vcf (text/x-vcard, 122B) download
begin:vcard
fn:Karl Denninger
n:Denninger;Karl
email;internet:karl@denninger.net
x-mozilla-html:TRUE
version:2.1
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: karl@denninger.net, Kevin.Grittner@wicourts.gov, bryce2@obviously.com
Subject: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
In-Reply-To: <4B757C59.8070500@denninger.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox