Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZrGxb-0002Hv-4T for pgsql-performance@arkaria.postgresql.org; Wed, 28 Oct 2015 02:59:35 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZrGxZ-0008Jx-V8 for pgsql-performance@arkaria.postgresql.org; Wed, 28 Oct 2015 02:59:34 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZrGxY-0008JX-0E for pgsql-performance@postgresql.org; Wed, 28 Oct 2015 02:59:32 +0000 Received: from mail-bn1bon0059.outbound.protection.outlook.com ([157.56.111.59] helo=na01-bn1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZrGxP-0005YT-M1 for pgsql-performance@postgresql.org; Wed, 28 Oct 2015 02:59:29 +0000 Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.attlocal.net (2602:306:ce8b:3460:505:133b:5569:d170) by BY2PR11MB0679.namprd11.prod.outlook.com (10.163.111.21) with Microsoft SMTP Server (TLS) id 15.1.306.13; Wed, 28 Oct 2015 02:59:20 +0000 Subject: Re: Partition Constraint Exclusion Limits To: Vitalii Tymchyshyn , "David G. Johnston" , GMail References: <7C9FB5CD-7AF6-4167-8CCA-2075BD9091E2@gmail.com> CC: "pgsql-performance@postgresql.org" From: Jim Nasby Message-ID: <56303A00.20908@BlueTreble.com> Date: Tue, 27 Oct 2015 21:59:12 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0) Gecko/20100101 Thunderbird/38.3.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [2602:306:ce8b:3460:505:133b:5569:d170] X-ClientProxiedBy: BY2PR03CA060.namprd03.prod.outlook.com (10.141.249.33) To BY2PR11MB0679.namprd11.prod.outlook.com (25.163.111.21) X-Microsoft-Exchange-Diagnostics: 1; BY2PR11MB0679; 2:4xHw4/HmL89wnIVD2hzHDJ7uEzSCOmNtRbsT9MInS3gPNImGjm1o94ybvSMJI7z6zANzyv8rPkTTwWRAJuxL0qTLojxjATnLmbuQaSEaAFDZN054x8IYAH9v8lBM5ssDd9HBIvhxaUrtePtrSVfpeid/TRsblSGwadKgn/2PpMU=; 3:2SMDtc1LDJI95W0BG5UdFhAcYGlxcM+PGb6Z3+oG+UFISK6nFT5FeWbZyYePmtv7IEB2kEan5Su/01A55ievopw2qt/vlQ1gJHz12OR7r+JToy/cGapPHTi4e0ez7wTqMh7ZDm6SkfXlpG32bXHhPA==; 25:xizB+wf7ynTdyyLkIBwr0FqxXqrcGTd/KNpmIpHpflQJJbtxuRYIi56H/mPwMrqGMBbMBVs/Eo+btXeOtpcINwSY50OZQtFMCZv1ZnnMLGPJ/4ukQv+jzfLW+jjB2PvXA8ewuD+Y/CIdaB7fPwWjyl+VTdkx09RPFJKl89U2wqqiB3Bt9I264lUtNMLWw/Swy8dBPNI3wXL1xbfq+zcsJSfSFei8OK8d5tJ7un6Ci9iTEdri0ST27v1cjpY4lUNHehvC5uUDhSo01PGgPrmHwg==; 20:C5DncLwDvsqM9RF33jJdUCb/5ba2AVKC7OvRIN94EV+YtVxABqWVhZJ7pbRBGAQYqZwjg1fNUSTEytZugRlOKIbUz7G2fvOOZRHDd5zcLEYdyU+x2yTPsufhkjsxtK2eSm6EsW7MWNID2XmRjo0i0guIhn+m/undHWyiNyahaH4= X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BY2PR11MB0679; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:(184068855800151); X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(2401047)(520078)(5005006)(8121501046)(3002001)(102215026); SRVR:BY2PR11MB0679; BCL:0; PCL:0; RULEID:; SRVR:BY2PR11MB0679; X-Microsoft-Exchange-Diagnostics: 1; BY2PR11MB0679; 4:9uoom4r6Nk3iVsKsqjksCF8wBtr3aXpKT+HP/2VcvWFVZVWBO+YcFk9UV6gCiPLBMIvwUYL6bIOevjos7eZaXsWEMhgM1shvSaAuF63Q1hzTaCqyFl5WwdKrUTzcOv5mEPzEd77p9fcvVZGMMddrAp6MpVbeyBV1MXVn94X3ax0h1CEHEF4VAx61kEIQWL462PVB+j8dP2GnvG5CtzoJksmIfQibHs6izfGotkIIxfUgebiokuJ7uwCKAnkc07FzgSprpNt0C9lGnw3sRPZtLraUEvhPFYQoS60L7HKyzPSE+VRKIgQRUldXmjE/4SXFIkGO2eooclKy1kKvQgjEuRLvioOsNjupvpMacF72u53novGAlRx4f8OZTuYdLxra X-Forefront-PRVS: 0743E8D0A6 X-Forefront-Antispam-Report: SFV:NSPM; SFS:(10009020)(6009001)(377424004)(479174004)(189002)(24454002)(377454003)(199003)(101416001)(15975445007)(97736004)(19580395003)(47776003)(65806001)(42186005)(81156007)(5001770100001)(33656002)(4001350100001)(4001150100001)(69596002)(53416004)(64126003)(86362001)(87976001)(122386002)(50986999)(83506001)(50466002)(54356999)(65956001)(65816999)(36756003)(5007970100001)(23676002)(76176999)(5004730100002)(77096005)(5008740100001)(92566002)(189998001)(2950100001)(5001960100002)(40100003)(105586002)(106356001)(3826002)(85282002)(336755003)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:BY2PR11MB0679; H:decina.attlocal.net; FPR:; SPF:None; PTR:InfoNoRecords; MX:1; A:1; LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-Microsoft-Exchange-Diagnostics: =?utf-8?B?MTtCWTJQUjExTUIwNjc5OzIzOmdHZEs3OG84QjF4cGV6RUZWakpqWFZiakJr?= =?utf-8?B?Wml3eUpES0xxTEloa1JmRmw3SHR4VmI1UHNOZjJwYUZocGpOQmpUbDQxT25t?= =?utf-8?B?TjVNZ25wdkhSS0k5bDdIQmZuendJR0dIczluSkhFTkUveUdCZ2hJL2YxTWpn?= =?utf-8?B?Q2xnb0NqWmt0NmNIY1MwMTc3bzVSc1dMVkhYcTA2a2M2NlF5ZmJqUVVyYTk3?= =?utf-8?B?OUZrWWg2S0VaNzdaRUpUem0rZkRsUnAwc3N2R05MUEU5TXJLemNsV1p0alNH?= =?utf-8?B?SSs2YzQ2Rys3R2xqcG1Hc3F0UWdaTUtlaS9NdnAyNlhlaHVNT3hGdEI0Y0lN?= =?utf-8?B?OEsvc3RzRVFJMnpJeFFYeHBTeEtYYVR4a0FNYmk2R3JraUJuL2RaVmIyV3ZE?= =?utf-8?B?d3pGeUR0N1o3d3lVOGdySityQm5WWlNvYTREWjdzbTUvcjM3bzloR29sWmI5?= =?utf-8?B?eE1NOFlwM01XeGo1bHIwQm83YkNCQ01UNzF1czd2NzFSaVhFQnJqQW1ET0pL?= =?utf-8?B?ejJYeWlYREw1SXpSUHZmNGM0cmRNWUNrS2puaS83cXlYcGNrS2FBTVl6UklZ?= =?utf-8?B?SW16RkJEcER3dXBBNG5oM3NmZno0eEVRM0RacGZONUpSWHFYOUZkS1pRZU1L?= =?utf-8?B?bndkekxjL0o0UUhtZ1VEN2c1UGJ2SUVic25nRkFNK0ZvTXBsQit5RFppZ291?= =?utf-8?B?aDg4WFo5ampVWDNCK0hIa0FwZmhoUWdGKytaMDVCRDVaSlJpcmx6eWVzdlFy?= =?utf-8?B?aWxBWmtxVnhpZ3cxMmFWSGxjVlRSbElPb0RBTWZXdVEzTDBXT2Q1cHBPNVFa?= =?utf-8?B?VCs3ODFSeEt3d0pFZzNEOGt2ZkxZSFdqcnZkTXh2S2M2alpIeTY5Lyt1SlBr?= =?utf-8?B?YzBDNTM4SS90OUxGQlY1c0t6U0hFZnlpSXBOYTVEVkZqNFh6WHVkTkdIR2tD?= =?utf-8?B?bUNuNDdoNzNCWjRJWUJSaFFFb3JuK2J1RWxDYStHNE9GZ3ZHQzRFLzlxNndB?= =?utf-8?B?Y0ZtOE5KQUU3SmNiYzBzNlJxTHpaeGplb1FZN1NjRUpMOTYzU1BXREdBbk8x?= =?utf-8?B?S25BZm8vdUY3eExZc0xVb0ZpUnhLYit0SzZ1MXFndS9Xak1qM2Nja3hkMUdo?= =?utf-8?B?RjlhOXRVU0NrZURZTGYxZWxpYThCdXpVQm42dktwMzMxV0xEU2pEelVaSk44?= =?utf-8?B?S3h6cXB4am8vWVFpalNNWGRQeEJTT0RiL1prNHcxU3FYbDBUbjF0TkxyTjZv?= =?utf-8?B?SURBdHhXMEFJeUtxYlJTN2FzdHRQRE91TDk1b2hlUHJGTTZTRnJFZTBjUk9J?= =?utf-8?B?cXp3QS9XSm9KaFNXc2VBaWNaWDJTVEVHM1FXVmRjZExXVVZHS0lpT1VxYVJq?= =?utf-8?B?VFBMNWRCeUg2SjlpT0ZEQUw3MXkzTmMzdjNTS01iYUx2ek9JMHN0U2ZjQjkz?= =?utf-8?B?LzRUampTbTN5ODZWQlU3WUs1MWs0Q0JPVnVuN21YU1o2OWxWeHJXTzhWVDJw?= =?utf-8?B?NWNCM0JHRXhHVWg1dllTYTkvYy9VZzFBTnlHaTVlTGkxQjYvOFhmRjJiSjNG?= =?utf-8?B?SnBON0M3M0RLNmh2eFcyN21RNHM1QnlvRmdweTVaTG56ZkZxSkVjdkN6Z2Nt?= =?utf-8?B?cXMvTjAyaW5ncWFoc1RUZE5haWYxQzBOa0RldUN5a0hkTzZ1SUpyYjdzaS85?= =?utf-8?B?eFNPdDZ0c1pLWWVCakMwSy9jNDkxZGM4V0c0OExkc3M3RWx3K0hiQUd0OHpu?= =?utf-8?Q?r7EoG+gcytLX4jAFhP6aE5yFtXMyo7a+UbnpM=3D?= X-Microsoft-Exchange-Diagnostics: 1; BY2PR11MB0679; 5:JWmm41ZRSUF7XHwd3BF9Fg4GPLtGcKqHFapxaVz4pMwT/LMxfMncKgYI2PIrnox5fRfTM3Ui2fvM8fQ86ZaUQP7dEc2RnC5MRzWIcnW63y4mYa3N+cGmaaZ4+IoLH7dqxtuUndO3Bjpt5nwumV50Ow==; 24:Zb16alOkCeBHYfT0o4JEqgrOkOX3jaxgpR5zXLEABmpZA8o+sH+RICHbBz+r2JJZcpBJtLAEXz0gy0Bhs4T59HkzaCVz6w86w4iZDUrWi+U=; 20:goZsUY2fwhJue3h5pl50ljjTmQ+udV+kMkinqmDO//YM1VAyLHO8Lrx6Aam/k2341OTx2cvW4w/Jb54B6EfGCQ== SpamDiagnosticOutput: 1:23 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 28 Oct 2015 02:59:20.1040 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY2PR11MB0679 X-Pg-Spam-Score: -1.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 On 10/27/15 3:33 PM, Vitalii Tymchyshyn wrote: > BTW: May be it could be feasible in future to perform partition > exclusion during the execution? This would be very neat feature. True exclusion? probably not. The problem is you can't completely exclude something based on any value that could change during execution. There has been some work done on declarative partition specification, where a given value would be fit to the exact partition it belong in. IIRC that's currently stalled though. One thing you could try would be to create an index on each partition that would always be empty. IE, if you have a June 2015 partition, you could: CREATE INDEX ... ON( date_field ) WHERE date_field < '2015-6-1'::date OR date_field >= '2015-7-1'::date; Because the WHERE clause will never be true, that index will always be empty, which will make probing it very fast. I suspect that might be faster than probing a regular index on the date field, but you should test it. -- 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance