Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cCbgD-0005Ux-PB for pgsql-performance@arkaria.postgresql.org; Fri, 02 Dec 2016 00:26: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 1cCbgC-0003ij-SZ for pgsql-performance@arkaria.postgresql.org; Fri, 02 Dec 2016 00:26:20 +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 1cCbgB-0003iE-Oh for pgsql-performance@postgresql.org; Fri, 02 Dec 2016 00:26:19 +0000 Received: from pecan2-mail.exetel.com.au ([220.233.0.71] helo=pecan2.exetel.com.au) by magus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cCbg8-0001JH-F6 for pgsql-performance@postgresql.org; Fri, 02 Dec 2016 00:26:18 +0000 Received: from 131.95.233.220.static.exetel.com.au ([220.233.95.131] helo=gandalf.measday.com) by pecan2.exetel.com.au with esmtp (Exim 4.84) (envelope-from ) id 1cCbg2-0007E6-Qj; Fri, 02 Dec 2016 11:26:10 +1100 Received: from localhost (localhost [127.0.0.1]) by gandalf.measday.com (Postfix) with ESMTP id 9996C80E047F; Fri, 2 Dec 2016 11:26:10 +1100 (AEDT) X-Virus-Scanned: amavisd-new at measday.com Received: from gandalf.measday.com ([IPv6:::1]) by localhost (gandalf.measday.com [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id nVtbwpQtjqZf; Fri, 2 Dec 2016 11:26:10 +1100 (AEDT) Received: from [10.26.68.210] (android-d33517d905c49d4e.measday.com [10.26.68.210]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by gandalf.measday.com (Postfix) with ESMTPSA id 5069E809F2F3; Fri, 2 Dec 2016 11:26:10 +1100 (AEDT) Subject: Re: Substantial different index use between 9.5 and 9.6 To: Tom Lane References: <21406.1480636092@sss.pgh.pa.us> Cc: pgsql-performance@postgresql.org From: Bill Measday Message-ID: Date: Fri, 2 Dec 2016 11:26:09 +1100 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:45.0) Gecko/20100101 Thunderbird/45.5.0 MIME-Version: 1.0 In-Reply-To: <21406.1480636092@sss.pgh.pa.us> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 1.0 (+) 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 Thanks Tom. First, this wasn't a migration but new db loaded from scratch (if that matters). As per the end of the original post "I have vacuum analysed both tables". I assume this is what you meant? My gut feel was that it isn't a postgis issue since the third example I gave uses the index, but I will take it up with them too. Rgds Bill On 2/12/2016 10:48 AM, Tom Lane wrote: > Bill Measday writes: >> Substantial different index use between 9.5 and 9.6 > Maybe you missed an ANALYZE after migrating? The plan difference > seems to be due to a vast difference in rowcount estimate for the > m_elevations condition: > >> -> Bitmap Heap Scan on m_elevations e >> (cost=282802.21..37401439.43 rows=3512160 width=8) >> -> Seq Scan on m_elevations e >> (cost=10000000000.00..13296950520.12 rows=3512159563 width=8) > If you don't know where that factor-of-1000 came from, maybe take > it up with the postgis folk. It'd mostly be coming out of their > selectivity estimation routines. > > regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance