Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1baVNJ-0002ML-Ij for pgsql-performance@arkaria.postgresql.org; Thu, 18 Aug 2016 22:01:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1baVNI-0003nt-3j for pgsql-performance@arkaria.postgresql.org; Thu, 18 Aug 2016 22:01:20 +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_2) (envelope-from ) id 1baVNH-0003nM-3u for pgsql-performance@postgresql.org; Thu, 18 Aug 2016 22:01:19 +0000 Received: from mail-bl2nam02on0059.outbound.protection.outlook.com ([104.47.38.59] helo=NAM02-BL2-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.0:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1baVN9-0004Vg-Pv for pgsql-performance@postgresql.org; Thu, 18 Aug 2016 22:01:17 +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=KrQ30Ila5RWlx+5B9sFjWiwAhxmBeDK8kAKFoHxA+wc=; b=I7sWFyOIAvx9ns4+pyDBQ+jkrZnx0fcsyCdwusn0MEF7eqY4jh5OYm2meJl3DDdxEPXUCdojAczprsxZsKc6tm8Wvzrx45X08YquOpZkpiOijt1r9C8feywAyyk8Aam+xfqwNThgUy7X3q9eee9BClP9l+JvyDWLdpKOolGW1aA= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from [192.168.1.8] (97.77.122.57) by CY4PR11MB1637.namprd11.prod.outlook.com (10.172.71.13) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.557.21; Thu, 18 Aug 2016 22:01:08 +0000 Subject: Re: Estimates on partial index To: Jeff Janes , Victor Yegorov References: CC: "pgsql-performance@postgresql.org" From: Jim Nasby Message-ID: <4c143963-7fc1-b1b8-e605-35b695969830@BlueTreble.com> Date: Thu, 18 Aug 2016 17:01:05 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0) Gecko/20100101 Thunderbird/45.2.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [97.77.122.57] X-ClientProxiedBy: SN1PR12CA0011.namprd12.prod.outlook.com (10.162.96.149) To CY4PR11MB1637.namprd11.prod.outlook.com (10.172.71.13) X-MS-Office365-Filtering-Correlation-Id: 643e97ec-804f-4d8a-084b-08d3c7b328ac X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1637;2:WRNaCArqH5yayNVSfPc7GTnAZ7Eh/hhkgfuSoFl8Hien3GHKaJ063zMZxKtB1KdwdqeyVbJXVNCyPZGjqxgmemV8Bj8Zk048Xhpf2Y7Ssq7R8PTb7v9VUm8CYkB/HSggj4QbeTi+FTYeOQhpTmA5GJDkQ8LM3XUD10Jy3DHChOKis4bubQJJtiOhzPixdxrb;3:y4NQ21bWoOIqBYY3tO0HFGp603buEMF/8aOGv4spBUxuszzaYLERZh/cWXwWu84xVwvE56i6ltyXs9j1rSQ9ilKhGSmiFYT8sw56VRboZEVA09R8H7GzYObotk4uU23C X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1637; X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1637;25:jRXyVoNlKvJI2PM6KX1jk6FY8YHD4cm8BtgRW9dafCkWogIgzd+pftLE//rVn7JGqPw0VGxW9aRAUku//eNYVpN4SHXnCduq5L7ajy8ixTpqh/EnH1CCHFIuxZrN7QhP8CJRcScCOvqhzBBNrEsSHFLNJh46N4PDvrbjYTB5zHRQr9zA2jpge0UwfRph3wz4Zh/U+tu/vdfTj0YeIOEXHwO4WdW6pMkVs95lO16z4uKmmGdBbI+b9+fSkASiEzfHwCOqwHA7CpFIAP9TInxMf1XFbjgYba2nlRqJosiNtIjd+S4XiYWi2jA9xsXc0GChpYSErCfZN6r5XKfP/ZWW0H/Qqpiyz1byGWRS/5i/ym2r0DVCsfe6lnbQhJrAiWCPMLJHlsrmqubjYH2/9SrTkfBG1aEgOjZxAhP2oBc7VcM/OgDqxylOkzaYrD/P+96WGlNziHyHuQsu11ltvqrfgH65Rjm4xU47AOHwpdzGJmqluSfMNkKIVN6grNie3jpPp9CaElaJCMapFGdT8W0TIamE8HUufK7OKgQcCWPBcVXwVESUlCyq/QdXSO2h3ZuhH2CO6EE/anp0RIHbz94TD3odvNcXLlAcTq5Nrlrvs/+Xv1QtHiMlEXfo13YYFv5KsHqCPaut4AFv5uAwRr86rP4dkji5lcFPtTy+yGNW7nA5e+oka/6NhEV3GNsYtDxXyBJj57MXUOitMV5KtNSGXQyFoxLmwXBTD368IuC3g69jfECG7IenST+9y35kCYoyKU1ffbC1uQIvjkzMPpyMakDCJSsaaZUEykV0u5AJLAgN+nFX46Gr0aeXCScyrZMO5N+S4057PZ4DtsBLPcpftw== X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1637;31:QpTR0xbI/C4fXKnQuA5USGwqYbGag+aCLmn7euR9ZHdGFwh8UhlvEaemkAdV5Xyq+Aii76Z+SKKhd8VBgFDtoSXPlCeKGOg3hxpJ1ELz53mL/lj/gcYHz1TOBBVwC7HlQ/8+29tSaeEI3+YeQpcyKvW7SgrPMKn5UWg1O4ujrMYEFou8v9vInmmxEdXD77DlQwoh+cZQGLUPm0uGOvcfGARBkw94ZGNvscNl8aNSduk=;4:Ass8Ylo6cU7UWZ3DZPzsMOh6+8/c0YtzgDY4GsiX3kAryMPpIr+55aq5Iwq78/OJwGIXEiRPs+F+7TO/uVBkUz/9pcaF3ylQtFkc7JilWhQ9bjWlDSkOG3F+eQTMON71InEulcukzuM561WpsgoS1PsGmh6864Y14DRr+Sn6k6ajIXxVSwfvCZge9JkGv2l6YdL3RzLjL7IZvJO3GNbihKH3TfDYAnWGCQ8pv7ps9K/wzdt/BdOCNEX0iwRDYt5mMPEA/zPThDzCr8t75hdUJeCyckTvhekLtaI0P3xHPkuLDopu6gjBE06oAmLD2guEhqR+9IG0hVpynjtPo0uACoj+IYnxVnExPt4DHS5e9q4JdmbLnunjG/9aoT9pgHgw5mDilaIkor9raRPcs2vdDPJxgPxZl3CCNBw0f2XbxTg= X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040176)(601004)(2401047)(8121501046)(5005006)(3002001)(10201501046)(6042046)(6043046);SRVR:CY4PR11MB1637;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1637; X-Forefront-PRVS: 0038DE95A2 X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(6049001)(7916002)(189002)(24454002)(377454003)(199003)(76176999)(33646002)(92566002)(8676002)(7846002)(117156001)(7736002)(93886004)(19580395003)(81166006)(305945005)(81156014)(65806001)(66066001)(42186005)(16601075003)(47776003)(586003)(64126003)(31686004)(50466002)(230700001)(36756003)(6116002)(101416001)(54356999)(97736004)(4326007)(5001770100001)(86362001)(2906002)(65956001)(31696002)(4001350100001)(83506001)(3846002)(68736007)(2950100001)(77096005)(189998001)(106356001)(50986999)(105586002)(15975445007)(23676002)(65826006)(85282002);DIR:OUT;SFP:1101;SCL:1;SRVR:CY4PR11MB1637;H:[192.168.1.8];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?MTtDWTRQUjExTUIxNjM3OzIzOnNpYkZuSUJwRTlPcGgxWVdneWNjYVlNZHZS?= =?utf-8?B?YTVsdUZLM3NmYjdzcXlBVUk0aXBkT2pQa3lCNmhWcjMwdjR1QzRJYjlPSTVX?= =?utf-8?B?dGIwUktXTlZKTUR5MTlEbXpEcllmNGxnTnBMUTV2K2Y5bWFBSkJPNm5HWUMx?= =?utf-8?B?Tm14THZEME5Vb3MvQ1E1cjk2QWhaRHFtUlFKTlIrQUVDVUVkWFV2WjNzSm9S?= =?utf-8?B?cDc1RWY2S085ZytxZGo5SmRqb2JUSFYrdkh3WElhbitxeFpzMGxHQ3A4N0ND?= =?utf-8?B?NGtFK28vMEt0L3h2eXJIQkxUVEVPNHNBTDQxWjZCRDhDaTZpRjJrVlVjajF5?= =?utf-8?B?eUc5ZENlNDE0SjZwY0xJOUoxUGczTW52bDBESnVPMUFjUXQ2SnZHbkpERmF5?= =?utf-8?B?elZidktEV2xMazhCNDdsT21PNXFDbzQ4b25mZWpQMDZQUDYwVzBSZWFqWnFN?= =?utf-8?B?QkQ5cUZtMXprYXl2U3FFdjRrQm4yL0wxRk1xZ2NGdS9HSEVwL2lnTlN3Zml2?= =?utf-8?B?cUJtenl3NXMrMmRNNzR3bEJJLzdaeWNva3BBNFpGeldFYmptVWpkSlA3L0lR?= =?utf-8?B?OERQR1BsMG45eEdRSzA4VFFJN2dGdEZBTHdLT1BQR3p6YXljd00vSC8raVcy?= =?utf-8?B?R3FiSzBsNDltaHBneEVyNExERVBJdmNkVjFrWStVL3paNGdlaU1SKzd0ZGgv?= =?utf-8?B?cktKQk4wMHZEeWMxNmZTbFBoRHBLaFNkS3R3aHBHUm1yOVdmemdpWFI5R2dO?= =?utf-8?B?cE9Pd1REU05MNVlpUDNNVDIwMDROVUVNajNYVWpucG8rQU5nSTlwQkhOSkJJ?= =?utf-8?B?emVnVGsvK2ZDM0E4SzVYQzlVcWdJZ2J6eHZuR3paTjhmZlZxMHJhLzJxanpr?= =?utf-8?B?YzNOTUd6eXZZMG4yNkJRZktzbTlDL0t6V3FJQ1k3c29iSlpmUzVmSEw2Rzlu?= =?utf-8?B?ZGpmc0lHcTU2ZDMxd1UwK2RRSlpSRFhwV1Q2empxWThOSUFVcTB1VVV0Q1Fh?= =?utf-8?B?OHBKcXd6ZEQzbzJzNldpalFNMUsvZXNQMEpRTDdsY3VkU0VNVlhMZkFKWW80?= =?utf-8?B?d0lLc3h2YzNDMVJqRk12VllUQjVkMnFuSHpaRTlKalBLL09tSWt3TzZDaTdE?= =?utf-8?B?N3JQeVVIWnR4QmwwVnR3aXU0YUJOV3BsREUrdncwRHorOFBsbjFBRlQzR3Ey?= =?utf-8?B?NlFJOHhxZHN1Z21YN0lDSE9xSTJUUEZkWE9WVG5RM2FFSTRQbC9zMG5USExR?= =?utf-8?B?RERLV2hxc0c4WnliQVdXZ2txZmM2TmxPeWlmWWVoc3FGQ01HRCtCRUttbzkz?= =?utf-8?B?a2xvR0lqTEp5MjF1U3MvWkNNVTl4cTRKcDZWVnJFR2RFUDJSajFvSGdsSzZS?= =?utf-8?B?UkNoblNXY1BkTk1LaE9zRWFGTUlGZWN4dkI0c2VuYkV2OVk0NmxQTG1YbG15?= =?utf-8?B?dzdGRFlUSGdyVWxzSFI5NG8rVEIwTzZSc2xHR2g4cjQ4WkE3MXlaeDRiNC9j?= =?utf-8?B?UzFmbGRKSUtYL0V3d24vc0NSSnJubmRBM29qUTV1Ni9SYWNFL1o1eVlRQ3dK?= =?utf-8?B?V3BLNzNYVWdGMmNrSkNmWkZYZjkvdkdGbmppREZjRXBPRkYzTG1EZVlBWm1Z?= =?utf-8?B?OEQ1akdBOUlqS0xHeGR2cmtHRVhXbXhoNVN5aVpvcGhHdDFMUzdseW9lSDhT?= =?utf-8?B?SldXYUdqUm9VZnNuT08xNHJGM09GSkhrTW5ETlFUZWdUeDRVOCt6cWdjMS9v?= =?utf-8?B?QWM0M21tSVg0MWxvU2RiWVlHT1RLZk0zQ0JxejBlVU95bTFIQ2VYY1Bzcm1H?= =?utf-8?B?Rzcwa09zZ1Y2STI0TG5YUjFMOHBpNFRvRHZkRlczaklWbWc9PQ==?= X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1637;6:ZykzbnqcwkioLxlAd3gmy54LS3L+JfqBfx+E3CZymCWdXfLpPNGhj2rzDGTdgH3FA0rby63UezB1BsLwmTV8QAvZs93jhDOPQFGACemfUT61kJbhzzfHMue0RXzgtmV3Vqec41xEBvphWecNfLd3cZAGy42QPMo83iYGEO7GlL68ovrqyAZu3IyqSOLlcsKe3p5BeytDOfI+PJPZl0HAsW7x+H1u917yQ1DR5Acb6QTUi0zuLg6a80RrP7WicBL0saQWK6Y6WVwjGEGhLUaVdmtqFQ9YvS1EWaraO248ocqXJEFqUREjI48E8Xz1WJ4Z;5:snXXehhoNrfVBZBX1h8N716b2m09nnEqpNnwj1bkk0T/svojFZt4MmOdd8oE/Kz6iK0qS/ONN208aQnZLq3N3CU/SHPLk+bvEhPFeus0k2yl7O9qIb3pC9zkNfxEb1OP9ie/4EhjicpWlKFDSHyBJw==;24:DJRKnUDFCn+F62lY0tW1Yt9pLPPf3v+LU3tPF+QJqp1G3yLIOKQWCOItYF0GswoFjbmP4LkhVEX75R/4Yeq7HFSqaMHH/HDSySDacWMITh8=;7:tIJltQFyeSqXQ9LJTAcCTg4Nqvdh6/Vrw91hailwBoapy0diIMh2lHe6WDIIE2MGyYobrCKZmAr6Qg4j9zQ9S+aPxTW75au55fR6frM8mYoqQnQICgldZ4TVo3qNasC1VhazmcTXGnwC35F/Gex04BbaRrUpdX0mXZdd2Wa/1KeAfaFuEqQnj717nVeE8/HbHZOGWUrQjgkX+qcMqLBlMVlQBQDfB0N47Fx7i6bxheKurfsAs4uEm7L8GBMjdFU5 SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 18 Aug 2016 22:01:08.2045 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY4PR11MB1637 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 8/18/16 3:06 PM, Jeff Janes wrote: > If you can come up with a data generator which creates data that > others can use to reproduce this situation, we can then investigate it > in more detail. BTW, the easy fix to this is most likely to create an index on due_date WHERE is_current. Or perhaps partition the table so non-current rows don't live with current ones. I'm not a fan of mixing history tracking in with the main table, because it leads to problems like this. -- 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) mobile: 512-569-9461 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance