Message-ID: <4B757C59.8070500@denninger.net>
Date: Fri, 12 Feb 2010 10:05:45 -0600
From: Karl Denninger <karl@denninger.net>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: Bryce Nesbitt <bryce2@obviously.com>, pgsql-performance@postgresql.org
Subject: Re: 512,600ms query becomes 7500ms... but why? 	 Postgres
	8.3 query planner quirk?
References: <4B723042.7090406@obviously.com>
	<4B751AD5020000250002F248@gw.wicourts.gov>
In-Reply-To: <4B751AD5020000250002F248@gw.wicourts.gov>
Content-Type: multipart/mixed; boundary="------------060904090906080804070607"

This is a multi-part message in MIME format.
--------------060904090906080804070607
Content-Type: multipart/alternative;
	boundary="------------010400070708060905030102"


--------------010400070708060905030102
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

"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
>
>   

--------------010400070708060905030102
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
"Exists" can be quite slow.&nbsp; So can "not exists"<br>
<br>
See if you can re-write it using a sub-select - just replace the
"exists ...." with "(select ...) is not null"<br>
<br>
Surprisingly this often results in a MUCH better query plan under
Postgresql.&nbsp; 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.<br>
<br>
<br>
Kevin Grittner wrote:
<blockquote cite="mid:4B751AD5020000250002F248@gw.wicourts.gov"
 type="cite">
  <pre wrap="">Bryce Nesbitt <a class="moz-txt-link-rfc2396E" href="mailto:bryce2@obviously.com">&lt;bryce2@obviously.com&gt;</a> wrote:
 
  </pre>
  <blockquote type="cite">
    <pre wrap="">I've got a very slow query, which I can make faster by doing
something seemingly trivial. 
    </pre>
  </blockquote>
  <pre wrap=""><!----> 
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

  </pre>
</blockquote>
</body>
</html>

--------------010400070708060905030102--

--------------060904090906080804070607
Content-Type: text/x-vcard; charset=utf-8;
 name="karl.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="karl.vcf"

begin:vcard
fn:Karl Denninger
n:Denninger;Karl
email;internet:karl@denninger.net
x-mozilla-html:TRUE
version:2.1
end:vcard


--------------060904090906080804070607--