Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wkp0p-0005RW-NF for pgsql-performance@arkaria.postgresql.org; Thu, 15 May 2014 06:19:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Wkp0o-0002B8-Lx for pgsql-performance@arkaria.postgresql.org; Thu, 15 May 2014 06:19:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wkp0m-0002AV-Hx for pgsql-performance@postgresql.org; Thu, 15 May 2014 06:19:24 +0000 Received: from bay0-omc3-s15.bay0.hotmail.com ([65.54.190.153]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wkp0d-0007uH-VY for pgsql-performance@postgresql.org; Thu, 15 May 2014 06:19:23 +0000 Received: from BAY174-W37 ([65.54.190.187]) by bay0-omc3-s15.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Wed, 14 May 2014 23:19:13 -0700 X-TMN: [YXpXIoCemGFXOkLhT/+LrIsXgTjTBuPB] X-Originating-Email: [chang-chao@hotmail.com] Message-ID: From: changchao To: David G Johnston , "pgsql-performance@postgresql.org" Subject: Re: how do functions affect query plan? Date: Thu, 15 May 2014 15:19:13 +0900 Importance: Normal In-Reply-To: <1400132604367-5803996.post@n5.nabble.com> References: , <1400132604367-5803996.post@n5.nabble.com> Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 MIME-Version: 1.0 X-OriginalArrivalTime: 15 May 2014 06:19:13.0711 (UTC) FILETIME=[979203F0:01CF7005] X-Pg-Spam-Score: 0.9 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org SGksRGF2aWQKClNlZW1zIHRoYXQgdGhlIHJvb3Qgb2YgZXZpbCBpcyBpbiB0 aGUgZnVuY3Rpb24ocmFuZG9tLHRydW5jKSwKYWx0aG91Z2ggSSBkb24ndCBr bm93IHdoeS4KCkhlcmUgaXMgdGhlIGNvbXBhcmlzb24uCgoxLncvbyBmdW5j dGlvbiA6IGluZGV4IGlzIHdpc2VseSB1c2VkLihFdmVuIHdpdGhvdXQgdGhl IGxpbWl0IDMwIGNsYXVzZSkKCmV4cGxhaW4gYW5hbHl6ZQpTRUxFQ1QgbWQu KgrCoCBGUk9NIG1lYXN1cmVfZGF0YSBtZArCoCB3aGVyZSB0ZWxlZ3JhbV9p ZCBpbiAKwqDCoMKgICgKwqDCoMKgwqDCoMKgwqDCoCBTRUxFQ1TCoCA2NjQ4 NCArICgxMzIzNjMtNjY0ODQpLzMwICogaSAKwqDCoMKgwqDCoMKgwqDCoCBG Uk9NIGdlbmVyYXRlX3NlcmllcygxLDMwKSBhcyBzKGkpCsKgwqDCoMKgwqDC oMKgwqAgbGltaXQgMzAKwqDCoMKgICkKwqAgOwoKIk5lc3RlZCBMb29wwqAg KGNvc3Q9MTAuMDEuLjM5MjkwLjc5IHJvd3M9MTAzOTIgd2lkdGg9MjgpIChh Y3R1YWwgdGltZT0wLjA3OS4uMy40OTAgcm93cz05MzYwIGxvb3BzPTEpIgoi wqAgLT7CoCBIYXNoQWdncmVnYXRlwqAgKGNvc3Q9MC44My4uMS4xMyByb3dz PTMwIHdpZHRoPTQpIChhY3R1YWwgdGltZT0wLjAyNy4uMC4wMzIgcm93cz0z MCBsb29wcz0xKSIKIsKgwqDCoMKgwqDCoMKgIC0+wqAgTGltaXTCoCAoY29z dD0wLjAwLi4wLjQ1IHJvd3M9MzAgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTAu MDEzLi4wLjAyMCByb3dzPTMwIGxvb3BzPTEpIgoiwqDCoMKgwqDCoMKgwqDC oMKgwqDCoMKgwqAgLT7CoCBGdW5jdGlvbiBTY2FuIG9uIGdlbmVyYXRlX3Nl cmllcyBzwqAgKGNvc3Q9MC4wMC4uMTUuMDAgcm93cz0xMDAwIHdpZHRoPTQp IChhY3R1YWwgdGltZT0wLjAxMS4uMC4wMTYgcm93cz0zMCBsb29wcz0xKSIK IsKgIC0+wqAgQml0bWFwIEhlYXAgU2NhbiBvbiBtZWFzdXJlX2RhdGEgbWTC oCAoY29zdD05LjE5Li4xMzA2LjIwIHJvd3M9MzQ2IHdpZHRoPTI4KSAoYWN0 dWFsIHRpbWU9MC4wMzAuLjAuMDc1IHJvd3M9MzEyIGxvb3BzPTMwKSIKIsKg wqDCoMKgwqDCoMKgIFJlY2hlY2sgQ29uZDogKHRlbGVncmFtX2lkID0gKCg2 NjQ4NCArICgyMTk1ICogcy5pKSkpKSIKIsKgwqDCoMKgwqDCoMKgIC0+wqAg Qml0bWFwIEluZGV4IFNjYW4gb24gaW5kZXhfbWVhc3VyZV9kYXRhX3RlbGVn cmFtX2lkwqAgKGNvc3Q9MC4wMC4uOS4xMCByb3dzPTM0NiB3aWR0aD0wKSAo YWN0dWFsIHRpbWU9MC4wMjUuLjAuMDI1IHJvd3M9MzEyIGxvb3BzPTMwKSIK IsKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgIEluZGV4IENvbmQ6ICh0ZWxl Z3JhbV9pZCA9ICgoNjY0ODQgKyAoMjE5NSAqIHMuaSkpKSkiCiJUb3RhbCBy dW50aW1lOiAzLjcxNCBtcyIKCgoyLndoZW4gZnVuY3Rpb24gaXMgdGhlcmU6 IHNlcSBzY2FuCgpleHBsYWluIGFuYWx5emUKU0VMRUNUIG1kLioKwqAgRlJP TSBtZWFzdXJlX2RhdGEgbWQKwqAgd2hlcmUgdGVsZWdyYW1faWQgaW4gCsKg wqDCoCAoCsKgwqDCoMKgwqDCoMKgwqAgU0VMRUNUwqAgdHJ1bmMoKDEzMjM2 My02NjQ4NCkgKiByYW5kb20oKSkgKzY2NDg0CsKgwqDCoMKgwqDCoMKgwqAg RlJPTSBnZW5lcmF0ZV9zZXJpZXMoMSwzMCkgYXMgcyhpKQrCoMKgwqDCoMKg wqDCoMKgIGxpbWl0IDMwCsKgwqDCoCApCsKgOwoKCiJIYXNoIEpvaW7CoCAo Y29zdD0xLjY1Li40OTAyODguODkgcm93cz0xMDI3NzI4MCB3aWR0aD0yOCkg KGFjdHVhbCB0aW1lPTAuMTY5Li40ODk0Ljg0NyByb3dzPTkzNjAgbG9vcHM9 MSkiCiLCoCBIYXNoIENvbmQ6ICgobWQudGVsZWdyYW1faWQpOjpkb3VibGUg cHJlY2lzaW9uID0gKCh0cnVuYygoNjU4Nzk6OmRvdWJsZSBwcmVjaXNpb24g KiByYW5kb20oKSkpICsgNjY0ODQ6OmRvdWJsZSBwcmVjaXNpb24pKSkiCiLC oCAtPsKgIFNlcSBTY2FuIG9uIG1lYXN1cmVfZGF0YSBtZMKgIChjb3N0PTAu MDAuLjM1NjY4Mi42MCByb3dzPTIwNTU0NTYwIHdpZHRoPTI4KSAoYWN0dWFs IHRpbWU9MC4wMTAuLjIwNzYuOTMyIHJvd3M9MjA1NTQ1NjAgbG9vcHM9MSki CiLCoCAtPsKgIEhhc2jCoCAoY29zdD0xLjI4Li4xLjI4IHJvd3M9MzAgd2lk dGg9OCkgKGFjdHVhbCB0aW1lPTAuMDQxLi4wLjA0MSByb3dzPTMwIGxvb3Bz PTEpIgoiwqDCoMKgwqDCoMKgwqAgQnVja2V0czogMTAyNMKgIEJhdGNoZXM6 IDHCoCBNZW1vcnkgVXNhZ2U6IDJrQiIKIsKgwqDCoMKgwqDCoMKgIC0+wqAg SGFzaEFnZ3JlZ2F0ZcKgIChjb3N0PTAuOTguLjEuMjggcm93cz0zMCB3aWR0 aD04KSAoYWN0dWFsIHRpbWU9MC4wMzQuLjAuMDM2IHJvd3M9MzAgbG9vcHM9 MSkiCiLCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoCAtPsKgIExpbWl0wqAg KGNvc3Q9MC4wMC4uMC42MCByb3dzPTMwIHdpZHRoPTApIChhY3R1YWwgdGlt ZT0wLjAxNi4uMC4wMjYgcm93cz0zMCBsb29wcz0xKSIKIsKgwqDCoMKgwqDC oMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgIC0+wqAgRnVuY3Rpb24gU2Nh biBvbiBnZW5lcmF0ZV9zZXJpZXMgc8KgIChjb3N0PTAuMDAuLjIwLjAwIHJv d3M9MTAwMCB3aWR0aD0wKSAoYWN0dWFsIHRpbWU9MC4wMTUuLjAuMDIzIHJv d3M9MzAgbG9vcHM9MSkiCiJUb3RhbCBydW50aW1lOiA0ODk1LjIzOSBtcyIK CgotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCj4g RGF0ZTogV2VkLCAxNCBNYXkgMjAxNCAyMjo0MzoyNCAtMDcwMAo+IEZyb206 IGRhdmlkLmcuam9obnN0b25AZ21haWwuY29tCj4gVG86IHBnc3FsLXBlcmZv cm1hbmNlQHBvc3RncmVzcWwub3JnCj4gU3ViamVjdDogUmU6IFtQRVJGT1JN XSBob3cgZG8gZnVuY3Rpb25zIGFmZmVjdCBxdWVyeSBwbGFuPwo+Cj4g5bi4 6LaFIHdyb3RlCj4+IEhpLGFsbAo+PiBJIGhhdmUgYSB0YWJsZSB0byBzYXZl IHJlY2VpdmVkIG1lYXN1cmUgZGF0YS4KPj4KPj4KPj4gQ1JFQVRFIFRBQkxF IG1lYXN1cmVfZGF0YQo+PiAoCj4+ICAgaWQgc2VyaWFsIE5PVCBOVUxMLAo+ PiAgIHRlbGVncmFtX2lkIGludGVnZXIgTk9UIE5VTEwsCj4+ICAgbWVhc3Vy ZV90aW1lIHRpbWVzdGFtcCB3aXRob3V0IHRpbWUgem9uZSBOT1QgTlVMTCwK Pj4gICBpdGVtX2lkIGludGVnZXIgTk9UIE5VTEwsCj4+ICAgdmFsIGRvdWJs ZSBwcmVjaXNpb24sCj4+ICAgQ09OU1RSQUlOVCBtZWFzdXJlX2RhdGFfcGtl eSBQUklNQVJZIEtFWSAoaWQpCj4+ICk7Cj4+Cj4+IENSRUFURSBJTkRFWCBp bmRleF9tZWFzdXJlX2RhdGFfdGVsZWdyYW1faWQgT04gbWVhc3VyZV9kYXRh IFVTSU5HIGJ0cmVlCj4+ICh0ZWxlZ3JhbV9pZCk7Cj4+Cj4+Cj4+IGluIG15 IHNjZW5hcmlvLGEgdGVsZWdyYW0gY29udGFpbnMgbWVhc3VyZSBkYXRhIGZv ciBtdWx0aXBsZSBkYXRhIGl0ZW1zCj4+IGFuZCB0aW1lc3RhbXBzLAo+PiBC VFcsYW5vdGhlciB0YWJsZSBpcyBmb3IgdGVsZWdyYW0uCj4+Cj4+IFRoZSBT UUwgSSB1c2VkIGluIG15IGFwcGxpY2F0aW9uIGlzCj4+ICAgc2VsZWN0ICog ZnJvbSBtZWFzdXJlX2RhdGEgd2hlcmUgdGVsZWdyYW1faWQgaW4oMSwyLC4u LixuKQo+PiBhbmQgdGhpcyBxdWVyeSB1c2VkIHRoZSBpbmRleF9tZWFzdXJl X2RhdGFfdGVsZWdyYW1faWQgaW5kZXgsYXMgZXhwZWN0ZWQuCj4+Cj4+IElu IG9yZGVyIHRvIHNlZSB0aGUgcGVyZm9ybWFuY2Ugb2YgbXkgcXVlcnkgLAo+ PiBJIHVzZWQgdGhlIGZvbGxvd2luZyBxdWVyeSB0byBzZWFyY2ggdGhlIG1l YXN1cmUgZGF0YSBmb3IgcmFuZG9tbHkgMzAKPj4gdGVsZWdyYW1zLgo+Pgo+ Pgo+PiBleHBsYWluIGFuYWx5emUKPj4gU0VMRUNUIG1kLioKPj4gICBGUk9N IG1lYXN1cmVfZGF0YSBtZAo+PiAgIHdoZXJlIHRlbGVncmFtX2lkIGluCj4+ ICAgICAoCj4+ICAgICAgICAgIFNFTEVDVCBkaXN0aW5jdAo+PiAgICAgICAg ICB0cnVuYygoMTMyMzYzLTY2NDg0KSAqIHJhbmRvbSgpICsgNjY0ODQpCj4+ ICAgICAgICAgIEZST00gZ2VuZXJhdGVfc2VyaWVzKDEsMzApIGFzIHModGVs ZWdyYW1faWQpCj4+ICAgICApCj4+ICAgOwo+Pgo+PiB0aGUgMTMyMzYzIGFu ZCA2NjQ4NCBhcmUgdGhlIG1heCBhbmQgbWluIG9mIHRoZSB0ZWxlZ3JhbSBp ZCxzZXBhcmF0ZWx5Lgo+Pgo+PiBXaGF0IHN1cnByaXNlZCBtZSBpcyB0aGF0 IGluZGV4IGlzIG5vdCB1c2VkLGluc3RlYWQsYSBzZXEgc2NhbiBpcwo+PiBw ZXJmb3JtZWQgb24gbWVhc3VyZV9kYXRhLgo+PiBBbHRob3VnaCxpbnR1aXRp dmVseSxpbiB0aGlzIGNhc2UsaXQgaXMgbXVjaCB3aXNlciB0byB1c2UgdGhl IGluZGV4Lgo+PiBXb3VsZCB5b3UgcGxlYXNlIGdpdmUgc29tZSBjbHVlIHRv IHdoeSB0aGlzIGhhcHBlbmVkPwo+Pgo+PiAiSGFzaCBTZW1pIEpvaW4gIChj b3N0PTY1LjAwLi41MzkxNjkuMzIgcm93cz0xMDI3NzI4MCB3aWR0aD0yOCkg KGFjdHVhbAo+PiB0aW1lPTc2LjQ1NC4uMTcxNzcuMDU0IHJvd3M9OTM2MCBs b29wcz0xKSIKPj4gIiAgSGFzaCBDb25kOiAoKG1kLnRlbGVncmFtX2lkKTo6 ZG91YmxlIHByZWNpc2lvbiA9ICh0cnVuYygoKDY1ODc5Ojpkb3VibGUKPj4g cHJlY2lzaW9uICogcmFuZG9tKCkpICsgNjY0ODQ6OmRvdWJsZSBwcmVjaXNp b24pKSkpIgo+PiAiICAtPiAgU2VxIFNjYW4gb24gbWVhc3VyZV9kYXRhIG1k ICAoY29zdD0wLjAwLi4zNTY2ODIuNjAgcm93cz0yMDU1NDU2MAo+PiB3aWR0 aD0yOCkgKGFjdHVhbCB0aW1lPTAuMDEyLi4xMzg3NC44MDkgcm93cz0yMDU1 NDU2MCBsb29wcz0xKSIKPj4gIiAgLT4gIEhhc2ggIChjb3N0PTUyLjUwLi41 Mi41MCByb3dzPTEwMDAgd2lkdGg9OCkgKGFjdHVhbAo+PiB0aW1lPTAuMDYy Li4wLjA2MiByb3dzPTMwIGxvb3BzPTEpIgo+PiAiICAgICAgICBCdWNrZXRz OiAxMDI0ICBCYXRjaGVzOiAxICBNZW1vcnkgVXNhZ2U6IDJrQiIKPj4gIiAg ICAgICAgLT4gIEhhc2hBZ2dyZWdhdGUgIChjb3N0PTIyLjUwLi40Mi41MCBy b3dzPTEwMDAgd2lkdGg9MCkgKGFjdHVhbAo+PiB0aW1lPTAuMDQ4Li4wLjA1 MyByb3dzPTMwIGxvb3BzPTEpIgo+PiAiICAgICAgICAgICAgICAtPiAgRnVu Y3Rpb24gU2NhbiBvbiBnZW5lcmF0ZV9zZXJpZXMgcyAgKGNvc3Q9MC4wMC4u MjAuMDAKPj4gcm93cz0xMDAwIHdpZHRoPTApIChhY3R1YWwgdGltZT0wLjAy MC4uMC4wMzQgcm93cz0zMCBsb29wcz0xKSIKPj4gIlRvdGFsIHJ1bnRpbWU6 IDE3MTc3LjUyNyBtcyIKPgo+IFRoZSBwbGFubmVyIGV4cGVjdHMgdG8gbmVl ZCB0byByZXR1cm4gaGFsZiB0aGUgdGFibGUgd2hlbiB5b3UgcHJvdmlkZSAx LDAwMAo+IGRpc3RpbmN0IHRlbGVncmFtX2lkcywgd2hpY2ggaXMgYmVzdCBo YW5kbGVkIGJ5IHNjYW5uaW5nIHRoZSB3aG9sZSB0YWJsZQo+IHNlcXVlbnRp YWxseSBhbmQgdG9zc2luZyBvdXQgaW52YWxpZCBkYXRhLgo+Cj4gSSBhbSBj dXJpb3VzIGlmIHRoZSBwbGFuIHdpbGwgYmUgZGlmZmVyZW50IGlmIHlvdSBh ZGRlZCBhIExJTUlUIDMwIHRvIHRoZQo+IHN1Yi1xdWVyeS4KPgo+IFRoZSBy b290IG9mIHRoZSBwcm9ibGVtIGlzIHRoZSBwbGFubmVyIGhhcyBubyB3YXkg b2Yga25vd2luZyB3aGV0aGVyCj4gZ2VuZXJhdGVfc2VyaWVzIGlzIGdvaW5n IHRvIHJldHVybiAxIG9yIDEsMDAwLDAwMCByb3dzIHNvIGJ5IGRlZmF1bHQg aXQgKGFuZAo+IGFsbCBmdW5jdGlvbnMpIGFyZSBhc3N1bWVkIChieSB0aGUg cGxhbm5lcikgdG8gcmV0dXJuIDEsMDAwIHJvd3MuIEJ5IGFkZGluZwo+IGFu IGV4cGxpY2l0IGxpbWl0IHlvdSBjYW4gYmV0dGVyIGluZm9ybSB0aGUgcGxh bm5lciBhcyB0byBob3cgbWFueSByb3dzIHlvdQo+IGFyZSBnb2luZyB0byBi ZSBwYXNzaW5nIHVwIHRvIHRoZSBwYXJlbnQgcXVlcnkgYW5kIGl0IHdpbGwg aG9wZWZ1bGx5LCB3aXRoCj4ga25vd2xlZGdlIG9mIG9ubHkgMzAgZGlzdGlu Y3QgdmFsdWVzLCB1c2UgdGhlIGluZGV4Lgo+Cj4KPgo+Cj4gLS0KPiBWaWV3 IHRoaXMgbWVzc2FnZSBpbiBjb250ZXh0OiBodHRwOi8vcG9zdGdyZXNxbC4x MDQ1Njk4Lm41Lm5hYmJsZS5jb20vaG93LWRvLWZ1bmN0aW9ucy1hZmZlY3Qt cXVlcnktcGxhbi10cDU4MDM5OTNwNTgwMzk5Ni5odG1sCj4gU2VudCBmcm9t IHRoZSBQb3N0Z3JlU1FMIC0gcGVyZm9ybWFuY2UgbWFpbGluZyBsaXN0IGFy Y2hpdmUgYXQgTmFiYmxlLmNvbS4KPgo+Cj4gLS0KPiBTZW50IHZpYSBwZ3Nx bC1wZXJmb3JtYW5jZSBtYWlsaW5nIGxpc3QgKHBnc3FsLXBlcmZvcm1hbmNl QHBvc3RncmVzcWwub3JnKQo+IFRvIG1ha2UgY2hhbmdlcyB0byB5b3VyIHN1 YnNjcmlwdGlvbjoKPiBodHRwOi8vd3d3LnBvc3RncmVzcWwub3JnL21haWxw cmVmL3Bnc3FsLXBlcmZvcm1hbmNlCiAJCSAJICAgCQkgIAotLSAKU2VudCB2 aWEgcGdzcWwtcGVyZm9ybWFuY2UgbWFpbGluZyBsaXN0IChwZ3NxbC1wZXJm b3JtYW5jZUBwb3N0Z3Jlc3FsLm9yZykKVG8gbWFrZSBjaGFuZ2VzIHRvIHlv dXIgc3Vic2NyaXB0aW9uOgpodHRwOi8vd3d3LnBvc3RncmVzcWwub3JnL21h aWxwcmVmL3Bnc3FsLXBlcmZvcm1hbmNlCg==