Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cCq06-0008KE-1u for pgsql-performance@arkaria.postgresql.org; Fri, 02 Dec 2016 15:43:50 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cCq05-0005dZ-17 for pgsql-performance@arkaria.postgresql.org; Fri, 02 Dec 2016 15:43:49 +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 1cCpyO-0003jX-CN for pgsql-performance@postgresql.org; Fri, 02 Dec 2016 15:42:04 +0000 Received: from mail-wm0-x243.google.com ([2a00:1450:400c:c09::243]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cCpyK-0003Cv-OR for pgsql-performance@postgresql.org; Fri, 02 Dec 2016 15:42:03 +0000 Received: by mail-wm0-x243.google.com with SMTP id m203so3300176wma.3 for ; Fri, 02 Dec 2016 07:42:00 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=BJBCpTW/2x5tNGmlus/OMFZ8kW8g6x2lmMZ2nBfLVMA=; b=meTcuIuuADc1oY4SRz/vEOKPTlF7uWERqHaGsL55USp/CUnR+kxJNTOBHYUba/NoMm 9Hwp/zSBDMoJN/L67cPquQZHWkgDfaNM922yB3IoLXrkrq+DnrRFQS/13ZdTZLsqCduT iBiHhOKkPsNDnSsIhOTXIrHxr4oHrY2RcJWGpC2teOzn1X/dsuNIHODwABeGbAB8gn6V NWkQVk+5sfXwMeb9E1t1tZX29ETEKY5tAMDAfdcFA6bfbencpXjRAexqErod6qfX+LfJ UsxvGeizrjoa5m1rMgoKT9+th4eqcFG/V6SJs6eM7jQiDnpn8N3L260aw5YF9aEd7Voi iOZg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=BJBCpTW/2x5tNGmlus/OMFZ8kW8g6x2lmMZ2nBfLVMA=; b=MzSt5m7CPHMtgZ2IX99A1Ztd5W+TPnWAkLfHMHqDHeqmcCgytWE8XkGHkQIa08qTBq jJeGgfWOY6/GDH9K2szRsLiBs6uiDBkYVMdos9ovZdUSMAucASPusTFTYUvBrXJ/UD4L QWiG0MQBxtKAf28NrT+3NxhH+/rqINhLiQQzSAZUgj+tHwyv1OF5jza9njR4+Q6OBMP/ r0kRg/VrsTFZom7KM6KDIU7ddx8iNqKggxt4CLWk5aKmfSR8msRgvVgOGCPxz1riUyfC xiLqisHdxqHHzQVxBC7ppuTFj8h8WwKVb82XhPhEGojvWHtp9CmrI/Nd7SwxXDppdxs6 FAlg== X-Gm-Message-State: AKaTC03SZlmrkADopGcpApGL47RFewOohU70TQFpxz+uMJid2w3ySWRxVpLFzMH0ve10Hw== X-Received: by 10.28.18.194 with SMTP id 185mr3433437wms.124.1480693319662; Fri, 02 Dec 2016 07:41:59 -0800 (PST) Received: from [192.168.1.110] ([90.77.173.136]) by smtp.gmail.com with ESMTPSA id x188sm3818263wmx.4.2016.12.02.07.41.58 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 02 Dec 2016 07:41:58 -0800 (PST) From: Daniel Blanch Bataller Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_7E9E5E1D-0B2B-4A88-B94B-4C711AD983EB" Mime-Version: 1.0 (Mac OS X Mail 10.1 \(3251\)) Subject: Re: Substantial different index use between 9.5 and 9.6 Date: Fri, 2 Dec 2016 16:41:56 +0100 In-Reply-To: Cc: Tom Lane , pgsql-performance@postgresql.org To: Bill Measday References: <21406.1480636092@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3251) X-Pg-Spam-Score: -2.7 (--) 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 --Apple-Mail=_7E9E5E1D-0B2B-4A88-B94B-4C711AD983EB Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 ANALYZE takes samples at random, so statistics might be different even = with same postgresql version: https://www.postgresql.org/docs/current/static/sql-analyze.html For large tables, ANALYZE takes a random sample of the table contents, = rather than examining every row. This allows even very large tables to = be analyzed in a small amount of time. Note, however, that the = statistics are only approximate, and will change slightly each time = ANALYZE is run, even if the actual table contents did not change. This = might result in small changes in the planner's estimated costs shown by = EXPLAIN = . In = rare situations, this non-determinism will cause the planner's choices = of query plans to change after ANALYZE is run. To avoid this, raise the = amount of statistics collected by ANALYZE, as described below. Though, having that round (x 1000) difference, my bet is that you have = different statistics target whether on database, table or columns, see: The extent of analysis can be controlled by adjusting the = default_statistics_target = configuration variable, or on a = column-by-column basis by setting the per-column statistics target with = ALTER TABLE ... ALTER COLUMN ... SET STATISTICS (see ALTER TABLE = ). = The target value sets the maximum number of entries in the = most-common-value list and the maximum number of bins in the histogram. = The default target value is 100, but this can be adjusted up or down to = trade off accuracy of planner estimates against the time taken for = ANALYZE and the amount of space occupied in pg_statistic. In particular, = setting the statistics target to zero disables collection of statistics = for that column. It might be useful to do that for columns that are = never used as part of the WHERE, GROUP BY, or ORDER BY clauses of = queries, since the planner will have no use for statistics on such = columns. Here is some help on how to see statistics per column: = http://stackoverflow.com/questions/15034622/check-statistics-targets-in-po= stgresql = Check if this is the case. > El 2 dic 2016, a las 1:26, Bill Measday escribi=C3=B3= : >=20 > Thanks Tom. >=20 > First, this wasn't a migration but new db loaded from scratch (if that = matters). >=20 > As per the end of the original post "I have vacuum analysed both = tables". I assume this is what you meant? >=20 > 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. >=20 > Rgds >=20 >=20 > Bill >=20 > 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: >>=20 >>> -> Bitmap Heap Scan on m_elevations e >>> (cost=3D282802.21..37401439.43 rows=3D3512160 width=3D8) >>> -> Seq Scan on m_elevations e >>> (cost=3D10000000000.00..13296950520.12 rows=3D3512159563 width=3D8) >> 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. >>=20 >> regards, tom lane >=20 >=20 >=20 > --=20 > Sent via pgsql-performance mailing list = (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance --Apple-Mail=_7E9E5E1D-0B2B-4A88-B94B-4C711AD983EB Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
ANALYZE takes samples at random, so = statistics might be different even with same postgresql = version:

https://www.postgresql.org/docs/current/static/sql-analyze.html=

For= large tables, ANALYZE takes a random sample of the table contents, rather = than examining every row. This allows even very large tables to be = analyzed in a small amount of time. Note, however, that the statistics = are only approximate, and will change slightly each time ANALYZE is run, even if the actual table contents did not = change. This might result in small changes in the planner's estimated = costs shown by EXPLAIN. = In rare situations, this non-determinism will cause the planner's = choices of query plans to change after ANALYZE is run. To avoid this, raise the = amount of statistics collected by ANALYZE, as described = below.
Though, having that round (x 1000) difference, my bet is that = you have different statistics target whether on database, table or = columns, see:

The= extent of analysis can be controlled by adjusting the default_statistics_target configuration variable, or on a = column-by-column basis by setting the per-column statistics target = with ALTER = TABLE ... ALTER COLUMN ... SET STATISTICS (see ALTER TABLE). = The target value sets the maximum number of entries in the = most-common-value list and the maximum number of bins in the histogram. = The default target value is 100, but this can be adjusted up or down to = trade off accuracy of planner estimates against the time taken = for ANALYZE and the amount of space occupied in pg_statistic. = In particular, setting the statistics target to zero disables collection = of statistics for that column. It might be useful to do that for columns = that are never used as part of the WHERE, GROUP BY, or ORDER BY clauses of queries, since the = planner will have no use for statistics on such = columns.
Here is some help on how to see statistics per = column:

http://stackoverflow.com/questions/15034622/check-statistics-ta= rgets-in-postgresql

Check if this is the case.




El 2 dic 2016, a las 1:26, Bill Measday = <bill@measday.com> escribi=C3=B3:

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 <bill@measday.com> 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=3D282802.21..37401439.43 = rows=3D3512160 width=3D8)
=       ->  Seq Scan on m_elevations = e
(cost=3D10000000000.00..13296950520.12 rows=3D3512159563 = width=3D8)
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

= --Apple-Mail=_7E9E5E1D-0B2B-4A88-B94B-4C711AD983EB--