From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: Reasons for choosing one execution plan over another?
Date: Thu, 12 Sep 2013 09:07:58 +1200
Message-ID: <5230DBAE.20108@archidevsys.co.nz> (raw)
In-Reply-To: <5230A08A.7050001@2ndquadrant.it>
References: <fa55f02db8800ec874cd3ffa714639f4@localhost>
<5230A08A.7050001@2ndquadrant.it>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 12/09/13 04:55, Giuseppe Broccolo wrote:
> Il 11/09/2013 13:16, Mikkel Lauritsen ha scritto:
>> Hi all,
>>
>> I have a number of Postgres 9.2.4 databases with the same schema but
>> with
>> slightly different contents, running on small servers that are basically
>> alike (8-16 GB ram).
>>
> I think that your answer can be found in your statement "slightly
> different contents". Planner choices query execution plans basing on
> statistics obtained during ANALYSE operations, including the
> autovacuum. In this way, Planner can decide which execution plan is
> the most suitable. Different content of values in your table could
> correspond to different statistical distribution of values in your
> columns and of rows in your tables, bringing to different choices of
> the Planner. Execution times can be very different, also by factor
> 10-100.
>
> There is a parameter (stat_target) which set the "selectivity" of
> statistical samples of a table. Maybe, but it's not necessarily true,
> you could obtain more comparable execution times for the two execution
> plans changing it, probably increasing them.
>
> Giuseppe.
>
Even identical content could lead to different plans, as the sampling is
done randomly (or at least 'randomly' according to the documentation).
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: GavinFlower@archidevsys.co.nz, giuseppe.broccolo@2ndquadrant.it
Subject: Re: Reasons for choosing one execution plan over another?
In-Reply-To: <5230DBAE.20108@archidevsys.co.nz>
* 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