Message-ID: <5193BA52.7040007@dunslane.net>
Date: Wed, 15 May 2013 12:39:46 -0400
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:15.0) Gecko/20120827 Thunderbird/15.0
MIME-Version: 1.0
To: Craig James <cjames@emolecules.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>
	<CAFwQ8rcSrwfJRgtP9GV_t-hKRR4=PmVeqCuGJ7wEnnO-9PD0wA@mail.gmail.com>
In-Reply-To: 
 <CAFwQ8rcSrwfJRgtP9GV_t-hKRR4=PmVeqCuGJ7wEnnO-9PD0wA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On 05/15/2013 12:23 PM, Craig James wrote:
> On Wed, May 15, 2013 at 8:31 AM, Shaun Thomas 
> <sthomas@optionshouse.com <mailto:sthomas@optionshouse.com>> wrote:
>
>     [Inefficient plans for correlated columns] has been a pain point
>     for quite a while. While we've had several discussions in the
>     area, it always seems to just kinda trail off and eventually
>     vanish every time it comes up.
>
>     ...
>     Since there really is no fix for this aside from completely
>     rewriting the query or horribly misusing CTEs (to force sequence
>     scans instead of bloated nested loops, for example)...
>     I'm worried that without an easy answer for cases like this, more
>     DBAs will abuse optimization fences to get what they want and
>     we'll end up in a scenario that's actually worse than query hints.
>     Theoretically, query hints can be deprecated or have GUCs to
>     remove their influence, but CTEs are forever, or until the next
>     code refactor.
>
>     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?
>
>
> It's a very hard problem.  There's no way you can keep statistics 
> about all possible correlations since the number of possibilities is 
> O(N^2) with the number of columns.
>

I don't see why we couldn't allow the DBA to specify some small subset 
of the combinations of columns for which correlation stats would be 
needed. I suspect in most cases no more than a handful for any given 
table would be required.

cheers

andrew


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance