Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id C0ABB6350E8 for ; Thu, 5 Mar 2009 15:58:15 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 30229-01 for ; Thu, 5 Mar 2009 15:58:11 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.246]) by mail.postgresql.org (Postfix) with ESMTP id 13EF6635302 for ; Thu, 5 Mar 2009 15:58:10 -0400 (AST) Received: by an-out-0708.google.com with SMTP id c3so67005ana.28 for ; Thu, 05 Mar 2009 11:58:09 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:content-type; bh=Zm71nqbxo9ahAi2ZUGDr4aq9Z9e7tTkt6X7FUH/lhPA=; b=BYNU5Cj0pXwdJSMKi8W1yRVpEJuuvlOpGIrinEZ+PApNc37Nt2PrQX/wU9PlzCYZFE 32vBHo4i3/F6SznfPUvSetRVf8o/f+ny1QVUL1pMsEMRBovwmaxfVy/PKYhNsG356T4h tjmlc8ot53WbDK0xTNdneBoAzgohJ15hoCQs8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; b=Lxy064NmDzMk33Fc3UvxenGxCqecbjtz0mvtXqZLmzA3WIxcdCJWbKKfKm7p7ewDlq B2yiSqDPT1GEz0XONbOKLyYYAs1VXm/BVqTFLJtljdd6xuErGKXLAisiQvISV+G3SOEH qsE/CNMzeqpbvA6VKVTU/83tJhCg2LV8OG/oQ= MIME-Version: 1.0 Received: by 10.100.143.14 with SMTP id q14mr1182856and.139.1236283089277; Thu, 05 Mar 2009 11:58:09 -0800 (PST) In-Reply-To: <1236275907.6979.40.camel@jd-laptop.pragmaticzealot.org> References: <2870.1236050632@sss.pgh.pa.us> <1236275907.6979.40.camel@jd-laptop.pragmaticzealot.org> Date: Thu, 5 Mar 2009 14:58:09 -0500 Message-ID: Subject: Re: Postgres 8.3, four times slower queries? From: Aaron Guyon To: pgsql-performance@postgresql.org Content-Type: multipart/mixed; boundary=001485f5480860d4c304646496c1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200903/56 X-Sequence-Number: 32978 --001485f5480860d4c304646496c1 Content-Type: multipart/alternative; boundary=001485f5480860d4ba04646496bf --001485f5480860d4ba04646496bf Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On Thu, Mar 5, 2009 at 12:58 PM, Joshua D. Drake wrote: > What happens if you do this: > > SET cpu_tuple_cost TO '0.5'; > SET cpu_index_tuple_cost TO '0.5'; > EXPLAIN ANALYZE 8.3 query.... > Right now, I'm getting very good results with the above. I'm still running additional tests but I'll keep you guys updated. I've attached the new explain analyze. --001485f5480860d4ba04646496bf Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable On Thu, Mar 5, 2009 at 12:58 PM, Joshua D. Drake <jd@commandprompt.com> wro= te:
What happens if you do this:

SET cpu_tuple_cost TO '0.5';
SET cpu_index_tuple_cost TO '0.5';
EXPLAIN ANALYZE 8.3 query....

Right now, I'm getting very good results with th= e above.=A0 I'm still running additional tests but I'll keep you gu= ys updated. I've attached the new explain analyze.
--001485f5480860d4ba04646496bf-- --001485f5480860d4c304646496c1 Content-Type: text/plain; charset=US-ASCII; name="new_tuple1.txt" Content-Disposition: attachment; filename="new_tuple1.txt" Content-Transfer-Encoding: base64 X-Attachment-Id: f_frxuz4ob1 U0VUClNFVAogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgIFFVRVJZIFBMQU4gICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAKLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCiBVbmlxdWUgIChjb3N0PTE3Mzk3LjE3Li4x NzM5Ny4xOCByb3dzPTIgd2lkdGg9MjApIChhY3R1YWwgdGltZT02MjEuODY1Li42MjEuODY1IHJv d3M9MCBsb29wcz0xKQogICAtPiAgU29ydCAgKGNvc3Q9MTczOTcuMTcuLjE3Mzk3LjE4IHJvd3M9 MiB3aWR0aD0yMCkgKGFjdHVhbCB0aW1lPTYyMS44NTcuLjYyMS44NTcgcm93cz0wIGxvb3BzPTEp CiAgICAgICAgIFNvcnQgS2V5OiB0OC5pZAogICAgICAgICBTb3J0IE1ldGhvZDogIHF1aWNrc29y dCAgTWVtb3J5OiAxN2tCCiAgICAgICAgIC0+ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9MC4wMC4uMTcz OTcuMTYgcm93cz0yIHdpZHRoPTIwKSAoYWN0dWFsIHRpbWU9NjIxLjgyNS4uNjIxLjgyNSByb3dz PTAgbG9vcHM9MSkKICAgICAgICAgICAgICAgSm9pbiBGaWx0ZXI6ICgodDIubm90X21vZGlmaWVk X3NpbmNlID49IHRvX3RpbWVzdGFtcCgnMjAwOS0wMi0yNFQxMToxOTo1Nyc6OnRleHQsICdZWVlZ L01NL0REIEhIOk1JOlNTJzo6dGV4dCkpIE9SICh0My5ub3RfbW9kaWZpZWRfc2luY2UgPj0gdG9f dGltZXN0YW1wKCcyMDA5LTAyLTI0VDExOjE5OjU3Jzo6dGV4dCwgJ1lZWVkvTU0vREQgSEg6TUk6 U1MnOjp0ZXh0KSkgT1IgKHQ2Lm5vdF9tb2RpZmllZF9zaW5jZSA+PSB0b190aW1lc3RhbXAoJzIw MDktMDItMjRUMTE6MTk6NTcnOjp0ZXh0LCAnWVlZWS9NTS9ERCBISDpNSTpTUyc6OnRleHQpKSBP UiAodDcubm90X21vZGlmaWVkX3NpbmNlID49IHRvX3RpbWVzdGFtcCgnMjAwOS0wMi0yNFQxMTox OTo1Nyc6OnRleHQsICdZWVlZL01NL0REIEhIOk1JOlNTJzo6dGV4dCkpIE9SICh0OC5ub3RfbW9k aWZpZWRfc2luY2UgPj0gdG9fdGltZXN0YW1wKCcyMDA5LTAyLTI0VDExOjE5OjU3Jzo6dGV4dCwg J1lZWVkvTU0vREQgSEg6TUk6U1MnOjp0ZXh0KSkgT1IgKHQ5Lm5vdF9tb2RpZmllZF9zaW5jZSA+ PSB0b190aW1lc3RhbXAoJzIwMDktMDItMjRUMTE6MTk6NTcnOjp0ZXh0LCAnWVlZWS9NTS9ERCBI SDpNSTpTUyc6OnRleHQpKSBPUiAodDEwLm5vdF9tb2RpZmllZF9zaW5jZSA+PSB0b190aW1lc3Rh bXAoJzIwMDktMDItMjRUMTE6MTk6NTcnOjp0ZXh0LCAnWVlZWS9NTS9ERCBISDpNSTpTUyc6OnRl eHQpKSBPUiAodDExLm5vdF9tb2RpZmllZF9zaW5jZSA+PSB0b190aW1lc3RhbXAoJzIwMDktMDIt MjRUMTE6MTk6NTcnOjp0ZXh0LCAnWVlZWS9NTS9ERCBISDpNSTpTUyc6OnRleHQpKSkKICAgICAg ICAgICAgICAgLT4gIE5lc3RlZCBMb29wICAoY29zdD0wLjAwLi4xNDYxNS44MiByb3dzPTUzNyB3 aWR0aD03NikgKGFjdHVhbCB0aW1lPTUxLjA5Mi4uNTU2LjQwNSByb3dzPTExMDQgbG9vcHM9MSkK ICAgICAgICAgICAgICAgICAgICAgLT4gIE5lc3RlZCBMb29wICAoY29zdD0wLjAwLi4xMjAwNy44 NCByb3dzPTU0MSB3aWR0aD01MikgKGFjdHVhbCB0aW1lPTQ2LjUzMC4uNTIzLjg2OSByb3dzPTEx MDQgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIE5lc3RlZCBMb29wICAo Y29zdD0wLjAwLi4yMjI5LjA2IHJvd3M9MTQ3IHdpZHRoPTQ0KSAoYWN0dWFsIHRpbWU9NDIuNjQy Li41MDEuMTY4IHJvd3M9MzMyIGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgIC0+ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9MC4wMC4uMTUzMC41NiByb3dzPTExNCB3aWR0aD00 MCkgKGFjdHVhbCB0aW1lPTM5LjQ4NS4uNDg5LjIyNCByb3dzPTMzNiBsb29wcz0xKQogICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgTmVzdGVkIExvb3AgIChjb3N0PTAu MDAuLjc1Ny4zMSByb3dzPTE3NyB3aWR0aD0yOCkgKGFjdHVhbCB0aW1lPTEwLjg3Ny4uNDczLjc3 NCByb3dzPTM3NiBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAtPiAgTmVzdGVkIExvb3AgIChjb3N0PTAuMDAuLjY5LjA3IHJvd3M9NCB3aWR0aD0y MCkgKGFjdHVhbCB0aW1lPTYuNDYzLi42LjY0MCByb3dzPTQgbG9vcHM9MSkKICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNp bmcgaWR4X2RlcGFydG1lbnRfZHUgb24gZGVwYXJ0bWVudCB0MTAgIChjb3N0PTAuMDAuLjE0Ljcy IHJvd3M9NyB3aWR0aD0xMikgKGFjdHVhbCB0aW1lPTYuMTQxLi42LjE3MyByb3dzPTcgbG9vcHM9 MSkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgSW5kZXggQ29uZDogKGNvbXBhbnlfaWQgPSAyNTA4OTMpCiAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEZpbHRlcjogKGFjdGl2ZSA8PiAw KQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAg SW5kZXggU2NhbiB1c2luZyBpZHhfcHJvamVjdF9kZXBhcnRtZW50X2lkIG9uIHByb2plY3QgdDIg IChjb3N0PTAuMDAuLjcuMjYgcm93cz0xIHdpZHRoPTE2KSAoYWN0dWFsIHRpbWU9MC4wNTEuLjAu MDUzIHJvd3M9MSBsb29wcz03KQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25kOiAodDIuZGVwYXJ0bWVudF9pZCA9IHQxMC5p ZCkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgRmlsdGVyOiAodDIuYWN0aXZlIDw+IDApCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIGlkeF9wcm9qZWN0X3R5cGVfcHJv amVjdF9pZCBvbiBwcm9qZWN0X3R5cGUgdDExICAoY29zdD0wLjAwLi4xMzMuMzcgcm93cz03NyB3 aWR0aD0xNikgKGFjdHVhbCB0aW1lPTEuMTEyLi4xMTYuMDk0IHJvd3M9OTQgbG9vcHM9NCkKICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29u ZDogKHQxMS5wcm9qZWN0X2lkID0gdDIuaWQpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgIEZpbHRlcjogKHQxMS5hY3RpdmUgPD4gMCkKICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgcHJvamVj dF9pbnZvaWNlX3BrZXkgb24gcHJvamVjdF9pbnZvaWNlIHQzICAoY29zdD0wLjAwLi4zLjg3IHJv d3M9MSB3aWR0aD0xMikgKGFjdHVhbCB0aW1lPTAuMDI0Li4wLjAyNyByb3dzPTEgbG9vcHM9Mzc2 KQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25k OiAodDMuaWQgPSB0MTEuc2xvdF9pZCkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgRmlsdGVyOiAodDMuYWN0aXZlIDw+IDApCiAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIGlkeF9pbnZvaWNlX293bmVyX3Jlc291 cmNlX2lkIG9uIGludm9pY2UgdDcgIChjb3N0PTAuMDAuLjUuNjIgcm93cz0xIHdpZHRoPTE2KSAo YWN0dWFsIHRpbWU9MC4wMTcuLjAuMDIxIHJvd3M9MSBsb29wcz0zMzYpCiAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICh0Ny5vd25lcl9yZXNvdXJjZV9p ZCA9IHQzLmlkKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBGaWx0ZXI6 ICh0Ny5hY3RpdmUgPD4gMCkKICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNj YW4gdXNpbmcgaWR4X3BheW1lbnRfaW52b2ljZV9pZCBvbiBwYXltZW50IHQ2ICAoY29zdD0wLjAw Li41Mi40NSByb3dzPTI4IHdpZHRoPTE2KSAoYWN0dWFsIHRpbWU9MC4wMjEuLjAuMDM3IHJvd3M9 MyBsb29wcz0zMzIpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6 ICh0Ni5pbnZvaWNlX2lkID0gdDcuaWQpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg IEZpbHRlcjogKHQ2LmFjdGl2ZSA8PiAwKQogICAgICAgICAgICAgICAgICAgICAtPiAgSW5kZXgg U2NhbiB1c2luZyBpZHhfcGF5bWVudF9hbW91bnRfcGF5bWVudF9pZCBvbiBwYXltZW50X2Ftb3Vu dCB0OCAgKGNvc3Q9MC4wMC4uNC4zMiByb3dzPTEgd2lkdGg9MjgpIChhY3R1YWwgdGltZT0wLjAx MS4uMC4wMTUgcm93cz0xIGxvb3BzPTExMDQpCiAgICAgICAgICAgICAgICAgICAgICAgICAgIElu ZGV4IENvbmQ6ICh0OC5wYXltZW50X2lkID0gdDYuaWQpCiAgICAgICAgICAgICAgIC0+ICBJbmRl eCBTY2FuIHVzaW5nIGFtb3VudF9wa2V5IG9uIGFtb3VudCB0OSAgKGNvc3Q9MC4wMC4uNC42NCBy b3dzPTEgd2lkdGg9MTIpIChhY3R1YWwgdGltZT0wLjAxMi4uMC4wMTYgcm93cz0xIGxvb3BzPTEx MDQpCiAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICh0OS5pZCA9IHQ4LmFtb3VudF9p ZCkKIFRvdGFsIHJ1bnRpbWU6IDYyMi4zNzcgbXMKKDM1IHJvd3MpCgo= --001485f5480860d4c304646496c1--