X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 23B299DCCB2; Wed, 15 Mar 2006 13:57:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78478-09; Wed, 15 Mar 2006 13:57:08 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id 79B259DCC9F; Wed, 15 Mar 2006 13:57:05 -0400 (AST) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Wed, 15 Mar 2006 11:57:05 -0600 Message-Id: <44180105.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0 Date: Wed, 15 Mar 2006 11:56:53 -0600 From: "Kevin Grittner" To: , Subject: BETWEEN optimizer problems with single-value range Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="=__Part14314575.0__=" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.099 required=5 tests=[AWL=0.099] X-Spam-Score: 0.099 X-Spam-Level: X-Archive-Number: 200603/721 X-Sequence-Number: 81119 --=__Part14314575.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline Attached is a simplified example of a performance problem we have seen, with a workaround and a suggestion for enhancement (hence both the performance and hackers lists). Our software is allowing users to specify the start and end dates for a query. When they enter the same date for both, the optimizer makes a very bad choice. We can work around it in application code by using an equality test if both dates match. I think the planner should be able to make a better choice here. (One obvious way to fix it would be to rewrite "BETWEEN a AND b" as "= a" when a is equal to b, but it seems like there is some underlying problem which should be fixed instead (or in addition to) this. The first query uses BETWEEN with the same date for both min and max values. The second query uses an equality test for the same date. The third query uses BETWEEN with a two-day range. In all queries, there are less than 4,600 rows for the specified cotfcNo value out of over 18 million rows in the table. We tried boosting the statistics samples for the columns in the selection, which made the estimates of rows more accurate, but didn't change the choice of plans. -Kevin --=__Part14314575.0__= Content-Type: application/octet-stream; name="between-optimization-problem.txt" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="between-optimization-problem.txt" XGQgIkNhbCIKICAgICAgICAgICAgICAgICAgVGFibGUgInB1YmxpYy5DYWwiCiAgICAgIENvbHVt biAgICAgICB8ICAgICAgICAgVHlwZSAgICAgICAgICB8IE1vZGlmaWVycwotLS0tLS0tLS0tLS0t LS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tCiBjYWxTZXFObyAgICAg ICAgICB8ICJDYWxTZXFOb1QiICAgICAgICAgICB8IG5vdCBudWxsCiBjb3VudHlObyAgICAgICAg ICB8ICJDb3VudHlOb1QiICAgICAgICAgICB8IG5vdCBudWxsCiBhY3Rpdml0eVR5cGUgICAgICB8 ICJBY3Rpdml0eVR5cGVUIiAgICAgICB8IG5vdCBudWxsCiBjYWxEYXRlICAgICAgICAgICB8ICJE YXRlVCIgICAgICAgICAgICAgICB8IG5vdCBudWxsCiBjYWxSZWNUeXBlICAgICAgICB8IGNoYXJh Y3RlcigxKSAgICAgICAgICB8IG5vdCBudWxsCiBjdG9mY05vICAgICAgICAgICB8ICJDdG9mY05v VCIgICAgICAgICAgICB8IG5vdCBudWxsCiBpc0NvdXJ0VHlwZSAgICAgICB8IGJvb2xlYW4gICAg ICAgICAgICAgICB8IG5vdCBudWxsCiBpc05vdGNQcmludGVkICAgICB8IGJvb2xlYW4gICAgICAg ICAgICAgICB8IG5vdCBudWxsCiBpc1ByaW50T25EbHlDYWwgICB8IGJvb2xlYW4gICAgICAgICAg ICAgICB8IG5vdCBudWxsCiBsYXN0U3BjbE5vdGNTZXFObyB8ICJTcGNsTm90Y1NlcU5vVCIgICAg ICB8IG5vdCBudWxsCiBwcmlvcml0eSAgICAgICAgICB8ICJQcmlvcml0eVQiICAgICAgICAgICB8 IG5vdCBudWxsCiBzdGFydFRpbWUgICAgICAgICB8ICJUaW1lVCIgICAgICAgICAgICAgICB8IG5v dCBudWxsCiBjYWxEaXNwb0NvZGUgICAgICB8ICJDYWxEaXNwb0NvZGVUIiAgICAgICB8CiBjYWxU eHQgICAgICAgICAgICB8ICJUZXh0VCIgICAgICAgICAgICAgICB8CiBjYXNlTm8gICAgICAgICAg ICB8ICJDYXNlTm9UIiAgICAgICAgICAgICB8CiBjb21tZW50cyAgICAgICAgICB8IGNoYXJhY3Rl ciB2YXJ5aW5nKDkwKSB8CiBjdG9mY0FkZHJTZXFObyAgICB8IHNtYWxsaW50ICAgICAgICAgICAg ICB8CiBkaXNwb0RhdGUgICAgICAgICB8ICJEYXRlVCIgICAgICAgICAgICAgICB8CiBlbmRUaW1l ICAgICAgICAgICB8ICJUaW1lVCIgICAgICAgICAgICAgICB8CiBlc3REdXJhdGlvbiAgICAgICB8 ICJDYWxEdXJhdGlvblQiICAgICAgICB8CiBlc3RQZCAgICAgICAgICAgICB8ICJQZENvZGVUIiAg ICAgICAgICAgICB8CiBsb2MgICAgICAgICAgICAgICB8ICJMb2NUIiAgICAgICAgICAgICAgICB8 CiBtYXN0ZXJDYWxTZXFObyAgICB8ICJDYWxTZXFOb1QiICAgICAgICAgICB8CiBycHRyQ29kZSAg ICAgICAgICB8ICJDdG9mY05vVCIgICAgICAgICAgICB8CiB0YWcgICAgICAgICAgICAgICB8ICJU YWdUeXBlVCIgICAgICAgICAgICB8CiBnd01lc3NhZ2VJZCAgICAgICB8ICJHd01lc3NhZ2VJZFQi ICAgICAgICB8CkluZGV4ZXM6CiAgICAiQ2FsX3BrZXkiIFBSSU1BUlkgS0VZLCBidHJlZSAoImNv dW50eU5vIiwgImNhbFNlcU5vIikKICAgICJDYWxfQ2FsRGF0ZSIgVU5JUVVFLCBidHJlZSAoImNh bERhdGUiLCAiY291bnR5Tm8iLCAiY2FsRGlzcG9Db2RlIiwgImFjdGl2aXR5VHlwZSIsICJjYXNl Tm8iLCAiY2FsUmVjVHlwZSIsICJjYWxTZXFObyIpCiAgICAiQ2FsX0Nhc2VObyIgVU5JUVVFLCBi dHJlZSAoImNhc2VObyIsICJjb3VudHlObyIsICJjYWxTZXFObyIpCiAgICAiQ2FsX0N0b2ZjTm8i IFVOSVFVRSwgYnRyZWUgKCJjdG9mY05vIiwgImNhbERhdGUiLCAiY291bnR5Tm8iLCAic3RhcnRU aW1lIiwgcHJpb3JpdHksICJjYWxTZXFObyIpCgpleHBsYWluIGFuYWx5emUKU0VMRUNUICJDQSIu ImNhbERhdGUiLCAiQ0EiLiJzdGFydFRpbWUiCiAgRlJPTSAiQ2FsIiAiQ0EiCiAgV0hFUkUgIkNB Ii4iY3RvZmNObyIgPSAnMjE5MicKICAgIEFORCAiQ0EiLiJjYWxEYXRlIiBiZXR3ZWVuICcyMDA2 LTAzLTE1JyBBTkQgJzIwMDYtMDMtMTUnCiAgT1JERVIgQlkgImNhbERhdGUiLCAic3RhcnRUaW1l IjsKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICBRVUVSWSBQTEFOICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCiBTb3J0ICAoY29zdD00LjAzLi40 LjAzIHJvd3M9MSB3aWR0aD0xMikgKGFjdHVhbCB0aW1lPTkyODMuNTYwLi45MjgzLjU2OCByb3dz PTQgbG9vcHM9MSkKICAgU29ydCBLZXk6ICJjYWxEYXRlIiwgInN0YXJ0VGltZSIKICAgLT4gIElu ZGV4IFNjYW4gdXNpbmcgIkNhbF9DYWxEYXRlIiBvbiAiQ2FsIiAiQ0EiICAoY29zdD0wLjAwLi40 LjAyIHJvd3M9MSB3aWR0aD0xMikgKGFjdHVhbCB0aW1lPTUzMi4yNDYuLjkyODMuNDUwIHJvd3M9 NCBsb29wcz0xKQogICAgICAgICBJbmRleCBDb25kOiAoKCgiY2FsRGF0ZSIpOjpkYXRlID49ICcy MDA2LTAzLTE1Jzo6ZGF0ZSkgQU5EICgoImNhbERhdGUiKTo6ZGF0ZSA8PSAnMjAwNi0wMy0xNSc6 OmRhdGUpKQogICAgICAgICBGaWx0ZXI6ICgoImN0b2ZjTm8iKTo6YnBjaGFyID0gJzIxOTInOjpi cGNoYXIpCiBUb3RhbCBydW50aW1lOiA5MjgzLjc1OCBtcwooNiByb3dzKQoKZXhwbGFpbiBhbmFs eXplClNFTEVDVCAiQ0EiLiJjYWxEYXRlIiwgIkNBIi4ic3RhcnRUaW1lIgogIEZST00gIkNhbCIg IkNBIgogIFdIRVJFICJDQSIuImN0b2ZjTm8iID0gJzIxOTInCiAgICBBTkQgIkNBIi4iY2FsRGF0 ZSIgPSAnMjAwNi0wMy0xNScKICBPUkRFUiBCWSAiY2FsRGF0ZSIsICJzdGFydFRpbWUiOwogICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBR VUVSWSBQTEFOICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAKLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KIFNvcnQgIChjb3N0PTI0LjM2Li4yNC4zOSByb3dz PTExIHdpZHRoPTEyKSAoYWN0dWFsIHRpbWU9MC43MTUuLjAuNzIzIHJvd3M9NCBsb29wcz0xKQog ICBTb3J0IEtleTogImNhbERhdGUiLCAic3RhcnRUaW1lIgogICAtPiAgSW5kZXggU2NhbiB1c2lu ZyAiQ2FsX0N0b2ZjTm8iIG9uICJDYWwiICJDQSIgIChjb3N0PTAuMDAuLjI0LjE3IHJvd3M9MTEg d2lkdGg9MTIpIChhY3R1YWwgdGltZT0wLjQyNC4uMC42Mjggcm93cz00IGxvb3BzPTEpCiAgICAg ICAgIEluZGV4IENvbmQ6ICgoKCJjdG9mY05vIik6OmJwY2hhciA9ICcyMTkyJzo6YnBjaGFyKSBB TkQgKCgiY2FsRGF0ZSIpOjpkYXRlID0gJzIwMDYtMDMtMTUnOjpkYXRlKSkKIFRvdGFsIHJ1bnRp bWU6IDAuODI4IG1zCig1IHJvd3MpCgpleHBsYWluIGFuYWx5emUKU0VMRUNUICJDQSIuImNhbERh dGUiLCAiQ0EiLiJzdGFydFRpbWUiCiAgRlJPTSAiQ2FsIiAiQ0EiCiAgV0hFUkUgIkNBIi4iY3Rv ZmNObyIgPSAnMjE5MicKICAgIEFORCAiQ0EiLiJjYWxEYXRlIiBiZXR3ZWVuICcyMDA2LTAzLTE1 JyBBTkQgJzIwMDYtMDMtMTYnCiAgT1JERVIgQlkgImNhbERhdGUiLCAic3RhcnRUaW1lIjsKICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgUVVFUlkgUExBTiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KIFNvcnQg IChjb3N0PTQuMDMuLjQuMDMgcm93cz0xIHdpZHRoPTEyKSAoYWN0dWFsIHRpbWU9NS4zODQuLjUu NDk0IHJvd3M9NTkgbG9vcHM9MSkKICAgU29ydCBLZXk6ICJjYWxEYXRlIiwgInN0YXJ0VGltZSIK ICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgIkNhbF9DdG9mY05vIiBvbiAiQ2FsIiAiQ0EiICAoY29z dD0wLjAwLi40LjAyIHJvd3M9MSB3aWR0aD0xMikgKGFjdHVhbCB0aW1lPTAuMzc3Li41LjEzMyBy b3dzPTU5IGxvb3BzPTEpCiAgICAgICAgIEluZGV4IENvbmQ6ICgoKCJjdG9mY05vIik6OmJwY2hh ciA9ICcyMTkyJzo6YnBjaGFyKSBBTkQgKCgiY2FsRGF0ZSIpOjpkYXRlID49ICcyMDA2LTAzLTE1 Jzo6ZGF0ZSkgQU5EICgoImNhbERhdGUiKTo6ZGF0ZSA8PSAnMjAwNi0wMy0xNic6OmRhdGUpKQog VG90YWwgcnVudGltZTogNS42ODYgbXMKKDUgcm93cykKCg== --=__Part14314575.0__=--