Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0901A6339EB for ; Wed, 14 Apr 2010 22:45:58 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 39127-08 for ; Thu, 15 Apr 2010 01:45:46 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f227.google.com (mail-gx0-f227.google.com [209.85.217.227]) by mail.postgresql.org (Postfix) with ESMTP id 664B363387C for ; Wed, 14 Apr 2010 22:45:47 -0300 (ADT) Received: by gxk27 with SMTP id 27so574408gxk.1 for ; Wed, 14 Apr 2010 18:45:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:received:message-id :subject:from:to:content-type; bh=5nXtamU0i9kXU6ge08BRavmAkQMTpWv6ZT7aXs0Am+c=; b=KwC/1zqpLunW90eQvlRAAbxO5YYNHEZz+y1Og/j41f2RET6uXhE+BEZ5bflwT4ZKSN c6y/Rlix7WeT9P+wAphY6cTo29IsRcS3VGKLZaTLF6N+lKiEgT4AQG8G7kLpknkTKwfA nAKdsCs7HmjrXlr0cu3KiUtvPU4DL70frg36g= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=rG/qo3MlytFsqnS5lOgzNRuLYc1OnKjw/NdlrtxIOYlSujyA3YjxSIMtkqDh7O9bmL UVFCrrlg/yphb3rp2yKSpdVj+A34ZT1wB8gqglbpxWDjvQOwSnOifHAojkjevUMxE+Yn akRbsU/6o8qCXOKnPWQHuols2aHbqMbJGJkn8= MIME-Version: 1.0 Received: by 10.150.189.9 with HTTP; Wed, 14 Apr 2010 18:45:45 -0700 (PDT) Date: Thu, 15 Apr 2010 11:15:45 +0930 Received: by 10.150.120.9 with SMTP id s9mr8106387ybc.299.1271295945440; Wed, 14 Apr 2010 18:45:45 -0700 (PDT) Message-ID: Subject: Good/Bad query plans based on text criteria From: JmH To: pgsql-performance@postgresql.org Content-Type: multipart/mixed; boundary=0015174783463b7f3604843ca778 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.888 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201004/180 X-Sequence-Number: 38430 --0015174783463b7f3604843ca778 Content-Type: multipart/alternative; boundary=0015174783463b7f3104843ca776 --0015174783463b7f3104843ca776 Content-Type: text/plain; charset=ISO-8859-1 Hello, I am struggling to understand why for certain criteria that i supply for a query alters the the query plan. In my "good" case i can see that an index is used, in my bad case where i only change the text value of the criteria, but not the criteria itslef (ie change/add the conditions) a hbitmap heap scan of the table is performed. Refer attached Good/Bad query plans. The basic query is: SELECT * FROM highrate_log_entry WHERE test_seq_number > 26668670 and udc = '2424' AND (test_signal_number = 'D2030' ) ORDER BY test_seq_number LIMIT 11 test_seq_number is the pk and is generated by a sequence. The D2030 is the only thing that i vary between good/bad runs. The issue is possibly related to the data spead is for the test-signal_number is not uniform, but there does not appear to be that much difference in difference between the first sequence number and the last sequence number (to achieve the 11 results), when compared between the test_seq_number that yield good or bad results. I dont believe that the issue is to do with re-writing the query, but how the planner chooses its path. I am using Postgres 8.4 on windows with default postgres.conf. I have tried changing(increasing) shared_buffers, work_mem and effective_cache_size without success. Any suggestions would be appreciated. Thanks Jason --0015174783463b7f3104843ca776 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hello,

I am struggling to understand why for certain criteria that i= supply for a query alters the the query plan. In my "good" case = i can see that an index is used, in my bad case where i only change the tex= t value of the criteria, but not the criteria itslef (ie change/add the con= ditions) a hbitmap heap scan of the table is performed.

Refer attached Good/Bad query plans.

The basic query is:

= SELECT * FROM highrate_log_entry
WHERE
test_seq_number > 26668670=
and udc =3D '2424'
AND (test_signal_number =3D 'D2030= 9;
)
ORDER BY test_seq_number LIMIT 11

test_seq_number is the pk and= is generated by a sequence.

The D2030 is the only thing that i vary= between good/bad runs. The issue is possibly related to the=A0 data spead = is for the test-signal_number is not uniform, but there does not appear to = be that much difference in difference between the first sequence number and= the last sequence number (to achieve the 11 results), when compared betwee= n the=A0 test_seq_number that yield good or bad results.

I dont believe that the issue is to do with re-writing the query, but h= ow the planner chooses its path.

I am using Postgres 8.4 on windows = with default postgres.conf. I have tried changing(increasing) shared_buffer= s, work_mem and effective_cache_size without success.

Any suggestions would be appreciated.

Thanks

Jason
--0015174783463b7f3104843ca776-- --0015174783463b7f3604843ca778 Content-Type: text/plain; charset=US-ASCII; name="GoodPlan.txt" Content-Disposition: attachment; filename="GoodPlan.txt" Content-Transfer-Encoding: base64 X-Attachment-Id: f_g80wievk0 ZXhwbGFpbiBhbmFseXplIFNFTEVDVCAqIEZST00gaGlnaHJhdGVfbG9nX2VudHJ5DQpXSEVSRSAN CnRlc3Rfc2VxX251bWJlciA+IDI2NjY4NjcwDQphbmQgdWRjID0gJzI0MjQnDQpBTkQgKHRlc3Rf c2lnbmFsX251bWJlciA9ICdEMjAzMCcNCikNCk9SREVSIEJZIHRlc3Rfc2VxX251bWJlciBMSU1J VCAxMQ0KDQpRVUVSWSBQTEFODQpMaW1pdCAgKGNvc3Q9NjY5NC4xNC4uNjY5NC4xNyByb3dzPTEx IHdpZHRoPTYxKSAoYWN0dWFsIHRpbWU9Mi44NTkuLjIuODgxIHJvd3M9MTEgbG9vcHM9MSkNCiAg LT4gIFNvcnQgIChjb3N0PTY2OTQuMTQuLjY2OTUuODIgcm93cz02NzIgd2lkdGg9NjEpIChhY3R1 YWwgdGltZT0yLjg1Ni4uMi44NjMgcm93cz0xMSBsb29wcz0xKQ0KICAgICAgICBTb3J0IEtleTog cHVibGljLmhpZ2hyYXRlX2xvZ19lbnRyeS50ZXN0X3NlcV9udW1iZXINCiAgICAgICAgU29ydCBN ZXRob2Q6ICB0b3AtTiBoZWFwc29ydCAgTWVtb3J5OiAxOGtCDQogICAgICAgIC0+ICBSZXN1bHQg IChjb3N0PTAuMDAuLjY2NzkuMTYgcm93cz02NzIgd2lkdGg9NjEpIChhY3R1YWwgdGltZT0wLjk3 My4uMi40ODQgcm93cz0yNTQgbG9vcHM9MSkNCiAgICAgICAgICAgICAgLT4gIEFwcGVuZCAgKGNv c3Q9MC4wMC4uNjY3OS4xNiByb3dzPTY3MiB3aWR0aD02MSkgKGFjdHVhbCB0aW1lPTAuOTcwLi4y LjA0NiByb3dzPTI1NCBsb29wcz0xKQ0KICAgICAgICAgICAgICAgICAgICAtPiAgSW5kZXggU2Nh biB1c2luZyBoaWdoX3JhdGVfaW5kZXggb24gaGlnaHJhdGVfbG9nX2VudHJ5ICAoY29zdD0wLjAw Li44LjI3IHJvd3M9MSB3aWR0aD0xNDUpIChhY3R1YWwgdGltZT0wLjAwOS4uMC4wMDkgcm93cz0w IGxvb3BzPTEpDQogICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICgodWRjID0g MjQyNDo6bnVtZXJpYykgQU5EICgodGVzdF9zaWduYWxfbnVtYmVyKTo6dGV4dCA9ICdEMjAzMCc6 OnRleHQpKQ0KICAgICAgICAgICAgICAgICAgICAgICAgICBGaWx0ZXI6ICh0ZXN0X3NlcV9udW1i ZXIgPiAyNjY2ODY3MDo6bnVtZXJpYykNCiAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNj YW4gdXNpbmcgaGlnaHJhdGVfbG9nX2VudHJ5XzI0MjRfMjAwOV9wa2V5IG9uIGhpZ2hyYXRlX2xv Z19lbnRyeV8yNDI0XzIwMDkgaGlnaHJhdGVfbG9nX2VudHJ5ICAoY29zdD0wLjAwLi4zNzM0LjAx IHJvd3M9MSB3aWR0aD02MCkgKGFjdHVhbCB0aW1lPTAuMDIwLi4wLjAyMCByb3dzPTAgbG9vcHM9 MSkNCiAgICAgICAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29uZDogKCh1ZGMgPSAyNDI0Ojpu dW1lcmljKSBBTkQgKHRlc3Rfc2VxX251bWJlciA+IDI2NjY4NjcwOjpudW1lcmljKSkNCiAgICAg ICAgICAgICAgICAgICAgICAgICAgRmlsdGVyOiAoKHRlc3Rfc2lnbmFsX251bWJlcik6OnRleHQg PSAnRDIwMzAnOjp0ZXh0KQ0KICAgICAgICAgICAgICAgICAgICAtPiAgSW5kZXggU2NhbiB1c2lu ZyBoaWdocmF0ZV9sb2dfZW50cnlfMjQyNF8yMDEwX3VkY19rZXkgb24gaGlnaHJhdGVfbG9nX2Vu dHJ5XzI0MjRfMjAxMCBoaWdocmF0ZV9sb2dfZW50cnkgIChjb3N0PTAuMDAuLjI5MzYuODggcm93 cz02NzAgd2lkdGg9NjEpIChhY3R1YWwgdGltZT0wLjkzNS4uMS42MjAgcm93cz0yNTQgbG9vcHM9 MSkNCiAgICAgICAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29uZDogKCh1ZGMgPSAyNDI0Ojpu dW1lcmljKSBBTkQgKCh0ZXN0X3NpZ25hbF9udW1iZXIpOjp0ZXh0ID0gJ0QyMDMwJzo6dGV4dCkp DQogICAgICAgICAgICAgICAgICAgICAgICAgIEZpbHRlcjogKHRlc3Rfc2VxX251bWJlciA+IDI2 NjY4NjcwOjpudW1lcmljKQ0KVG90YWwgcnVudGltZTogMi45NDkgbXMNCg== --0015174783463b7f3604843ca778 Content-Type: text/plain; charset=US-ASCII; name="BadPlan.txt" Content-Disposition: attachment; filename="BadPlan.txt" Content-Transfer-Encoding: base64 X-Attachment-Id: f_g80wiew71 ZXhwbGFpbiBhbmFseXplIFNFTEVDVCAqIEZST00gaGlnaHJhdGVfbG9nX2VudHJ5DQpXSEVSRSAN CnRlc3Rfc2VxX251bWJlciA+IDI2NjY4NjcwDQphbmQgdWRjID0gJzI0MjQnDQpBTkQgKHRlc3Rf c2lnbmFsX251bWJlciA9ICdEMjA0OCcNCikNCk9SREVSIEJZIHRlc3Rfc2VxX251bWJlciBMSU1J VCAxMQ0KDQpRVUVSWSBQTEFODQpMaW1pdCAgKGNvc3Q9MTA3MDE3LjkxLi4xMDcwMTcuOTQgcm93 cz0xMSB3aWR0aD02MSkgKGFjdHVhbCB0aW1lPTIxMDIuMTI5Li4yMTAyLjE1MiByb3dzPTExIGxv b3BzPTEpDQogIC0+ICBTb3J0ICAoY29zdD0xMDcwMTcuOTEuLjEwNzExMi45NiByb3dzPTM4MDIx IHdpZHRoPTYxKSAoYWN0dWFsIHRpbWU9MjEwMi4xMjYuLjIxMDIuMTMzIHJvd3M9MTEgbG9vcHM9 MSkNCiAgICAgICAgU29ydCBLZXk6IHB1YmxpYy5oaWdocmF0ZV9sb2dfZW50cnkudGVzdF9zZXFf bnVtYmVyDQogICAgICAgIFNvcnQgTWV0aG9kOiAgdG9wLU4gaGVhcHNvcnQgIE1lbW9yeTogMThr Qg0KICAgICAgICAtPiAgUmVzdWx0ICAoY29zdD0wLjAwLi4xMDYxNzAuMTUgcm93cz0zODAyMSB3 aWR0aD02MSkgKGFjdHVhbCB0aW1lPTE0MjguOTYwLi4yMDY2LjgxNSByb3dzPTI2NDk3IGxvb3Bz PTEpDQogICAgICAgICAgICAgIC0+ICBBcHBlbmQgIChjb3N0PTAuMDAuLjEwNjE3MC4xNSByb3dz PTM4MDIxIHdpZHRoPTYxKSAoYWN0dWFsIHRpbWU9MTQyOC45NTcuLjIwMjQuOTU5IHJvd3M9MjY0 OTcgbG9vcHM9MSkNCiAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgaGln aF9yYXRlX2luZGV4IG9uIGhpZ2hyYXRlX2xvZ19lbnRyeSAgKGNvc3Q9MC4wMC4uOC4yNyByb3dz PTEgd2lkdGg9MTQ1KSAoYWN0dWFsIHRpbWU9MC4wMDYuLjAuMDA2IHJvd3M9MCBsb29wcz0xKQ0K ICAgICAgICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25kOiAoKHVkYyA9IDI0MjQ6Om51bWVy aWMpIEFORCAoKHRlc3Rfc2lnbmFsX251bWJlcik6OnRleHQgPSAnRDIwNDgnOjp0ZXh0KSkNCiAg ICAgICAgICAgICAgICAgICAgICAgICAgRmlsdGVyOiAodGVzdF9zZXFfbnVtYmVyID4gMjY2Njg2 NzA6Om51bWVyaWMpDQogICAgICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIGhp Z2hyYXRlX2xvZ19lbnRyeV8yNDI0XzIwMDlfcGtleSBvbiBoaWdocmF0ZV9sb2dfZW50cnlfMjQy NF8yMDA5IGhpZ2hyYXRlX2xvZ19lbnRyeSAgKGNvc3Q9MC4wMC4uMzczNC4wMSByb3dzPTIyIHdp ZHRoPTYwKSAoYWN0dWFsIHRpbWU9MC4wMTcuLjAuMDE3IHJvd3M9MCBsb29wcz0xKQ0KICAgICAg ICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25kOiAoKHVkYyA9IDI0MjQ6Om51bWVyaWMpIEFO RCAodGVzdF9zZXFfbnVtYmVyID4gMjY2Njg2NzA6Om51bWVyaWMpKQ0KICAgICAgICAgICAgICAg ICAgICAgICAgICBGaWx0ZXI6ICgodGVzdF9zaWduYWxfbnVtYmVyKTo6dGV4dCA9ICdEMjA0OCc6 OnRleHQpDQogICAgICAgICAgICAgICAgICAgIC0+ICBCaXRtYXAgSGVhcCBTY2FuIG9uIGhpZ2hy YXRlX2xvZ19lbnRyeV8yNDI0XzIwMTAgaGlnaHJhdGVfbG9nX2VudHJ5ICAoY29zdD0yNjAwLjQy Li4xMDI0MjcuODcgcm93cz0zNzk5OCB3aWR0aD02MSkgKGFjdHVhbCB0aW1lPTE0MjguOTI4Li4x OTg3LjA1MCByb3dzPTI2NDk3IGxvb3BzPTEpDQogICAgICAgICAgICAgICAgICAgICAgICAgIFJl Y2hlY2sgQ29uZDogKCh1ZGMgPSAyNDI0OjpudW1lcmljKSBBTkQgKCh0ZXN0X3NpZ25hbF9udW1i ZXIpOjp0ZXh0ID0gJ0QyMDQ4Jzo6dGV4dCkpDQogICAgICAgICAgICAgICAgICAgICAgICAgIEZp bHRlcjogKHRlc3Rfc2VxX251bWJlciA+IDI2NjY4NjcwOjpudW1lcmljKQ0KICAgICAgICAgICAg ICAgICAgICAgICAgICAtPiAgQml0bWFwIEluZGV4IFNjYW4gb24gaGlnaHJhdGVfbG9nX2VudHJ5 XzI0MjRfMjAxMF91ZGNfa2V5ICAoY29zdD0wLjAwLi4yNTkwLjkyIHJvd3M9OTMzMTEgd2lkdGg9 MCkgKGFjdHVhbCB0aW1lPTM3MC44MjQuLjM3MC44MjQgcm93cz05MDI4MiBsb29wcz0xKQ0KICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25kOiAoKHVkYyA9IDI0MjQ6Om51 bWVyaWMpIEFORCAoKHRlc3Rfc2lnbmFsX251bWJlcik6OnRleHQgPSAnRDIwNDgnOjp0ZXh0KSkN ClRvdGFsIHJ1bnRpbWU6IDIxMDIuMjkxIG1zDQo= --0015174783463b7f3604843ca778--