Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1c25Bd-00026S-0R for pgsql-performance@arkaria.postgresql.org; Wed, 02 Nov 2016 23:43:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1c25Bc-00081C-Cb for pgsql-performance@arkaria.postgresql.org; Wed, 02 Nov 2016 23:43:16 +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 1c25Bb-00080t-P9 for pgsql-performance@postgresql.org; Wed, 02 Nov 2016 23:43:15 +0000 Received: from mail-bl2nam02on0077.outbound.protection.outlook.com ([104.47.38.77] helo=NAM02-BL2-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 1c25BX-0002ik-J0 for pgsql-performance@postgresql.org; Wed, 02 Nov 2016 23:43:15 +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=/INZN+DmsHyJJJuDJABaat2+fdtVbZJrhx9hkdBIuwc=; b=PfHonkiCdoDLkMOXnzb1rp2WYIhu+cbPXgTRNi29mWG98ONP3X1FjLbJ4NOlaMkfAWb5loLy1A+Rzl4msglHic9Yk3ZAUYyIrAyrJPXI45X4Yj4Kj1o1u2bnMYFifpRlEr6yNCydi0iUobnYfIKusyWnUEfVd60MqkWSoudTMH0= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.local (70.113.18.142) by CY4PR11MB1638.namprd11.prod.outlook.com (10.172.71.14) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.679.12; Wed, 2 Nov 2016 23:43:08 +0000 Subject: Re: query slowdown after 9.0 -> 9.4 migration To: =?UTF-8?Q?Filip_Rembia=c5=82kowski?= , Andreas Kretschmer References: <20161027053838.GA5952@tux> CC: postgres performance list From: Jim Nasby Message-ID: Date: Wed, 2 Nov 2016 18:43:02 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0) Gecko/20100101 Thunderbird/45.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 8bit X-Originating-IP: [70.113.18.142] X-ClientProxiedBy: BY1PR14CA0027.namprd14.prod.outlook.com (10.161.91.37) To CY4PR11MB1638.namprd11.prod.outlook.com (10.172.71.14) X-MS-Office365-Filtering-Correlation-Id: f3604c48-4861-4212-68f9-08d40379ffea X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1638;2:5hxMAjgiKKvyiGFb29wI5SMH2/zpefA3W4KEfhwfXA+NaHfdZwRgCX9ZRhXxbHkm2PGDOyVttroVqLYoKGCzlupAB3gXiWWkQx0sndSG5EIUfo75DUiqhNKZ2wUPnINGmSqJs/l38StP6Mm+Tw+d8l9q9bnh7IQ5yZ0p2m5GY5VlQqtrCq3W/FxdnmL7khWYwsSY4FK/y0Y0Fwf1wsrbZg==;3:nm2bQM05NkJ7EDTLtBeifuj2iO4RgPWJr3pd4cDi5tPIc6EEq6qePmf84TOLk1GUjapLNZletCXKgDTtfNp8k84q4RISNVtqnQlKYCnIMiqSsFMwTGdYSF7O3UPu1V//QbvE7gjYrFN6vORHHdxD7w==;25:bNKj9CyzOU7s6upk0M+6xyaMvYGEa2W8mQGq25vRAkfS/qhwYqjz4K4qnmiEbf31d7iiP987dNMbLdChkNvIqFdefUxxYzrotWFhKUJa0OvYwRk1Mk/uafbkPfnTqetTE/Aa5K0qsuyasMN8nQPNkw6XRwu8+0sydaRQpuAyzPV8aX02OKdA2Ws2jHKrRHAbY/JoKNRcDGz1JBGHQwBtZIyEedhMJh6Pyl0bRO+cH7gAmpMPL3J/FBlTXfV4/6/xZDtWgO2ywVXrDQi203lnaVaun2pgSbR7P5KkHtlceBwT+/+QTpb2qdIsg96ucDEegoJ44woQ85BRAw7vzCn0ywcR9y2TBjeZe1lN0erqvtTVZgdlOYK7ldkjyDQ8gGWM5526ipPEA1cO1pgi5qHcCs9IDzwQKNkocxv8idwO7JmfZ8c2cHNWlpb+jxV/2yTA X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1638; X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1638;31:ObYdip6m1iUFAW3b/4d3hDNfbOT8C+LRCxN0H7H/eDhZy0pciFlyPhLZdUm2tEESj7L4D4t1YBT0C4OJYn6Ej3tAJz8wTs4lWgc/RbCKqajSDIlkjeCpqkvZxfpWAg5CsrldU82i6AkTNnEPElQL/9kxVyjxcrS9TlpYvBxiX6VgznH0Scn+p2JVQSpD9arvG8KD8It/fKxRQ8M2Rupc0Q88pm+0SzhzhXnhbRY6G78mBRWDdJhcFlXoJzmLVWkCsMZ0UWCkRD2dywmXE9Q53Q==;4:ombuurr9bKhpQV70qwUIRqdA1FeYHcwqmSoJQ1AyqCfh9xn+6V+4J4paruBkjsVpJEC3Hw96CnNSO1ubJd45k7lp1/gECFutmrGJQm6QMT7SEhn0ss0xbPcJ68L14oZ/e0eTK8vlAf9Yc4JOILvTXJ9FFzVBcu4EH/MBzIooHsCjLxOM8ExEsHvbQUVwhIxQrpTzN7aYUjo9a3HGDUxcItsR55eQDz2rBcpf5ZcbxMrSZVFmmHB9Lja/DZI5U/21y7e/FAFptXlSio+kg8zBh70IEN2OR8vaPSPkCZGPeB3nwD8Rsx+JjWMEK2iDP1yPwJ+aAijFWsib2G7h1d5UqB4UOCzNd0wsiAaWtyxKZE/zDKXLyiUXKb5nZNDhv2C9712coMjwdtafIFnbV/s9uke+Qvuhbt5MjX0/LwgFc3yFhGp6CCJCqKeMJUjLMQGV 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)(10201501046)(3002001)(6043046)(6042046);SRVR:CY4PR11MB1638;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1638; X-Forefront-PRVS: 0114FF88F6 X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(7916002)(377454003)(189002)(199003)(24454002)(83506001)(31686004)(105586002)(77096005)(66066001)(305945005)(36756003)(4326007)(2950100002)(47776003)(65956001)(65806001)(6116002)(5660300001)(2906002)(68736007)(65826007)(86362001)(50466002)(92566002)(3846002)(31696002)(106356001)(64126003)(93886004)(33646002)(42186005)(15975445007)(8676002)(16601075003)(76176999)(2870700001)(586003)(23676002)(81166006)(54356999)(7846002)(4001350100001)(6666003)(189998001)(97736004)(7736002)(50986999)(5001770100001)(81156014)(19580395003)(101416001)(85282002);DIR:OUT;SFP:1101;SCL:1;SRVR:CY4PR11MB1638;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?MTtDWTRQUjExTUIxNjM4OzIzOkd1RjhCem1OSk5KaEJqeExjMTB5VmVDM3o4?= =?utf-8?B?NkI1VXBja2RLZmV1YVVXZ0Y2cVNpZEJ0U0FUOExEY0hiSlpVQnJOY0doeDJr?= =?utf-8?B?UjROK0NZdWNWYnlaMnRHVmdVTDFJTTNyK3J3cE5BbGxqNFU1RGswSUI0SERT?= =?utf-8?B?Q0FqS3c3RXhhN1hSV1ZqcTB1WXZVL1EwMjdGSGFrQkNUWm56ZThLczlKQXly?= =?utf-8?B?MTVGSHM4RURTQVRoSk9ZemRITkxFa2JtbGNFQmJYTm1ZOXFCcFdEM3l6UDND?= =?utf-8?B?MUFnU1lTN3RrN2pyakV1SEtHSThkVktWclRCZ2crcTByNmRJNDZuYklHREVU?= =?utf-8?B?SkRERERKUXVYTVpCbGtET3Z3WlRRTXRrVSsyOTJ2Nk1pc1RNTzJudkVEblVQ?= =?utf-8?B?bGowdTBsbGxPa0dUVSs4ZlYrRjY2VTJUWFZwVHlBQTl0d0U0aFllNjdzS05n?= =?utf-8?B?RDgxb1VNVVRpWEdPOS91Z2JOWHRSZG50REFLaG1nT3FQU01pWFVWaUlGYmEy?= =?utf-8?B?N2gwTjZ3YUlHK0xXZ3VMc01uT292YmhlTmQwOUU5VkgxRmpRMVBCbzRLZmUy?= =?utf-8?B?NGpFQ2tQc240SzdFRjUxSk0zd2YzODBYeU9TRk8wbmRnaGdBb1Y2bzNGbmwx?= =?utf-8?B?d3FVeGpLTnBvUzZ3Nk9VWml6d3FOVlk3N205MFhQTEVLSEl6YVNRSWUwQW9W?= =?utf-8?B?OFlyQnJuRmQyMENVdmFWN09KZ2x5Vml6N3Jtalc4dHM4NTZHSnh1U2UyQ21V?= =?utf-8?B?U0pTKzFNSW56dG9heE5ERDJYQ0hnNjA0RzlCT1VSYmNHeVJmUU1IQTlVVFhH?= =?utf-8?B?TTdBSzJxRGJEUjVVRnRKOGpyKzBZbnZYSUwzd0Q3MXVVbTI2QWZ3dUNOSGlm?= =?utf-8?B?cTJWM1dQamtqb2VkRW5ZVHhMYUNSUFZNRVJsRFI2aWlWcWhnaGdjelQ1Ylpo?= =?utf-8?B?bVI4NStyeDdLRlkxYnROaENSWE9JRUJaN1lyN2l4QXpPZm9lZzUrbXpRZGw3?= =?utf-8?B?ZUJJOWxtakxHNWxWa3R4UVJGMEY0eWNPamM2aEJESG9wZjJicC9Db2JKQjEr?= =?utf-8?B?cjFURUhRU1RrWkV1RXNjYSsrRkRmc3F6TGN0RURGY2NDSHNUOWFxSGEyMy9P?= =?utf-8?B?V2s1M2UraElJU09NNmI4Wm93c3oxazhKK1I1bGxrdzg1Ylc2aHdHRUlFU3N3?= =?utf-8?B?dUkrd2hxTFJna09HQUw2cWVPMFhFTzRWdjJHTU9MSVhCUFROVWU2VVRVNnY4?= =?utf-8?B?YldiK1BTZFY3T0gzTWcvdUw4c0ZDMCsvRWM5YkgwRWJzZFNmK085L0VlUFRu?= =?utf-8?B?eHQyOFhuSlJ3eVc3d3RXdHB4V05sTTlpNlNTOXJtUFN3RzMwZEgyWmVzWlEw?= =?utf-8?B?VDVUMlRuVDBybHZqSEJQMmpweVNIeUxGWnJNNFdramVtWTNROVdqRHhaV3R1?= =?utf-8?B?bTVrVEMzKy8yeFd2Zm5tQ3U0SEZwby82Yy9aSUNybldLYUZnVWovd1U3Mytk?= =?utf-8?B?Q3JiUWhVV1JmYVBGZnd6NFVxRisxMHU3M3lncGZEbURpdktxajNnUzBCRkU1?= =?utf-8?B?c0NsWHJYbnFOYU5QZnpsYnNVd24rd3ZhUmJCbkNaczE4ZlE3QU8rZzVqZlN1?= =?utf-8?B?YUpqTkk5aU1mUnNjNWxudmF5RDVyNCt2M0M4bndyOHgxdXFVL2hLcEdSN1RP?= =?utf-8?B?b3hTOVZZK2xpTC9SbDZ2ZVFyWG9LaSt1WWZRaXpkNW5hMVFvWUl5d1gzR1Ux?= =?utf-8?B?NTJCWi8xMk5iTytEa3A2ZG15TFg2eWhFUzRRY3NOUThUZ2NpRC9vOHZIWEc5?= =?utf-8?B?aEdFUWYxeFA2MFQ3MkxIMUlqd0hqdDkzR1lJbzdPRXpUdGw3UEtsQno2Mmxm?= =?utf-8?Q?4io7UmwPgeg=3D?= X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1638;6:k6DZiOB+oaXPfSbI0eKW5FJ5HpcC1eCyp62OhGLFDy18EA12U4sZmZtxXic2mTE55Hl9yi1wtYhuj41qbBw9RxCg4ylbd7qqwv7/oa7pZrl0v8gBa/QAC1myXeu1tdU6+0Su37LuFdkl73zV6Dsf9sFo5944D/1v8xW2NdIbWtrhABNErKOUW9ze4Q46uL2NLWtdejE7ssoviPxDMB6pP6GmXeGIBp7DcQfyvJindUavxRaLc2I5P+gJ3sG3Sc39oJQToRautxjFc/lp+ltR3etQU5ydTAKlwSzjoJar4BOx0rIeUUlS4kjC2kjag7vnmQbYwJt0lDuUWzPuJNErGg==;5:0PYiakfoLANvZhzmQXotiRP0tAuNH353A3rkWQVg/GSwVamVLyJu00N1oui0pjJPCstaqCgRiqOehttEYVobzp+9nMHRf8xwPd1ga1FDMiuJYBGQUq4+XLyrN4q7xznCILPaNqYmm7wtwGCSZc/mOQ==;24:x2HeOIiBDBnDcmrkFgHwzI/Rugzbt+DbUyw9wNbREGlEO2qDwo7KxTVysB36jqLx+fWTvKNCYMRl0oaRD9ZWgzlgTj6IImX5JXxw2hnGBgU= SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1638;7:T737JCGWwVW/1c+lMSKUn0znlsEvKnh8Rv5yJ7HvTDk0B/Sra13n52ZXSV5PWXlfL4BCKRGYC+HeHkmmHRyHqxHO/60TTRJ8b7LBjKx6y+R5yzscjiDYGZ92DjXQpyDaU9gxCCrfTVUAAK55jYTU7bNe/+Qt+EUi917S4UobdlCc2NR4XbJIqJHxZLJZ6fkX6Wldg4ZEq9R3YCgyTN357z9/omTxXRHrgVPU5bpFMHx6Y/uymY9N8IPCFcfM3HvBBnXdm66Hg/oKFuFKdtItBTC7Y1Z/Pbq4Z090jDAU4t2YoqAOH9g/1YpvaJIxfU2cuqUsdg42NmdSj1oofhS0FN2F8KZFnnAO8TcgPzL3XTo= X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 02 Nov 2016 23:43:08.0946 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY4PR11MB1638 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/16 8:37 PM, Filip Rembiałkowski wrote: > Does it make sense to ask on postgis-users list? Yes. I suspect that the reason Buffers: shared hit is so high is because of something st_distance_sphere() is doing. -- 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