Message-ID: <5193EE89.1090406@archidevsys.co.nz>
Date: Thu, 16 May 2013 08:22:33 +1200
From: Gavin Flower <GavinFlower@archidevsys.co.nz>
Organization: ArchiDevSys
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130509 Thunderbird/17.0.6
MIME-Version: 1.0
To: Heikki Linnakangas <hlinnakangas@vmware.com>
CC: sthomas@optionshouse.com,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Thinking About Correlated Columns (again)
References: <5193AA62.2040700@optionshouse.com> <5193AF36.3040409@vmware.com>
In-Reply-To: <5193AF36.3040409@vmware.com>
Content-Type: multipart/alternative;
	boundary="------------010006000501070207020702"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------010006000501070207020702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 16/05/13 03:52, Heikki Linnakangas wrote:
> On 15.05.2013 18:31, Shaun Thomas wrote:
>> I've seen conversations on this since at least 2005. There were even
>> proposed patches every once in a while, but never any consensus. Anyone
>> care to comment?
>
> Well, as you said, there has never been any consensus.
>
> There are basically two pieces to the puzzle:
>
> 1. What metric do you use to represent correlation between columns?
>
> 2. How do use collect that statistic?
>
> Based on the prior discussions, collecting the stats seems to be 
> tricky. It's not clear for which combinations of columns it should be 
> collected (all possible combinations? That explodes quickly...), or 
> how it can be collected without scanning the whole table.
>
> I think it would be pretty straightforward to use such a statistic, 
> once we have it. So perhaps we should get started by allowing the DBA 
> to set a correlation metric manually, and use that in the planner.
>
> - Heikki
>
>
How about pg comparing actual numbers of rows delivered with the 
predicted number - and if a specified threshold is reached, then 
maintaining statistics? There is obviously more to it, such as: is this 
a relevant query to consider & the size of the tables (no point in 
attempting to optimise tables with only 10 rows for example).


Cheers,
Gavin

--------------010006000501070207020702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 16/05/13 03:52, Heikki Linnakangas
      wrote:<br>
    </div>
    <blockquote cite="mid:5193AF36.3040409@vmware.com" type="cite">On
      15.05.2013 18:31, Shaun Thomas wrote:
      <br>
      <blockquote type="cite">I've seen conversations on this since at
        least 2005. There were even
        <br>
        proposed patches every once in a while, but never any consensus.
        Anyone
        <br>
        care to comment?
        <br>
      </blockquote>
      <br>
      Well, as you said, there has never been any consensus.
      <br>
      <br>
      There are basically two pieces to the puzzle:
      <br>
      <br>
      1. What metric do you use to represent correlation between
      columns?
      <br>
      <br>
      2. How do use collect that statistic?
      <br>
      <br>
      Based on the prior discussions, collecting the stats seems to be
      tricky. It's not clear for which combinations of columns it should
      be collected (all possible combinations? That explodes
      quickly...), or how it can be collected without scanning the whole
      table.
      <br>
      <br>
      I think it would be pretty straightforward to use such a
      statistic, once we have it. So perhaps we should get started by
      allowing the DBA to set a correlation metric manually, and use
      that in the planner.
      <br>
      <br>
      - Heikki
      <br>
      <br>
      <br>
    </blockquote>
    <font size="-1">How about pg comparing actual <font size="-1">numbers
        of rows delivered with the predicted number</font></font> - and
    if a specified threshold is reached, then maintaining statistics?
    There is obviously more to it, such as: is this a relevant query to
    consider &amp; the size of the tables (no point in attempting to
    optimise tables with only 10 rows for example).<br>
    <br>
    <br>
    Cheers,<br>
    Gavin<br>
  </body>
</html>

--------------010006000501070207020702--