Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bdUzp-0004t2-I7 for pgsql-performance@arkaria.postgresql.org; Sat, 27 Aug 2016 04:13:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bdUzo-0002xR-1F for pgsql-performance@arkaria.postgresql.org; Sat, 27 Aug 2016 04:13:28 +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 1bdUy7-00017A-AL for pgsql-performance@postgresql.org; Sat, 27 Aug 2016 04:11:43 +0000 Received: from mail-bl2nam02on0089.outbound.protection.outlook.com ([104.47.38.89] 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 1bdUy2-0006iG-V8 for pgsql-performance@postgresql.org; Sat, 27 Aug 2016 04:11:41 +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=TDlTIirH9faD7Xph2PMaEqnJ3cQVkNq40f1e02M1TVM=; b=Z0jKyyFbfmayyRNhoGsTOnIykxCdePnKfArv0Xtg9q+DvqRFNCDzv7cC7DExXcgdINEE0V5HFNdz14KeRmKPiWpPW1DK35i24+FLgnVAENCM7AzlMcFaxJBQrkRqfUN8aHx6rVxw4/ExqsG7lrcNzGqLnOwtP2v2hTtBbrrYo3Y= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.attlocal.net (2602:306:ce8b:3460:91b4:1e26:9c6b:d3c8) by DM5PR11MB1643.namprd11.prod.outlook.com (10.172.38.14) with Microsoft SMTP Server (version=TLS1_0, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA_P384) id 15.1.587.13; Sat, 27 Aug 2016 04:11:34 +0000 Subject: Re: Slow query with big tables To: Mike Sofen , References: <00e601d1ffd8$2beb8850$83c298f0$@runbox.com> From: Jim Nasby Message-ID: <1b6396d4-ca7d-05a1-51c7-3b1d71c6cd55@BlueTreble.com> Date: Fri, 26 Aug 2016 23:11:30 -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: <00e601d1ffd8$2beb8850$83c298f0$@runbox.com> Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [2602:306:ce8b:3460:91b4:1e26:9c6b:d3c8] X-ClientProxiedBy: BN6PR1601CA0010.namprd16.prod.outlook.com (10.172.104.148) To DM5PR11MB1643.namprd11.prod.outlook.com (10.172.38.14) X-MS-Office365-Filtering-Correlation-Id: 014f141e-1ed3-42bc-cff4-08d3ce303c17 X-Microsoft-Exchange-Diagnostics: 1;DM5PR11MB1643;2:Ab+/BYG4CtmrposX89a3i9EDCMA3fSCd01uJPzpNAcbNIAlErfwo9oau1TL/dLk/iTb5e2Lxx1I7qqVYFKYOCIEVCPECQUfEfz6HT1qb4TLpezqNOiTNbKJecAjVya0hvZTsmD1ixnEL6j1b7mJQtoAtwngnt9/GsqzCGsO/M1qgPqIfKLTTwXI6MEI0iXYn;3:wcAF5MEXAUIZCglvFY5QWtQ3cfpuvzRYo/HSaZ0AktTxljRenLb+kz3QUGc8p+tNfXQV1gUaWoiwBMzN3MTR3vGTipGmvRDLCC5IASyovpgfg9UC35ZbhXatOkhmcLhi X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:DM5PR11MB1643; X-Microsoft-Exchange-Diagnostics: 1;DM5PR11MB1643;25:B68T19pCGDSht4Dzapnoe/LBOHC8i3GxANrTVUikIRvE0lh1H/Y9p9eAuYOxs0dXLbEd6n9v13DBICQ3b30B5WapLLlOUjm3lBYwWBxptFGKwIEslnNPP17pYbr69iPPso4JIE4Y8jszgW+ErcVf1o1nPWKE5MugVufNdCcAh1EpXMRhQVWq9FkmJJzxs0/zDLYYVvmQ/CCkmwDVROwKUI4QhZi7AvUZk7qsTdah/54Dc6P2HqgvhbEIsW/o3r2MOYWhOHAJZs85uE5UUgt0SbMmwn4Bg/lM/UbPJWQe4FO5GkvMEli8lVIykc4EcxWqgLrfswgswp8+4HMXDZDmbW71jk8JtEkjvEh9H9RUOW5Z3Kv7cT3Pe5kAloncBbcv4RiYpSTAviPkcSTpL1fYDkjRTsuxssAq07VXQ6//coQu10Y7k+fv1JKiGV1Un+8keQJM0Cnj2MXoLygJ2gLXtw/6e1gQPE7FvX84+3YbTUw2azErRPVJuRV1jnkXIMWOpkTYZCGrVx2SacvHZZOP6PPen5hTkJgm/ueESmOP66AmvpVe/sCM3xXHwige2bhbZ+dZl0BCzKJOKzkfN24/B6CsRJJY+WLcOE+JUwM1nXl1Tl7cZTWOdN6xvhxruKX7+co58hSjd4z1aPI4+rgRSfRFGCPyx73NSYgB0Kp7PQ6+fQcA+IE3WW8qumI17ueyW9NTFkZEXxjzfN1Qkfgqdng8lRfsGhWeDaJn4h8taAZjqOz+oGwMPnOHRxXFcgi2uJp4pTsrVo9GeHFkBcLW7sPq8pdKqG8dj5/FvIBSdYs= X-Microsoft-Exchange-Diagnostics: 1;DM5PR11MB1643;31:xJh/epU7ENOszSltn/RM74I2wAB+A2nCuaITbBrGkZL+nkERvEXB5Ql8oAVyd5DCRwomBFlTWche9Mp/HA5pMReSYgBenC/vDDngQ4tsZglQZNdx+YxENx/Atz125mLJqrH9pgGFA2UhXv4I8dvP8Kltt/AiXu+ad13PQ6Ab3e6Jpwi6pYWHnifxFPuVNhG4FR299MLcoeAOoWSdh0gmlq7axYBXGNZOOWI4IjhyBXQ=;4:juDMxKKrkPPklOLNMUCwo2INrDfOrbo5YL2OpPh9Je3TznbmKSQPMJyaQeC48Y4WMnlqX/0bk5FYVLCQNMrJncn55kwO6cAnLCOme1WldfI4QSDHA53uK7tH6WIR81p3/VgwktjQQSRB43tnsPyh3sVP2XB0T6kkPm66Fx5up/MLy2EPJlh1fR5HP1/YpYoftB8331veKzP9iSa1ZPK8aGSCfQGhMoCJrvrWwMqyzkZvIGQUwwhpDZ1Um6qmcxrwtGEdhxPgjyjL/akSMEcuMNk28T8GqPeoa2sQAkePaeShwgl3eKgJIIrZZo0HCLEXBP3TRbHCosa4DNbkqog/RZFue3fJh+tuEiNgjPBv21gz2x0KLprZUGQ9bb4OEAG1cK+Sc1HM6wmESdKj+gMjEIWxs1uIZL2HqA9FSK5gqA4= X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040176)(601004)(2401047)(5005006)(8121501046)(3002001)(10201501046)(6042046)(6043046);SRVR:DM5PR11MB1643;BCL:0;PCL:0;RULEID:;SRVR:DM5PR11MB1643; X-Forefront-PRVS: 0047BC5ADE X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(7916002)(199003)(377454003)(189002)(24454002)(50466002)(77096005)(5660300001)(33646002)(76176999)(54356999)(101416001)(15975445007)(50986999)(31696002)(5001770100001)(2906002)(19580395003)(6116002)(230700001)(107886002)(2950100001)(586003)(86362001)(305945005)(189998001)(4001350100001)(92566002)(97736004)(69596002)(7846002)(64126003)(83506001)(23676002)(65806001)(93886004)(7736002)(42186005)(65956001)(31686004)(68736007)(81166006)(105586002)(53416004)(106356001)(36756003)(16601075003)(8676002)(47776003)(81156014)(65826006)(3826002)(85282002);DIR:OUT;SFP:1101;SCL:1;SRVR:DM5PR11MB1643;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?MTtETTVQUjExTUIxNjQzOzIzOnJySi9Gam9wcHJkVU5HVkFteUlyN0dpOHNQ?= =?utf-8?B?aUZwVDdlSlB2ck83ZW8rMUVvZGpydklCUmJwRU1BRURoV3paOWhUMTZtT0xC?= =?utf-8?B?OFlFdUNHeUlUeWhSYllMR2ZWa0dtZzQ3V0FhT1pnbFNhbzZhT2ZYVlovUEpB?= =?utf-8?B?MFRQMTJxVjgwTzAwSHA4Rk9qaklvc1A4MTg2NitMZTBJcUNLK3ZPS3pnckJC?= =?utf-8?B?dGRnZXpxb2x5ZUxaMzJ5bHQxZzNLL1dWUm9DdFN5Vkc1VzNNT3JNUnZFc21t?= =?utf-8?B?ZmI4eVNPSkZsb0tUdmNiMkttT003UERiaElmSTNRSzA3YlcxSHBpZGdlY0tj?= =?utf-8?B?Q29sTmFOaVRFRGZxUVVJYVI5RElZRWRXck1XRUNzdk9ldi9kNUJhdFMrRUho?= =?utf-8?B?aUZTcURleHFVOE1haGZmYnBSUzN5SHplZ3B0d2FxNEY2UHBKYXJ4Vm9TdXUy?= =?utf-8?B?Y2dRTnFHQjlZMnVSODVDRm9EckpzcCtHMG5mUVY2WUxKWjJvcnpIYktkcjE3?= =?utf-8?B?TUlSSkl0MGc5ZmVwZ2REWG5aaCs4Wk5aa1N5UGVSYlErVDVYTC8zQk9SdXhv?= =?utf-8?B?K21BN3BXR3YxZU5FR3d4SzhSTFl6OEhDRGpNM2diaU52SmM0RHFXYzlUMDBo?= =?utf-8?B?M3loZTJFM0RiaER4cnVJUUNpazJJZ2c2VTZSNGcxdXJuQWJuNEFCZWVCOGNU?= =?utf-8?B?VGJKN2tZZDhkK1AvQWlxRHVzangxRCtFTHFHclVDd3VJMzJ0QVJvOS9zQm5r?= =?utf-8?B?MHI0WEdxaTh3aEJjWjNPbVNjdUtIc3Q5dG1GVjhnQlA0MGhqZDI5eE9pMzhz?= =?utf-8?B?b1V3REhqK2x2NXNiMnBURzFlc1NERFR3SWRWMXdDZHREaEk5c0IrNlBldWhU?= =?utf-8?B?bTBDUnpSaitpaXhic0JENmZUMjVXL2dWZ2pLK1RkWHA1ZjFya0lQZXN1djlJ?= =?utf-8?B?YlRGd25XRzBTWnZhdmR2UGR1V2pDajBrU1hFZG44OWxBMk9vNzNubENLSlVS?= =?utf-8?B?dk1OYmUxSnllRVJsMmdTY2M2TlpqamhnOUlJdmZtMU1vQnhXeHd4RGg1Q096?= =?utf-8?B?Y1FPN1IrQmRYVnJhZFg5T2x2MUF4aUxRblhQZXVVcHJuUEdiVlJHdXhJanI3?= =?utf-8?B?amYwaG1ndVVWRllibzV2SmwzYThmcDltQXk1aVBQMWZIQVhGNHZUazQrYnJu?= =?utf-8?B?dDhDL2N3NnN1QVhTb0U2Q1ozUDVLbGptQkwydHUySUYyNGRvMUV1TGNBK0Vn?= =?utf-8?B?NWNBL2tJNFJUbGY1azI3ODBBdTRMcVg1YkpSVWhaVHpMdDN5TElrVzg5NDNN?= =?utf-8?B?OG1ZYkgyQW9hcFYwOXNyNjJLN1plWVVJWmlQcmgvcWdraXhJSG1jdUJVMitJ?= =?utf-8?B?MThzL3JQRkRtc1VEVFFwNElJa0ViMjdxMXorRUpEQjQvVncyblFVM2lGNjlS?= =?utf-8?B?OGVHUTlJYmpSQVVJeFFaZEVndEVlR0tmVEVkYnBlVkU4SmdYYjFNZ0ZJYnFs?= =?utf-8?B?czhhRWhVR0RoeDVnTEpQZ0pvTjdncHJUMlJoUDhrM2FUZjJMeTB3RGVGR0RJ?= =?utf-8?B?TWxzcWJrNjZKY0ZiaHY5VE9GL1ZKNUozSWlaQ3hDU0w5cWJ4MFpPQzJuajdD?= =?utf-8?B?b2NyYVJxRFFhK0xhdE9SZDg1OHdQRitjWUJxT0ZKN3gzb0NwUEs1WmJNcWdz?= =?utf-8?B?end4YXZhRnlEVFdUeXVubFQ1ZnFkck50MmF2ZXZpSjBiaVRJNGNYaWs3Qmdi?= =?utf-8?B?UW9XOGUrYTFIaXEybDVMdzF3eVUrQjRjWHk1azY3dUxRUGNYN3NJZEtTYWJz?= =?utf-8?B?c2Y1SmNhNXNDSDJtRkdXak9xOEhNZzM3dVZSUkZLTmhiUGJLbkZySVM5Ti9s?= =?utf-8?Q?eXoYKF8szQ8=3D?= X-Microsoft-Exchange-Diagnostics: 1;DM5PR11MB1643;6:gMm3dRw8yT3Sj6p3XIJlpm4GTkDmeuMumzKq2i2qRXGiu0jvbE11PPSxUyfRi99ZGQ0vo51qIdvZ2FgTQeMHHmB0XOo4Jhml3IPPgxXSrcSwfewXYZhBhyD8bY5pfKXMxtJbQDPNjrrmWS+f5UoP8z0AM6i79lwGCXD5ErnG2BFt5W5FIRpGGuzsPrb+xYe+ALKP2p+5rkjAbflk1aKbpvIyZApt333VFPEwO1gDsPMbC4UuLkFBnKFUf6ZlRqiyrU7UHMfCG7fiWsWLD4i5vgCxFTmF4Q0kiMHjLQPhg6H/OVltJ6unVNE45IC0FMAb;5:PSQwM7Xs/8IJU6UuLwSPo3OOEKk6DnHmrYq2eTcff0VjwWr6/QgGHZKCQ5Cydnvm7a4xK2DxXksTahypJwoH6fpiI9coz7Gn2uWtyiXLbT/C4HDGMqF6EhN8eqmsLbiBjccdDR7lQUFicuKJARaDCA==;24:fya6SdPwcEFu/z7vUek1juNBc4iMsZGDmRrK7cpUGtb9ypCpsx1LQDWi3VZkAAdHfvBBUZ6FB/slkA4OSVi/lASijNeVinyiUMcqiYhY27I=;7:tYX/wWIQ/Z3dFgMS4iFgmJGG5mYY072+FrNImYwF5d8y+N3m0I39NSs7IlRe8PYxFnXEb0hIIU8Ms1OQ4J5Es/OIL9jHPsEwBTkTK3JEfZNXM4zlDQHnZiXFufndXjn/QFmVgxC9fap2jzZQ8WID+96e94rkQjaKQI7QUFqCxQqaf0ZJsGn0CQTTYF9ZPuVjvzUMRvaR1tkx4k4um2j8EX8V3d2wQOyZbE+w21z+kjvawYmOvM73xff2ASWR9bmE SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 27 Aug 2016 04:11:34.6863 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: DM5PR11MB1643 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/26/16 3:26 PM, Mike Sofen wrote: > Is there way to keep query time constant as the database size grows. No. More data == more time. Unless you find a way to break the laws of physics. > Should I use partitioning or partial indexes? Neither technique is a magic bullet. I doubt either would help here. -- 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