Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bq6AE-00017n-Ql for pgsql-performance@arkaria.postgresql.org; Fri, 30 Sep 2016 22:20:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bq6AE-0002RI-7K for pgsql-performance@arkaria.postgresql.org; Fri, 30 Sep 2016 22:20:18 +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 1bq6A7-0002Ad-DT for pgsql-performance@postgresql.org; Fri, 30 Sep 2016 22:20:11 +0000 Received: from mail-sn1nam02on0075.outbound.protection.outlook.com ([104.47.36.75] helo=NAM02-SN1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bq6A3-0006GM-Mh for pgsql-performance@postgresql.org; Fri, 30 Sep 2016 22:20:09 +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=bxWVzHfmF5FZo+q5DpjJDW5d5CBBkAWUpA3/I76GfD4=; b=lFdJ47GyuZ3/pGjj8g0MiTTGQljhNFoi1wUIcai+/PfaTYOLbJncs0GFgMuifm2YpzcfIHxmCjEL+vKwAkzrvJHjaZEtInYijEiXO1kOTraySqrGnC7Uzby5b7THy43+s0X3d/S6W5canQ39UF1RWU2RAqdLie0DsHIQyfzmbKU= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.local (70.113.18.142) 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.639.5; Fri, 30 Sep 2016 22:20:04 +0000 Subject: Re: Unexpected expensive index scan To: Jake Nielsen , Tom Lane References: <00e901d21921$1e647180$5b2d5480$@runbox.com> <15575.1475067892@sss.pgh.pa.us> CC: From: Jim Nasby Message-ID: <7abde325-3452-a1e1-e288-ee33408d7708@BlueTreble.com> Date: Fri, 30 Sep 2016 17:19:59 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0) Gecko/20100101 Thunderbird/45.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: [70.113.18.142] X-ClientProxiedBy: SN2PR17CA0032.namprd17.prod.outlook.com (10.169.188.170) To BN6PR11MB1635.namprd11.prod.outlook.com (10.172.24.8) X-MS-Office365-Filtering-Correlation-Id: a68ed2b9-3507-45f3-3500-08d3e97fedf9 X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1635;2:kmA1lRecg2SdUwzt0wHP9GcLlPsJ7WR+kJgag42BJvJr5iVP2Qj6QwLQcqcPHu+xb9x8RaFmSKmMdgJFYUg9w4G2Tmnkx9xQg5uzJcj53507k33hBQVq46kJtI/1ClgYnNYub7kBDSvwhxXSa/qNwrR7R+x2ukWqcr47Jighkf1p3MJ8x8FN1p9B84uzl7Js;3:3tl3hH4rlZb8IgSlo8ZavKvb5eWzgCw2cBi9Lf5vSICanxxVNM2se1tgGJj9qFZgUpwEnS/GTQunc91wwU1iaHWDLQ0f91DquXMqxyn0xYaJ4fCk1qoB3J9GbHoRFFNB;25:kSnqMYmZK5CenL/OtOTTfzwvwhebz/tQWzdPk1TYfbH3v9BUA3aVtnZIxSxRT3DtJzOgU5BPj3K3PJXAKQHfif1VRc91FsVuWAZKMKPOyC8SAg1efVDFqxeATnmw1K1avZbfoY3DYVay6WiE2clrWVZVp7g2S1JEQENisNMfP76SLc4VDz6PpWFoaNLyk6q7qdLiuEekQ/RpdqwuriKgj5PGJ9YySATpgF+gxGsDawab6KtjmfwgphBcHV2EucrLZ6VzImLiUb9mHC3W+Yz07P8rswh5Qfn0Ay2KYRAwP+WVCqKV9D7nbAJDt2xuK038oX8PdPUmXcMD3NvPR8802+Kr43hvpdDeZUgAFVW/c56eeRS8VkuRypysf9z0uQK2T8469QQEwhOpeLVTrIVyCf/7aEmu197kUWaMhHSb1N0= X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BN6PR11MB1635; X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1635;31:GRUFeqPraJEylUHUXZpJ9kzA75pWstja9I+Yp9fsC17ic0YCp5HVZ3bM0ev2EY+7BMrSlRGPg7cVeNcx1S1ol/qWp2ob8R91fYUDwVuIyDfSUeMI8kAHtFTGkVLL53AmYcj1LeAPltS8hJwcmoyxpYK6mUONxwZd4g6coWK9k10l0r364DcYcEp9mxxEruzOmMNMPlDx7PtSuiLFt6tejlKgRbX6PnbWg5KH3uYrdy0jGDK9/lDHvF2tC+QEyafA;4:7725MUJoD6RKyaOzI8yVWM3jE3ZLCUDusz0aJAjfDvjw1Xc9A8zAM/mFpFfXwUXnTZGY5Tf1nOSsKhJnXmdDHsrRGIV+1NmeJOUAvjwrEFKuV27QACWYISVEQGrdi7Uxtx2fwk/brvAvYrR85elgtDNlbkBKw3FCLWWuYRQjlygjn25Fpb3zmdxPDlFB0Sx3RnwLCsa2BP+7Y3UM0RJifOTAS7nNBV8Ja1q8sJZcIlrk2tjVt8eFuEIngUxbe0FERRVyP7lHKTTQOJRRDMTSuSG6YHWMMShPMM+8sabCPUyL5l6IL0ccMGqbdAhBmKwtih+WQpOXlq4g65Wy8xb3nP//C8q5bQxPQ5oyjO/uCw7IiZKid2Uxnplz3aAqmJaJx7BesMnzTrhpxt6tXe1YxWurxM/wI4rt5D8HoCBamSPaBusdXtByoDDWX6oLnySa8Zi3TyTLa9z95uXKj2ORb0kU1RISOeuz3cd0efON+zg= X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:(155532106045638); X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040176)(601004)(2401047)(5005006)(8121501046)(3002001)(10201501046)(6042046)(6043046);SRVR:BN6PR11MB1635;BCL:0;PCL:0;RULEID:;SRVR:BN6PR11MB1635; X-Forefront-PRVS: 008184426E X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(7916002)(24454002)(189002)(199003)(377454003)(83506001)(305945005)(54356999)(64126003)(81156014)(4001350100001)(19580395003)(8676002)(65826007)(105586002)(81166006)(68736007)(93886004)(5660300001)(31696002)(92566002)(7736002)(5001770100001)(33646002)(50466002)(7846002)(586003)(86362001)(97736004)(3846002)(50986999)(6666003)(230700001)(76176999)(15975445007)(189998001)(77096005)(66066001)(65806001)(6116002)(101416001)(4326007)(106356001)(551934003)(23676002)(42186005)(36756003)(47776003)(2950100002)(65956001)(16601075003)(31686004)(2906002)(85282002);DIR:OUT;SFP:1101;SCL:1;SRVR:BN6PR11MB1635;H:decina.local;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?MTtCTjZQUjExTUIxNjM1OzIzOmZOeFYxeG1hdUhsNWFRMlhKSGJEa25TZVFy?= =?utf-8?B?Y0IxOWRhSnR2ekY3VThvQ2NiT2NlZ0NRYkgyenlXREtlOU5VOXd3SzNVQXlt?= =?utf-8?B?cS9RQm8zcDByYmF6VUZtR1JqMEsyRVpIOFFPMWx3YjZiRjBlb1FkVUVreDF0?= =?utf-8?B?QVpuRXJ3a2JYRjlyNmwrL2x3ZUttTXIwSWxDeFdNTFkzOE5sR3lQZTBlZmF5?= =?utf-8?B?bGJxL01VQ2JHVFpWVEE4Z0JoeEdXM1NSdzgvTk5tM0pNWWZrRTBTZzM5Q0pk?= =?utf-8?B?UVB2dllYWkJUYkM2b0NOVUxIeFdiM1hCNWpPL0ZSTmlaWVp4dEFzWXdtRzIz?= =?utf-8?B?aHVVd3p0OUdseVY2WHRqbDZ4YjlCZlJsajZabzI4dmVvMG9tOXE5R25IOHZM?= =?utf-8?B?NVI5a0p0ZE44UDlxRTFTSi9veFVVNjdOQm1USCs2aDhpVVV5N0o0NGRCNnhH?= =?utf-8?B?dnVxbzFjOVNCWVE0Q2hlRUZZdzdWdy91N2ZodEhKdWFnOFlldGI1cm9WcHFk?= =?utf-8?B?OW1TZXZrYUY3Nllwb3dGVkx5TWl2T0lMZUpXQnNPeTZvc0VWaHJCQkRhZ29z?= =?utf-8?B?cFROSHlxWDlZbHZYelFmYzRPS0NDNUlLa3dVelZEUEhEVVYyQktOTjF5b3po?= =?utf-8?B?NG1mRlQxWjU4Vm1mZmx6NzVtVGowcnBSY1dFZFlYRkpDeklmV3g2ZUhmQUFD?= =?utf-8?B?T3dNODFXVWw4Z2htMlJvWlZQVVgvTkZGUXdSbDQ2NTYwaDUrZGZBQnRrQ1NI?= =?utf-8?B?dXA5M0ZJalErbEcvWFM4d2tzNWZxNlM5TFNmM3pDRzJGdUp0ZDhNYlNxVWRz?= =?utf-8?B?MFpCRCtOZ3VyU0lGSDd5aUFGN2RaUlVKWW03ZlB3NE9sWWFneVoyelRaelhN?= =?utf-8?B?ZW9hMmt4b0VTVEg2ejZ2UFJzVEJEUzU5Q213SXkyWDNpQ01jUG9SeURPaGdn?= =?utf-8?B?a05hQ3R1OWtKWWRRU0RiOEo4ZzNIS0lwckxwSWlkRGg1dWpzanozWXdQTDhF?= =?utf-8?B?ZTJvbGZXYkdDT1dYK0d1VnU1NWg5TmY0OGlEMUZIeUpiR1k1L1dFZTFWRS9I?= =?utf-8?B?czJlWTRIdXBiYjd6TVJINWJHNEovOHNnaFZHY2JrVHhNbkwvZkNrbmhoYW5s?= =?utf-8?B?OTBEY200WDJVZnpwUDIvWm1iKzhxeUdZUG1EcU5SQjBBQ1haT04yaHRubEV4?= =?utf-8?B?aTJqN2tlekhFZDd6ZE5icnlKM2c5YVJNYVhKN0RVNU9DTE1ibGZJRmlZMXBq?= =?utf-8?B?Ylp2S1o1enFhVUhwK0dVQXhXT202bStmU3lRb3Q5YlczREx6MkxDODMzVW91?= =?utf-8?B?VEZIbTBVbXk2VGYwYWtCSlBpb1AxU05icFZLNzJWMTBVOEU4VGNSL0xyMTR0?= =?utf-8?B?TDZiQ01iaVFXV2MwS2V1c0NEKzF6RFhRNkJvVjB0ZkVJV2ZMWUdYL1MxVjJr?= =?utf-8?B?a2NZSnJPcWxiWlpXclpNYjJvbk5DbUJpckttbUQ0OGxUeDBkVDNtaEtXTyts?= =?utf-8?B?K25JNmR6QlRCTWdWR1BqWTh2aWhXdjdXa2dnS2xYd01KaEVLb3Q2bGhmbE92?= =?utf-8?B?UTcydVYvWlh6QytkSjRnTHoyYkRzSWZRR29NY0FiRjhwZ3VaM2tvUytRak5W?= =?utf-8?B?NjFYYzdEajhSTVE0ZFFqWmsyS29HZlprWUNUK3hid1pzdnhseUxBQXlvUjBs?= =?utf-8?B?MTZjZkU4RnYvRmM0ckJQME90dEhMU3U4dUI2SnRsWVA5ZjdpWWlheXFjTlZG?= =?utf-8?B?blBlSEcrN29IbnBzdm9GQkhrYTR3R0NaRWhCY2RtQnV3YlFXUzdRZVp2UHRh?= =?utf-8?B?akNUei8vQzJpcGV6UWhiWkVxSEY2M2k1Z2ZBTnRmeHY1Y29NQ3B2TFlyQUhl?= =?utf-8?Q?TwyFrhNGoyZLBE2WZjdol54HrytqFLU8?= X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1635;6:TBWLD8K+Fbf925YLduBRivahhI9GjlfIHA4HW05m5sfRzsa5V0340Or+u4ZEgiT8mWsEAyk7Yto4M0bJwOSGxIcuYRXoG0lrmVuwUY/gdiL44VmycxDd39pSbgtCJQgEJ7kxswQWA7vQaGOWUmsPwnNgz1djmAVmnU0Gw/O+TB70wBtL3cTzcPV2cU6MuhZcbyUYUgNMClv14dIIIn8v2HIygwR8pmiTVzCDFNJHV3qHOsNPJ8FnawT3GzdQdr3Bssmfu16zlKFoDJhswK9eHxkk9srNvK/vWUQ96LZndW7Ddej27VLWDepLJyiyhXUb;5:WGe3BgX1Hh9q9+VbB60f9Vkp0o7B/dprBj+4HkKanGlebuooD3KuLUy3qFrHTPJZS3PWq4onrI92gbU9BxmHCLSrqwdKlaca3qQGCmjRX2IudQ0MYLvvuqG/uV6gHJoGax3Sm4tezwDuc56ODGaXfA==;24:ZeZCv+agJ00FXuD3Xu5KrKQ2ebRdwAzufqhoP99X/VkQV9TqfJlvki+vOkQDHgu6k97oHqjRB0SyCZBkiBzmPcIhVamqfSj4YopaY6/E4x0=;7:NtB0N5yq2TzZC7XiZ0XSVHuoYE4sxAIfolA77vCkGfUROKgIrtVX2kJkhP9icNXGiXnPGSDtB45IBW3tsVLz7AemrM0TRb6ZLoVUXDaVa4OM336YKDxgRtTk1kS1XpfBANjczcb0uugTjo+nsWdVhjXWJ2Q5zYhKJIylPgKPTrwiTouUM+8SYA3udz2lhKqBxsHCH8SBtbOExXgp+jeUBgVpFFl4S9muwZa7vY6vxcdQ42VKGQbxU0w49VoPfz8Vat06TJOuGK+6qpfpASL+uk7FQ/JgS8DiJQ3yFpOwheeZM0AlQgd65t3GcyVUDI2w SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 30 Sep 2016 22:20:04.7739 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN6PR11MB1635 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 9/28/16 1:11 PM, Jake Nielsen wrote: > Beautiful! After changing the random_page_cost to 1.0 the original query > went from ~3.5s to ~35ms. This is exactly the kind of insight I was > fishing for in the original post. I'll keep in mind that the query > planner is very tunable and has these sorts of hardware-related > trade-offs in the future. I can't thank you enough! Be careful with setting random_page_cost to exactly 1... that tells the planner that an index scan has nearly the same cost as a sequential scan, which is absolutely never the case, even with the database in memory. 1.1 or maybe even 1.01 is probably a safer bet. Also note that you can set those parameters within a single session, as well as within a single transaction. So if you need to force a different setting for a single query, you could always do BEGIN; SET LOCAL random_page_cost = 1; SELECT ... COMMIT; (or rollback...) -- 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