Message-ID: <5230DBAE.20108@archidevsys.co.nz>
Date: Thu, 12 Sep 2013 09:07:58 +1200
From: Gavin Flower <GavinFlower@archidevsys.co.nz>
Organization: ArchiDevSys
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130805 Thunderbird/17.0.8
MIME-Version: 1.0
To: Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
CC: pgsql-performance@postgresql.org
Subject: Re: Reasons for choosing one execution plan over another?
References: <fa55f02db8800ec874cd3ffa714639f4@localhost>
 <5230A08A.7050001@2ndquadrant.it>
In-Reply-To: <5230A08A.7050001@2ndquadrant.it>
Content-Type: multipart/alternative;
 boundary="------------050203050209010302080000"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------050203050209010302080000
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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).

--------------050203050209010302080000
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 12/09/13 04:55, Giuseppe Broccolo
      wrote:<br>
    </div>
    <blockquote cite="mid:5230A08A.7050001@2ndquadrant.it" type="cite">Il
      11/09/2013 13:16, Mikkel Lauritsen ha scritto:
      <br>
      <blockquote type="cite">Hi all,
        <br>
        <br>
        I have a number of Postgres 9.2.4 databases with the same schema
        but with
        <br>
        slightly different contents, running on small servers that are
        basically
        <br>
        alike (8-16 GB ram).
        <br>
        <br>
      </blockquote>
      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.
      <br>
      <br>
      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.
      <br>
      <br>
       Giuseppe.
      <br>
      <br>
    </blockquote>
    <font size="-1">Even identical content could lead to different
      plans, as the sampling </font>is done randomly (or at least
    'randomly' according to the documentation).<br>
  </body>
</html>

--------------050203050209010302080000--