X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 226829FB1D5 for ; Tue, 2 May 2006 19:56:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 12084-01 for ; Tue, 2 May 2006 19:55:58 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (pproxy.gmail.com [64.233.166.181]) by postgresql.org (Postfix) with ESMTP id B86CB9FAA6E for ; Tue, 2 May 2006 19:55:58 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id c31so49961pyd for ; Tue, 02 May 2006 15:55:57 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=pgpAvrPQq0v40dkSsrGjV/QRxdR6LA+m/0rXsGOg+w9bcYl1/r+JYgDQGN5bHzFpWvc1OTNdCjMMdXaNQWyaOigZq4UiV5OYT523bnHOWNO0U92JkRrqfCqWuOkjU4xKUqAkbt+iGahTeKJyjsdmm3rkKKZx1i2IUhqJspg6Hf8= Received: by 10.35.36.13 with SMTP id o13mr387508pyj; Tue, 02 May 2006 15:55:57 -0700 (PDT) Received: by 10.35.45.14 with HTTP; Tue, 2 May 2006 15:55:57 -0700 (PDT) Message-ID: Date: Tue, 2 May 2006 15:55:57 -0700 From: "Ian Burrell" To: pgsql-performance@postgresql.org Subject: Nested loop join and date range query MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/56 X-Sequence-Number: 18843 V2UgcmVjZW50bHkgdXBncmFkZWQgdG8gUG9zdGdyZVNRTCA4LjEgZnJvbSA3LjQgYW5kIGEgZmV3 IHF1ZXJpZXMgYXJlCmhhdmluZyBwZXJmb3JtYW5jZSBwcm9ibGVtcyBhbmQgcnVubmluZyBmb3Ig dmVyeSBsb25nIHRpbWVzLiAgVGhlCmNvbW1vbmFsaXR5IHNlZW1zIHRvIGJlIFBvc3RncmVTUUwg OC4xIGlzIGNob29zaW5nIHRvIHVzZSBhIG5lc3RlZApsb29wIGpvaW4gYmVjYXVzZSBpdCBlc3Rp bWF0ZXMgdGhlcmUgd2lsbCBiZSBvbmx5IGJlIGEgc2luZ2xlIHJvdy4KVGhlcmUgYXJlIHJlYWxs eSB0aG91c2FuZHMgb2Ygcm93cyBhbmQgdGhlIG5lc3RlZCBsb29wIHZlcnNpb24gdGFrZXMKbXVj aCBsb25nZXIuCgpFdmVuIHdpdGggdGhlIGJhZCBwbGFuLCB0aGUgdGVzdCBxdWVyeSBydW5zIHF1 aWNrbHkuICBUaGUgcmVhbCBxdWVyeQppcyBtdWNoIG1vcmUgY29tcGxpY2F0ZWQgYW5kIHdlIGhh dmUgaGFkIHRvIGtpbGwgaXQgYWZ0ZXIgcnVubmluZyBmb3IKMjQgaG91cnMuCgpTRUxFQ1QKICAg IE1BWCh0aXRsZXMubmFtZSkgQVMgdGl0bGVfbmFtZSwKICAgIE1BWChwcm92aWRlcnMuc2hvcnRf bmFtZSkgQVMgcHJvdmlkZXJfc2hvcnRfbmFtZSwKICAgIFNVTSh4Lnh0bnMpIEFTIHh0bnMsCiAg ICBTVU0oeC5yZXYpIEFTIHJldgpGUk9NIHh0bnNfYnlfbXNvX3RpdGxlX3drIHgKSU5ORVIgSk9J TiBwcm92aWRlcnMgcHJvdmlkZXJzIE9OIHgucHJvdmlkZXJfbm8gPSBwcm92aWRlcnMucHJvdmlk ZXJfbm8KSU5ORVIgSk9JTiB0aXRsZXMgdGl0bGVzIE9OIHgudGl0bGVfbm8gPSB0aXRsZXMudGl0 bGVfbm8KV0hFUkUgeC5tc29fbm8gPSA1MAogICAgQU5EIHgud2VlayBCRVRXRUVOICcyMDA2MDQy MyAwMDowMDowMCcgQU5EICAnMjAwNjA0MjMgMDA6MDA6MDAnCkdST1VQIEJZIHgudGl0bGVfbm8s IHgucHJvdmlkZXJfbm8KClRoZSBFWFBMQUlOIEFOQUxZWkUgbG9va3MgbGlrZToKCiBHcm91cEFn Z3JlZ2F0ZSAgKGNvc3Q9MTEuNjMuLjExLjY3IHJvd3M9MSB3aWR0aD02MSkgKGFjdHVhbAp0aW1l PTE0NDAuNTUwLi4xNDY3LjYwMiByb3dzPTM0NTkgbG9vcHM9MSkKICAgLT4gIFNvcnQgIChjb3N0 PTExLjYzLi4xMS42NCByb3dzPTEgd2lkdGg9NjEpIChhY3R1YWwKdGltZT0xNDQwLjUxNS4uMTQ0 Ni42MzQgcm93cz0zOTM0IGxvb3BzPTEpCiAgICAgICAgIFNvcnQgS2V5OiB4LnRpdGxlX25vLCB4 LnByb3ZpZGVyX25vCiAgICAgICAgIC0+ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9MC4wMC4uMTEuNjIg cm93cz0xIHdpZHRoPTYxKSAoYWN0dWFsCnRpbWU9Ny45MDAuLjE0MjIuNjg2IHJvd3M9MzkzNCBs b29wcz0xKQogICAgICAgICAgICAgICAtPiAgTmVzdGVkIExvb3AgIChjb3N0PTAuMDAuLjcuMzgg cm93cz0xIHdpZHRoPTQ5KQooYWN0dWFsIHRpbWU9Ny44NzcuLjEzNzMuMzkyIHJvd3M9MzkzNCBs b29wcz0xKQogICAgICAgICAgICAgICAgICAgICAtPiAgSW5kZXggU2NhbiB1c2luZyB1bnFfeHRu c19ieV9tc29fdGl0bGVfd2sgb24KeHRuc19ieV9tc29fdGl0bGVfd2sgeCAgKGNvc3Q9MC4wMC4u NC4xMiByb3dzPTEgd2lkdGg9MjYpIChhY3R1YWwKdGltZT03LjgyNy4uMTI5Ny42ODEgcm93cz0z OTM0IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICgod2Vl ayA+PSAnMjAwNi0wNC0yMwowMDowMDowMCc6OnRpbWVzdGFtcCB3aXRob3V0IHRpbWUgem9uZSkg QU5EICh3ZWVrIDw9ICcyMDA2LTA0LTIzCjAwOjAwOjAwJzo6dGltZXN0YW1wIHdpdGhvdXQgdGlt ZSB6b25lKSBBTkQgKG1zb19ubyA9IDUwKSkKICAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4 IFNjYW4gdXNpbmcgcGtfdGl0bGVzIG9uIHRpdGxlcyAKKGNvc3Q9MC4wMC4uMy4yNSByb3dzPTEg d2lkdGg9MjcpIChhY3R1YWwgdGltZT0wLjAxMC4uMC4wMTIgcm93cz0xCmxvb3BzPTM5MzQpCiAg ICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICgib3V0ZXIiLnRpdGxlX25vID0g dGl0bGVzLnRpdGxlX25vKQogICAgICAgICAgICAgICAtPiAgSW5kZXggU2NhbiB1c2luZyBwa19w cm92aWRlcnMgb24gcHJvdmlkZXJzIAooY29zdD0wLjAwLi40LjIzIHJvd3M9MSB3aWR0aD0xNikg KGFjdHVhbCB0aW1lPTAuMDA0Li4wLjAwNSByb3dzPTEKbG9vcHM9MzkzNCkKICAgICAgICAgICAg ICAgICAgICAgSW5kZXggQ29uZDogKCJvdXRlciIucHJvdmlkZXJfbm8gPSBwcm92aWRlcnMucHJv dmlkZXJfbm8pCgpJZiBpdCBpcyBzZWFyY2hpbmcgb3ZlciBtdWx0aXBsZSB3ZWVrcyAod2VlayBC RVRXRUVOICcyMDA2MDQxNwowMDowMDowMCcgQU5EICAnMjAwNjA0MjMgMDA6MDA6MDAnKSwgaXQg ZXN0aW1hdGVzIGJldHRlciBhbmQgdXNlcyBhCmhhc2ggam9pbi4KCiBHcm91cEFnZ3JlZ2F0ZSAg KGNvc3Q9Nzg0OC4yMC4uNzg3OC40OCByb3dzPTE1NiB3aWR0aD02MSkgKGFjdHVhbAp0aW1lPTEx Ny43NjEuLjE0NS45MTAgcm93cz0zNDU5IGxvb3BzPTEpCiAgIC0+ICBTb3J0ICAoY29zdD03ODQ4 LjIwLi43ODUyLjA4IHJvd3M9MTU1MiB3aWR0aD02MSkgKGFjdHVhbAp0aW1lPTExNy43MzUuLjEy My44MjMgcm93cz0zOTM0IGxvb3BzPTEpCiAgICAgICAgIFNvcnQgS2V5OiB4LnRpdGxlX25vLCB4 LnByb3ZpZGVyX25vCiAgICAgICAgIC0+ICBIYXNoIEpvaW4gIChjb3N0PTUuOTUuLjc3NjUuOTQg cm93cz0xNTUyIHdpZHRoPTYxKQooYWN0dWFsIHRpbWU9Ni41MzkuLjEwMi44MjUgcm93cz0zOTM0 IGxvb3BzPTEpCiAgICAgICAgICAgICAgIEhhc2ggQ29uZDogKCJvdXRlciIucHJvdmlkZXJfbm8g PSAiaW5uZXIiLnByb3ZpZGVyX25vKQogICAgICAgICAgICAgICAtPiAgTmVzdGVkIExvb3AgIChj b3N0PTAuMDAuLjc3MzYuNzEgcm93cz0xNTUyCndpZHRoPTQ5KSAoYWN0dWFsIHRpbWU9NS4xMTcu Ljg2Ljk4MCByb3dzPTM5MzQgbG9vcHM9MSkgICAgICAgICAgICAgICAKICAgICAtPiAgSW5kZXgg U2NhbiB1c2luZyBpZHhfeHRuc19ieV9tc29fdGlfd2tfd2tfbXNvX3Qgb24KeHRuc19ieV9tc29f dGl0bGVfd2sgeCAgKGNvc3Q9MC4wMC4uMjY3Ny4wNCByb3dzPTE1NTIgd2lkdGg9MjYpCihhY3R1 YWwgdGltZT01LjA4NS4uMTguMDY1IHJvd3M9MzkzNCBsb29wcz0xKQogICAgICAgICAgICAgICAg ICAgICAgICAgICBJbmRleCBDb25kOiAoKHdlZWsgPj0gJzIwMDYtMDQtMTcKMDA6MDA6MDAnOjp0 aW1lc3RhbXAgd2l0aG91dCB0aW1lIHpvbmUpIEFORCAod2VlayA8PSAnMjAwNi0wNC0yMwowMDow MDowMCc6OnRpbWVzdGFtcCB3aXRob3V0IHRpbWUgem9uZSkgQU5EIChtc29fbm8gPSA1MCkpCiAg ICAgICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIHBrX3RpdGxlcyBvbiB0aXRs ZXMgCihjb3N0PTAuMDAuLjMuMjUgcm93cz0xIHdpZHRoPTI3KSAoYWN0dWFsIHRpbWU9MC4wMDYu LjAuMDEwIHJvd3M9MQpsb29wcz0zOTM0KQogICAgICAgICAgICAgICAgICAgICAgICAgICBJbmRl eCBDb25kOiAoIm91dGVyIi50aXRsZV9ubyA9IHRpdGxlcy50aXRsZV9ubykKICAgICAgICAgICAg ICAgLT4gIEhhc2ggIChjb3N0PTUuMTYuLjUuMTYgcm93cz0zMTYgd2lkdGg9MTYpIChhY3R1YWwK dGltZT0xLjM1Ni4uMS4zNTYgcm93cz0zMjUgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICAg LT4gIFNlcSBTY2FuIG9uIHByb3ZpZGVycyAgKGNvc3Q9MC4wMC4uNS4xNgpyb3dzPTMxNiB3aWR0 aD0xNikgKGFjdHVhbCB0aW1lPTAuMDA4Li4wLjY5MSByb3dzPTMyNSBsb29wcz0xKQoKSWYgdGhl IHdlZWsgcmFuZ2UgaXMgcmVwbGFjZSBieSBhbiBlcXVhbHMgKHdlZWsgPSAnMjAwNjA0MjMKMDA6 MDA6MDAnKSwgaXQgYWxzbyB1c2VzIGEgaGFzaCBqb2luLiAgVW5mb3J1bnRhdGVseSwgdGhlIHF1 ZXJpZXMgYXJlCmF1dG9tYXRpY2FsbHkgZ2VuZXJhdGVkIGFuZCBjaGFuZ2luZyB0aGVtIHRvIHVz ZSBhbiBlcXVhbHMgY291bGQgYmUKcHJvYmxlbWF0aWMuCgogR3JvdXBBZ2dyZWdhdGUgIChjb3N0 PTc4MjguNzUuLjc4NTkuMzIgcm93cz0xNTcgd2lkdGg9NjEpIChhY3R1YWwKdGltZT05OC4zMzAu LjEyNS4zNzAgcm93cz0zNDU5IGxvb3BzPTEpCiAgIC0+ICBTb3J0ICAoY29zdD03ODI4Ljc1Li43 ODMyLjY3IHJvd3M9MTU2NyB3aWR0aD02MSkgKGFjdHVhbAp0aW1lPTk4LjMwMy4uMTA0LjA1NSBy b3dzPTM5MzQgbG9vcHM9MSkKICAgICAgICAgU29ydCBLZXk6IHgudGl0bGVfbm8sIHgucHJvdmlk ZXJfbm8KICAgICAgICAgLT4gIEhhc2ggSm9pbiAgKGNvc3Q9NS45NS4uNzc0NS42MCByb3dzPTE1 Njcgd2lkdGg9NjEpCihhY3R1YWwgdGltZT0xLjc4NS4uODMuODMwIHJvd3M9MzkzNCBsb29wcz0x KQogICAgICAgICAgICAgICBIYXNoIENvbmQ6ICgib3V0ZXIiLnByb3ZpZGVyX25vID0gImlubmVy Ii5wcm92aWRlcl9ubykKICAgICAgICAgICAgICAgLT4gIE5lc3RlZCBMb29wICAoY29zdD0wLjAw Li43NzE2LjE0IHJvd3M9MTU2Nwp3aWR0aD00OSkgKGFjdHVhbCB0aW1lPTAuMTcwLi42OC4zMzgg cm93cz0zOTM0IGxvb3BzPTEpICAgICAgICAgICAgICAgCiAgICAgLT4gIEluZGV4IFNjYW4gdXNp bmcgaWR4X3h0bnNfYnlfbXNvX3RpX3drX3drX21zb190IG9uCnh0bnNfYnlfbXNvX3RpdGxlX3dr IHggIChjb3N0PTAuMDAuLjI2MDcuNTYgcm93cz0xNTY3IHdpZHRoPTI2KQooYWN0dWFsIHRpbWU9 MC4xMzguLjExLjk5MyByb3dzPTM5MzQgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICAgICAg ICAgSW5kZXggQ29uZDogKCh3ZWVrID0gJzIwMDYtMDQtMjMKMDA6MDA6MDAnOjp0aW1lc3RhbXAg d2l0aG91dCB0aW1lIHpvbmUpIEFORCAobXNvX25vID0gNTApKQogICAgICAgICAgICAgICAgICAg ICAtPiAgSW5kZXggU2NhbiB1c2luZyBwa190aXRsZXMgb24gdGl0bGVzIAooY29zdD0wLjAwLi4z LjI1IHJvd3M9MSB3aWR0aD0yNykgKGFjdHVhbCB0aW1lPTAuMDA2Li4wLjAwOCByb3dzPTEKbG9v cHM9MzkzNCkKICAgICAgICAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29uZDogKCJvdXRlciIu dGl0bGVfbm8gPSB0aXRsZXMudGl0bGVfbm8pCiAgICAgICAgICAgICAgIC0+ICBIYXNoICAoY29z dD01LjE2Li41LjE2IHJvd3M9MzE2IHdpZHRoPTE2KSAoYWN0dWFsCnRpbWU9MS41NjUuLjEuNTY1 IHJvd3M9MzI1IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiBw cm92aWRlcnMgIChjb3N0PTAuMDAuLjUuMTYKcm93cz0zMTYgd2lkdGg9MTYpIChhY3R1YWwgdGlt ZT0wLjAwOC4uMC42Nzcgcm93cz0zMjUgbG9vcHM9MSkKCkRvZXMgYW55b25lIGhhdmUgc29tZSBz dWdnZXN0aW9ucyB0byB0cnk/IFRoZSBtb3N0IHdvcnJ5aW5nIHRoaW5nIGlzCnRoYXQgd2hlbiB0 aGUgc3RhdGlzdGljcyBhcmUgb2ZmLCBpdCBjYW4gZG8gYSAgcGF0aG9sb2dpY2FsIHF1ZXJ5LgoK IC0gSWFuCg==