Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cVgG8-0001og-91 for pgsql-performance@arkaria.postgresql.org; Mon, 23 Jan 2017 15:10:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cVgG7-0006Gc-SS for pgsql-performance@arkaria.postgresql.org; Mon, 23 Jan 2017 15:10:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cVgG6-0006F3-Nz for pgsql-performance@postgresql.org; Mon, 23 Jan 2017 15:10:15 +0000 Received: from mail-dm3nam03on0087.outbound.protection.outlook.com ([104.47.41.87] helo=NAM03-DM3-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cVgG0-0001eG-FF for pgsql-performance@postgresql.org; Mon, 23 Jan 2017 15:10:14 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=BlueTreble.onmicrosoft.com; s=selector1-bluetreble-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=EjiBbV9LW0H+/tZcfmEMcFuqTKrbtvJAulzn3p2NpMU=; b=keB7w50MMBekt4cSJ3me+4orza57seVuF3AgTpMr8Tt/b4C40BTCQdENlHo6YqwVJGQasOxi28VAzyGKgrXom8VrjTRA+Ycnpfjslz8Hpb+LLU/PLkIhZaI/Vfe3uPw6eZ2W1glPYTnJ9qqRwjrEMEmEWaEUPtqNgm1FN0ayRVk= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.attlocal.net (2602:306:ce8b:3460:fc78:abce:71e8:6436) by BN6PR11MB1635.namprd11.prod.outlook.com (10.172.24.8) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.860.13; Mon, 23 Jan 2017 15:10:04 +0000 Subject: Re: optimizing immutable vs. stable function calls? To: "David G. Johnston" , Tom Lane References: <20170118223633.GC23081@moraine.isi.edu> <14964.1484780049@sss.pgh.pa.us> <16158.1484781806@sss.pgh.pa.us> CC: Karl Czajkowski , "pgsql-performance@postgresql.org" From: Jim Nasby Message-ID: <74793a73-20ac-0e1f-bb61-cafe6b3371cf@BlueTreble.com> Date: Mon, 23 Jan 2017 09:10:00 -0600 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0) Gecko/20100101 Thunderbird/45.6.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 8bit X-Originating-IP: [2602:306:ce8b:3460:fc78:abce:71e8:6436] X-ClientProxiedBy: DM5PR18CA0014.namprd18.prod.outlook.com (10.175.218.152) To BN6PR11MB1635.namprd11.prod.outlook.com (10.172.24.8) X-MS-Office365-Filtering-Correlation-Id: 08fa4e04-f358-4cc0-e2c5-08d443a1e948 X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:(22001);SRVR:BN6PR11MB1635; X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1635;3:NokJji0SXphrxqZiVH0CChbmqh3BH8+sbXY5u2wtPNOTU9wizRHsflPX1vh7M+fQ4+qHQbYZNDu7rgTduZyR0cPJZdHpsjjvxYURDlpj6DVTSV/6cBo7PHzlYVZdtQvmVVTI48ogUpSQRCwOfoZ/VBK270xF1sDDowBNs2P4wzCOW3LcW+RPjXhZ+4+jjG2QNOUWM/CrKLA1slpYAUhv5tiyEkKplDz8Ew2J91w/ZppGHUqW1hU4lsacM85g2feiMG1LFjT57NLmLxa0pp7aFQ== X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1635;25:P8f5OwlHjDiwRJgzxquqhp0o/lFuMSh9m1XX6LIXSa99q/U7eLEIwv+w/1SN5DG9jXjTqui5w+eX344q0uNEsz7jGEXknX8ZVILBQg6WXMyP+OeBW97QyWRV2gLMJ9STXyKntEyYtWjM+zTznYcPPwNv+Z9vOlcqgXR/DLpu2SQmMivwljJdTn7P/NVul5mpTFMZd/RAnKfpreMOAgJA59DriY6sIEXCiCLH/F2E+Kb0p6KMkjWGYj1LOC/acWe3pMAtbA70fcwjOpaXCWs1iQZvqWn8v4S+L4prGWiVQfaq/VtzmEND2W9vTEdYnVAY/sHBJV1Gf4l5JyRKbBx4h9/Z3OKaTvMgsrU5JlCk5wk0oYfdDPYUJJYGjj/jQBTH0zfwiqaSSovCkBcJYUGAuuQtuJQRXsM886VSdrMnWecwj1Ey/F850JN5C7Jok5Mn2kpEUeL/QygXfO0g5cqeYfo3UeelNPbuTXGOZaL/7966ngUnS2dLpWsv59Gtr+xPWAF5+9m4j/NGBmn+uT6SgLMi93D+PYnJbyOxTUu1Z9OkviADKjs4kdDEPHWcWRnOej34zmG6i3fJzehFpOW/rGi9Csdi9u+qAopQJ8QqO0i/6oCQ6LfVioBt5KzX6OZBWiCkO1PXIzwQmZVRZ8zeqFYvuUs0urBEe37kbbqDc+htrogXgJXz/8et8XBUFFblHt7ZNTxljbzdx3QkwabaQgq72qkbKgauTzIC0gq+V8DO4tk5Bjj65zCH4spAifNzrXSz85YqNbNXfWyiAiFdtr5XBH3yhqr+AAi185OhR0q+H5v0g8SGRHLRUV+apWjKA8t/VOZ5Wj+Bn2iq67p97A== X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1635;31:IMAvs0AvkmSZHjTtY9qccP7eM7QLiPH039nIBqykt+wpox/wwUn1S6zuqY9QS5P7TJXzzT0JW5PW/Zdf1wiqWD1L0TKmnLDi8Ei8SEeMqRfSyGZVrku+5flf+s3frPx/nTqtZ3OZ+aiaA9ny8kdAuevlyeLDH8EnFjQhbag7MAd3w719qSFH7M4k58J5Jk0eK5ZuDCs0liOrKqI0luiKdo5tgofrzmxrVRquhgllJgOZ8dNTtmuchwJHt+7FfJs+v/MTuX9fYjbggqUv1Rijw3ko2nzVJQ2aHCWeX1+CmTM=;4:CC88E8qd4FdcaJ3YsS0lreUalHfbpe5qOmGPzjlIEvBcPP8VgWViZVNwESjVIJK5IiBGRTIC1caDjkMAM3h/3Oi1hO8bAn9URMO8y/26HtibD012AGuQyghW/lctMf1/pchtJJ0xILyyQDw+PTrOJgexlp71V5pQg4jprjqZfJsvIJpSXrLiYECYFLXmrWhDhzRaKt5VrDgtaEStv6rsFk3BdVrPYX7WVCbjxExjpvbH0gY+W0Ifg45Gvqxc6xkQr1C6/Tjjsk9IhVHoUzwgvFQUnVpZ09H/uB8jEN7DxRTE/JHLA61e0/aHox6RpjEhOA4g8Qp8TwLW5O9zzlX69KuiGIIs+2M6sTDRfxrHE4ucAHwZ3bvs1/i0Woa/by39r5NV2sGCppGZB2AMX/gAB7y4W6/pZVjEHOWKUH/W+2iXvlhggdhAE2uJtjnn/U9li4b6KBoOS15FBAD8Fn7JKrl/t6atNHFTBOTy++GBX4/rRgC8STLBDHohtl1SoYe8sF3V5Vfd4idXuHq6BgEPROkDsUTJJWcVPPlyuHzcdQoX55SVuQP3kFQWw6t7VE1fUTRP+Etk1W8lkzfscL3Vuehl+0HEmt7GufP37tIqpqiFUTp1KSUFR5jVJt6QKPNj X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040375)(601004)(2401047)(5005006)(8121501046)(3002001)(10201501046)(6041248)(20161123560025)(20161123562025)(20161123555025)(20161123564025)(2016111802025)(6072148)(6043046);SRVR:BN6PR11MB1635;BCL:0;PCL:0;RULEID:;SRVR:BN6PR11MB1635; X-Forefront-PRVS: 0196A226D1 X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(7916002)(39450400003)(39410400002)(39830400002)(199003)(189002)(377454003)(24454002)(105586002)(83506001)(106356001)(53416004)(65826007)(16601075003)(36756003)(42186005)(5660300001)(65956001)(65806001)(4326007)(68736007)(47776003)(31686004)(4001350100001)(6116002)(31696002)(86362001)(5001770100001)(97736004)(6666003)(2950100002)(69596002)(189998001)(2906002)(101416001)(53936002)(76176999)(2870700001)(50986999)(7736002)(54356999)(305945005)(23676002)(92566002)(54906002)(33646002)(64126003)(25786008)(6512007)(229853002)(93886004)(38730400001)(6306002)(81156014)(8676002)(6506006)(81166006)(50466002)(6486002)(39060400001)(85282002);DIR:OUT;SFP:1101;SCL:1;SRVR:BN6PR11MB1635;H:decina.attlocal.net;FPR:;SPF:None;PTR:InfoNoRecords;A:1;MX:1;LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-Microsoft-Exchange-Diagnostics: =?utf-8?B?MTtCTjZQUjExTUIxNjM1OzIzOjJpL0tReThWVDZzVk13OUZSSUs1dEVsdW52?= =?utf-8?B?UFZRWXp4MHJhSXRtemZCSUlhU0NVcTZTcDlLeXZOWjJvQWh0RW9HSHdISFE0?= =?utf-8?B?LzRkbGdqWEhVVG55ckJzYmJyRTBDUmE1Z1I0cGx4TjEraG9GdTVKOGt6eStW?= =?utf-8?B?dXdXSVlKNXoxdWVxQ3IyMzk2T0VpSDFicEhUWTZ3VGUzZkZ6YkJ4eGNMMGNC?= =?utf-8?B?WVdodzNOK3o2US9DcmJvNGw4NnFaQjkxbEFvOGFUWFhUb1JIcXNGcUFIZ2Ir?= =?utf-8?B?Tk1hRUhOSDVGS2FHOEJkSEpmeWJ3ZjNiRmpLNjNyNVhSS3Buak16SUlYS1NG?= =?utf-8?B?NzMrdXZqVnMvOUhYMzVnc01YQ09KYWY0RGltTGdoQUo2ZG5LditsTWlNZllj?= =?utf-8?B?VW1tMElQcmVSR1l5RVgrd0ljYldGUTNZTlJBZ1lYRS9XeUpGbnVac1lnSGIx?= =?utf-8?B?VDVjVmZUczVETldwZWw4UHovYUFkWHB5YWdPcXk0L2xiclJyY28zd3l3YWYv?= =?utf-8?B?QmxrbFFEeFNKUE05emJOanhYKzhadHgrZ0xTMHo5dmZJTDRkMDFsL1NEVk1a?= =?utf-8?B?MHVQdkJBNEZ6OHQ2eXVGdjRxR0VSaTZIRUNHQm5yV2xjazU4WWErci9VMzB6?= =?utf-8?B?eTlLc2M2K1czSVYxRVZVK01YQkM5aVYvSkhiVit6VlFwNkRwejdBWWVWYThw?= =?utf-8?B?OEYwZ3dFc3lybzdRSUtQcENQMVlxak1EUmt2OFpTNkhHZVNRNmhjend0S25P?= =?utf-8?B?NGVhQmJwWG1MYkxZaCtWNGdJWXVIVzFST0s0ZDFQaTFyWFlEZ1VHZEVHMXNP?= =?utf-8?B?K1p3UmlsVzJmKzEwN1FxbXQ5VzJGT0pvN1lZbTNPMU52cFFIL3JVeW9pZDVH?= =?utf-8?B?cWc1akdOb0pRZnB1b25ZUEdFODZ3eDlhSlhQQ1RLUFZUQTBOeUFvdmUwbThL?= =?utf-8?B?ZzRlZkNWNlpyNmZ2QnA4aTVrUVRCelMwamxmT0w3NVFhR2FEQWowdThMOVhr?= =?utf-8?B?MUdKcnh4ajFkaHRYZXNpcUpQTDJQRWJicEhDb0ZJVlVPbFowVnNCWmpUNWd3?= =?utf-8?B?UU1KSGQwRFF4aGZhRk9pWDEwdVVGbndsU0ZWNVJqN0VXY21ndVVHVmZiNjlL?= =?utf-8?B?elRpc3ZtN3lyVTNKSFpPMnBlclFNZkFabUpOYU9uRE54VjJWRURmb2tvUFZh?= =?utf-8?B?L1RLM1lNUyt0aW1OcXZIeGF0ZmJITU4reUMrMlhuSVUzRUFkKzlkQ0tGc2w0?= =?utf-8?B?MFRLSVJLUXRuQTlOUmNPOHVtb1lxVW8yWGs3TjhzTDZSa2ZRU2E4UjBVK29o?= =?utf-8?B?K1pXWkRMcmlaMEhyRkZkUFNVdnJaT1Q5MmlFOWlvM1Z1dWZOV1lmRGpKRGdu?= =?utf-8?B?d1NobTZ3cE45V3JqQ1VtWXozMmF0ZUpjSnMvYTY5dGZPOFBDR0NMYSs0UGkr?= =?utf-8?B?ZDBrQnVBUkdiS3A2WHoydkMwRzdUeG5vYWdhbmR4WTJZUDYxdU5BZ0pDV3A0?= =?utf-8?B?Rzh6bUtoY2RmNktpbHdlcEZ3cVpWRGhndkx3Z29XcWtYeFBQdXpCTmx3V2pp?= =?utf-8?B?U28vUjc3N05Vd2hYaHlXbzZxczhvb093NmZLVHB0ZnBwemhsMFZ1SStmVGtl?= =?utf-8?B?NTFqTUlDNmtwNmtxeUovd2ovYlZsR20xVVY1NmdVd0V2UHJJRjZlbDZrRFZU?= =?utf-8?B?azdwVS9vd2J6dW1HOVA5MERESlMrVXEyTG9ONDBnVkhraWplbVN3SzA2ZG43?= =?utf-8?B?R25rOTd3N1ZIblhsbFoyOUgxZlZSNS83d2VITis2Y09QY2pkZUZpZ0NwZko2?= =?utf-8?B?VHFJY3RRQ0NnQ0VoNkZiUytBbW9vOUZIcEtLRkNXUTRuVnpTRzVwaFQva2lt?= =?utf-8?B?VTh5UUhrbUx0bFlsaEY4SXhXYVF3UEdpYzhBY0kyR1ZuM096eEVPcmRKQkVk?= =?utf-8?B?elMyYlc2cVRzNk9WQk8ydDNOTWdQbkxVK21HY2FCamFWc2JjTGM4aXhOaWRS?= =?utf-8?B?WGFlZjdaeVBDM2d0MmlDVXVvVmt3MVR3UGMvMTB5aWtSdjhLRzdKZnFZZXJm?= =?utf-8?Q?2SSD3cSVrbe0FCSPNkN6SCYK9?= X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1635;6:1GCpyH6gfovwTvQcdHhnrGS9r3yp/fftDVoRKzQx8y5qlmVOmSWK6NV7BIy9P81girpxHZfEu8KkocWpN2N1wiBmaxGAu9YbQYnsojVzWI76K4S4a2O4NYUi3f70TUrzVnXxDUYuzLxp4f7K4QcQAkmNEcE7jDXO2tXmu9MiZPSRb4qr2D/r73uoIJX30++QT2sliEOsICG8bt7IwqLxyXGT1FVHS5UutfjyEgB4UawOXmkO0b4Ml3lZehN2kSFBmjEiFGYIzTVmTLxOPY35+dwF8/ikMyQyBqPaiHUEBPMA0vrxa6V/7+Jpm0yBzctAENHBEHjENmHYahtiFoanVhzdBJMfE04RbmkSi2gwRqFj1oegk+TPmkoR1y788W/mo8u5N/ql1VwEMR+1U+uAsfpBbc3hDX33FPKt6Ll7tQ3V7WO1ZMRe7jyeapyuJmnN;5:RfV2SzST3jA5NpMMe99v5sRpL4KRL8zpZt/MdpzFQ2AZQTljjTALf2n4E1EfwqKGNwfGR2+WYWcCb/TZI78cV23e8CLkbGR0lyZ4GhnvnIQOnQy1G0lzkJbILhdJ2hLDGSR5c4wsXsxS3YX3udrcrQ==;24:FQK5FW3/qbBzgWEGI2Vs+znrQSiOTpvkp5PNH02DW6mCbVyqOmrhJaX2IOhNYI7ws8XEPaJ8ip+p8I9xrcH34lc8BaaYO2HE6E7PgHQhXAo= SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1635;7:EsE06RS+5f3viEiZIdqEWWR2Tw2fMyGFbmCEagbRTMSjtMMLHKWHMMAocjamADCHZzSbvtRqpPzxKmWa/NuGcFmd49rZkmItNmy7z/hJUIPRrLhyFTVsYpzanJBvxPE9EGZr34EQF6cpoXI5llTE/2k/lCkTtkwppItzsw+bVnvwyx3fuM/lNkUCj+t1AZxa8asKD+NrtgUWoId72uJuutw3taUXuyv5Vh3owyELRyxhKGIo2DGJqvkmmmiaLFD4dI1jHjRdP7IoQ0KiqKC85ODG9glYzHg8kXpXeggz2QYEXKkT4do3sPw0H21kXCaB7hnfn8vhcjm59pw1D4mlofRwZMGSy7onmRRN0RDOCbTSIaeoFaMbqzqlV/nyx54deBWrZZCv+kKrgw1SbHcM/G4TnqnQr65GHD1Vc9DIZcmBbzHiYGTprLqBn40D+0ygDBc84PJeepycbzhHKppQvg== X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 23 Jan 2017 15:10:04.3511 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN6PR11MB1635 X-Pg-Spam-Score: -3.1 (---) 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 On 1/18/17 6:09 PM, David G. Johnston wrote: > ​That would not be a productive exercise for me, or most people who just > want > some idea of what to expect in terms of behavior when they write and use a > Stable function (Immutable and Volatile seem fairly easy to reason about). Yeah, this isn't an uncommon question for users to have, and "read the code" isn't a great answer. If there's a README or comment block that describes this, that might be a reasonable compromise. It would certainly be useful to document how to push the planner in the right direction as well. I didn't realize that SELECT ... (SELECT slow_stable_function()) was a thing until reading this thread. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance