Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3390E63285F for ; Sat, 13 Feb 2010 03:45:48 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 09463-03 for ; Sat, 13 Feb 2010 07:45:34 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from out-0.mx.aerioconnect.net (outb.internet-mail-service.net [216.240.47.225]) by mail.postgresql.org (Postfix) with ESMTP id 1B20D632843 for ; Sat, 13 Feb 2010 03:45:36 -0400 (AST) Received: from idiom.com (postfix@mx0.idiom.com [216.240.32.160]) by out-0.mx.aerioconnect.net (8.13.8/8.13.8) with ESMTP id o1D7jPvI019546; Fri, 12 Feb 2010 23:45:25 -0800 X-Client-Authorized: MaGic Cook1e X-Client-Authorized: MaGic Cook1e Received: from [192.168.2.113] (adsl-71-135-173-32.dsl.pltn13.pacbell.net [71.135.173.32]) by idiom.com (Postfix) with ESMTP id F357E2D603B; Fri, 12 Feb 2010 23:45:23 -0800 (PST) Message-ID: <4B765893.9080600@obviously.com> Date: Fri, 12 Feb 2010 23:45:23 -0800 From: Bryce Nesbitt User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.23) Gecko/20090410 SUSE/1.1.18-0.1 SeaMonkey/1.1.18 MIME-Version: 1.0 To: pgsql-performance@postgresql.org CC: Kevin Grittner 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="------------000502090409040901060203" X-Scanned-By: MIMEDefang 2.67 on 216.240.47.51 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.159 tagged_above=-10 required=5 tests=AWL=-0.050, BAYES_05=-1.11, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/218 X-Sequence-Number: 37639 This is a multi-part message in MIME format. --------------000502090409040901060203 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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
;
512,600ms query becomes 225,976ms.  Twice as fast on pos
Definitely not beating the 7500ms version.
PostgreSQL 8.3.4




--------------000502090409040901060203 Content-Type: text/plain; name="Grittner_qplan.txt" Content-Transfer-Encoding: base64 Content-Disposition: inline; filename="Grittner_qplan.txt" RVhQTEFJTiBBTkFMWVpFClNFTEVDVCBjb250ZXh0cy5jb250ZXh0X2tleQogIEZST00gY29u dGV4dHMKICBKT0lOIGFydGljbGVzIE9OIChhcnRpY2xlcy5jb250ZXh0X2tleSA9IGNvbnRl eHRzLmNvbnRleHRfa2V5KQogIEpPSU4gbWF0dmlld184MjAzNCBPTiAobWF0dmlld184MjAz NC5jb250ZXh0X2tleSA9CiAgICAgICAgICAgICAgICAgICAgICAgICBjb250ZXh0cy5jb250 ZXh0X2tleSkKICBXSEVSRSBFWElTVFMKICAgICAgICAoCiAgICAgICAgICBTRUxFQ1QgKgog ICAgICAgICAgICBGUk9NIGFydGljbGVfd29yZHMKICAgICAgICAgICAgSk9JTiB3b3JkcyB1 c2luZyAod29yZF9rZXkpCiAgICAgICAgICAgIFdIRVJFIGNvbnRleHRfa2V5ID0gY29udGV4 dHMuY29udGV4dF9rZXkKICAgICAgICAgICAgICBBTkQgd29yZCA9ICdpbnNpZGVyJwogICAg ICAgICkKICAgIEFORCBFWElTVFMKICAgICAgICAoCiAgICAgICAgICBTRUxFQ1QgKgogICAg ICAgICAgICBGUk9NIGFydGljbGVfd29yZHMKICAgICAgICAgICAgSk9JTiB3b3JkcyB1c2lu ZyAod29yZF9rZXkpCiAgICAgICAgICAgIFdIRVJFIGNvbnRleHRfa2V5ID0gY29udGV4dHMu Y29udGV4dF9rZXkKICAgICAgICAgICAgICBBTkQgd29yZCA9ICd0cmFkaW5nJwogICAgICAg ICkKICAgIEFORCBFWElTVFMKICAgICAgICAoCiAgICAgICAgICBTRUxFQ1QgKgogICAgICAg ICAgICBGUk9NIHZpcnR1YWxfYW5jZXN0b3JzIGEKICAgICAgICAgICAgSk9JTiBicF9jYXRl Z29yaWVzIE9OIChicF9jYXRlZ29yaWVzLmNvbnRleHRfa2V5ID0KICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICBhLmFuY2VzdG9yX2tleSkKICAgICAgICAgICAgV0hFUkUg YS5jb250ZXh0X2tleSA9IGNvbnRleHRzLmNvbnRleHRfa2V5CiAgICAgICAgICAgICAgQU5E IGxvd2VyKGJwX2NhdGVnb3JpZXMuY2F0ZWdvcnkpID0gJ2xhdycKICAgICAgICApCiAgICBB TkQgYXJ0aWNsZXMuaW5kZXhlZAo7CgoKCgotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KIE5lc3RlZCBMb29wICAoY29zdD0x MzUxMS45NS4uMTM2NjIwMjMuMDUgcm93cz0xOTI4MiB3aWR0aD00KSAoYWN0dWFsIHRpbWU9 MzA3MC44MzQuLjIyNTk3My4xNTkgcm93cz02MjIgbG9vcHM9MSkKICAgLT4gIEhhc2ggSm9p biAgKGNvc3Q9MTM1MTEuOTUuLjM1NjkzOC4zOCByb3dzPTE3NzkzNyB3aWR0aD04KSAoYWN0 dWFsIHRpbWU9MTEwNi4yNDIuLjc1MjAuNzU2IHJvd3M9MzUxMzM3IGxvb3BzPTEpCiAgICAg ICAgIEhhc2ggQ29uZDogKGFydGljbGVzLmNvbnRleHRfa2V5ID0gbWF0dmlld184MjAzNC5j b250ZXh0X2tleSkKICAgICAgICAgLT4gIFNlcSBTY2FuIG9uIGFydGljbGVzICAoY29zdD0w LjAwLi4zMzQ1MDIuOTAgcm93cz0zODYyNjYgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTAuMDMw Li40MDMxLjIwMyByb3dzPTM1NTYyNiBsb29wcz0xKQogICAgICAgICAgICAgICBGaWx0ZXI6 IGluZGV4ZWQKICAgICAgICAgLT4gIEhhc2ggIChjb3N0PTYzMjIuMjAuLjYzMjIuMjAgcm93 cz00MzgyMjAgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTExMDUuNjYzLi4xMTA1LjY2MyByb3dz PTQzODIyMCBsb29wcz0xKQogICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4gb24gbWF0dmll d184MjAzNCAgKGNvc3Q9MC4wMC4uNjMyMi4yMCByb3dzPTQzODIyMCB3aWR0aD00KSAoYWN0 dWFsIHRpbWU9Ny4xMDUuLjU0NC4wNzIgcm93cz00MzgyMjAgbG9vcHM9MSkKICAgLT4gIElu ZGV4IFNjYW4gdXNpbmcgY29udGV4dHNfcGtleSBvbiBjb250ZXh0cyAgKGNvc3Q9MC4wMC4u NzQuNzYgcm93cz0xIHdpZHRoPTQpIChhY3R1YWwgdGltZT0wLjYxOS4uMC42MTkgcm93cz0w IGxvb3BzPTM1MTMzNykKICAgICAgICAgSW5kZXggQ29uZDogKGNvbnRleHRzLmNvbnRleHRf a2V5ID0gYXJ0aWNsZXMuY29udGV4dF9rZXkpCiAgICAgICAgIEZpbHRlcjogKChzdWJwbGFu KSBBTkQgKHN1YnBsYW4pIEFORCAoc3VicGxhbikpCiAgICAgICAgIFN1YlBsYW4KICAgICAg ICAgICAtPiAgTmVzdGVkIExvb3AgIChjb3N0PTAuMDAuLjMwLjU0IHJvd3M9MSB3aWR0aD0x NykgKGFjdHVhbCB0aW1lPTYuMTE5Li42LjExOSByb3dzPTEgbG9vcHM9OTgzKQogICAgICAg ICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIHdvcmRzX3dvcmQgb24gd29yZHMgIChj b3N0PTAuMDAuLjUuNTAgcm93cz0xIHdpZHRoPTEzKSAoYWN0dWFsIHRpbWU9MC4wMjguLjAu MDI5IHJvd3M9MSBsb29wcz05ODMpCiAgICAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29u ZDogKCh3b3JkKTo6dGV4dCA9ICd0cmFkaW5nJzo6dGV4dCkKICAgICAgICAgICAgICAgICAt PiAgSW5kZXggU2NhbiB1c2luZyBhcnRpY2xlX3dvcmRzX2N3IG9uIGFydGljbGVfd29yZHMg IChjb3N0PTAuMDAuLjI1LjAyIHJvd3M9MSB3aWR0aD04KSAoYWN0dWFsIHRpbWU9Ni4wODIu LjYuMDgyIHJvd3M9MSBsb29wcz05ODMpCiAgICAgICAgICAgICAgICAgICAgICAgSW5kZXgg Q29uZDogKChwdWJsaWMuYXJ0aWNsZV93b3Jkcy5jb250ZXh0X2tleSA9ICQwKSBBTkQgKHB1 YmxpYy5hcnRpY2xlX3dvcmRzLndvcmRfa2V5ID0gcHVibGljLndvcmRzLndvcmRfa2V5KSkK ICAgICAgICAgICAtPiAgTmVzdGVkIExvb3AgIChjb3N0PTAuMDAuLjMwLjU0IHJvd3M9MSB3 aWR0aD0xNykgKGFjdHVhbCB0aW1lPTYuMTk2Li42LjE5NiByb3dzPTAgbG9vcHM9MjY0OTQp CiAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgd29yZHNfd29yZCBvbiB3 b3JkcyAgKGNvc3Q9MC4wMC4uNS41MCByb3dzPTEgd2lkdGg9MTMpIChhY3R1YWwgdGltZT0w LjAyMi4uMC4wMjQgcm93cz0xIGxvb3BzPTI2NDk0KQogICAgICAgICAgICAgICAgICAgICAg IEluZGV4IENvbmQ6ICgod29yZCk6OnRleHQgPSAnaW5zaWRlcic6OnRleHQpCiAgICAgICAg ICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgYXJ0aWNsZV93b3Jkc19jdyBvbiBhcnRp Y2xlX3dvcmRzICAoY29zdD0wLjAwLi4yNS4wMiByb3dzPTEgd2lkdGg9OCkgKGFjdHVhbCB0 aW1lPTYuMTY1Li42LjE2NSByb3dzPTAgbG9vcHM9MjY0OTQpCiAgICAgICAgICAgICAgICAg ICAgICAgSW5kZXggQ29uZDogKChwdWJsaWMuYXJ0aWNsZV93b3Jkcy5jb250ZXh0X2tleSA9 ICQwKSBBTkQgKHB1YmxpYy5hcnRpY2xlX3dvcmRzLndvcmRfa2V5ID0gcHVibGljLndvcmRz LndvcmRfa2V5KSkKICAgICAgICAgICAtPiAgTmVzdGVkIExvb3AgIChjb3N0PTAuMDAuLjM4 LjM4IHJvd3M9MyB3aWR0aD0yOSkgKGFjdHVhbCB0aW1lPTAuMTIyLi4wLjEyMiByb3dzPTAg bG9vcHM9MzUxMzM3KQogICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIHZp cnR1YWxfY29udGV4dF9rZXlfaWR4IG9uIHZpcnR1YWxfYW5jZXN0b3JzIGEgIChjb3N0PTAu MDAuLjcuMzUgcm93cz01IHdpZHRoPTEwKSAoYWN0dWFsIHRpbWU9MC4wNzQuLjAuMDg1IHJv d3M9NSBsb29wcz0zNTEzMzcpCiAgICAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29uZDog KGNvbnRleHRfa2V5ID0gJDApCiAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNp bmcgYnBfY2F0ZWdvcmllc19jb250ZXh0X2tleV9pZHggb24gYnBfY2F0ZWdvcmllcyAgKGNv c3Q9MC4wMC4uNi4xOSByb3dzPTEgd2lkdGg9MTkpIChhY3R1YWwgdGltZT0wLjAwNC4uMC4w MDQgcm93cz0wIGxvb3BzPTE3NDAwNTApCiAgICAgICAgICAgICAgICAgICAgICAgSW5kZXgg Q29uZDogKGJwX2NhdGVnb3JpZXMuY29udGV4dF9rZXkgPSBhLmFuY2VzdG9yX2tleSkKICAg ICAgICAgICAgICAgICAgICAgICBGaWx0ZXI6IChsb3dlcihicF9jYXRlZ29yaWVzLmNhdGVn b3J5KSA9ICdsYXcnOjp0ZXh0KQogVG90YWwgcnVudGltZTogMjI1OTc2LjA0NiBtcwooMjgg cm93cykKCgojIHNlbGVjdCAqIGZyb20gdmVyc2lvbigpOwogUG9zdGdyZVNRTCA4LjMuNCBv biB4ODZfNjQtcGMtbGludXgtZ251LCBjb21waWxlZCBieSBHQ0MgY2MgKEdDQykgNC4xLjIg MjAwNjExMTUgKHByZXJlbGVhc2UpIChEZWJpYW4gNC4xLjEtMjEpCigxIHJvdykKCg== --------------000502090409040901060203--