From: Bill Measday <bill@measday.com>
To: Daniel Blanch Bataller <daniel.blanch.bataller@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Substantial different index use between 9.5 and 9.6
Date: Sun, 4 Dec 2016 07:42:02 +1100
Message-ID: <6586dbf1-3c1e-e7b1-bdae-c2a3d2914f49@measday.com> (raw)
In-Reply-To: <AB2D19D3-2900-453A-AC90-ECB5B3EF4205@gmail.com>
References: <c5d43892-7382-5105-67d0-c7800eac77f8@measday.com>
<21406.1480636092@sss.pgh.pa.us>
<c04c008a-0a79-7c7b-c09f-8ed3db3b36c5@measday.com>
<AB2D19D3-2900-453A-AC90-ECB5B3EF4205@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Seems to be a replicable issue in PostGis - ticket raised at their end,
so I'll wait for a resolution of the root cause.
Thanks for your help/thoughts.
Rgds
Bill
On 3/12/2016 2:41 AM, Daniel Blanch Bataller wrote:
> 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
> <https://www.postgresql.org/docs/current/static/sql-explain.html;.
> 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
> <https://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARG...; 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
> <https://www.postgresql.org/docs/current/static/sql-altertable.html;).
> 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-postgresql
>
> Check if this is the case.
>
>
>
>
>
>
>> El 2 dic 2016, a las 1:26, Bill Measday <bill@measday.com
>> <mailto:bill@measday.com>> escribió:
>>
>> 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 <mailto: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=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
>> <mailto:pgsql-performance@postgresql.org>)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bill@measday.com, daniel.blanch.bataller@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Substantial different index use between 9.5 and 9.6
In-Reply-To: <6586dbf1-3c1e-e7b1-bdae-c2a3d2914f49@measday.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox