Message-ID: <55B9DEA6.6030603@iki.fi>
Date: Thu, 30 Jul 2015 11:21:58 +0300
From: Heikki Linnakangas <hlinnaka@iki.fi>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Icedove/31.7.0
MIME-Version: 1.0
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
CC: Jeff Janes <jeff.janes@gmail.com>, Stephen Frost <sfrost@snowman.net>,
 Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Subject: Re: multivariate statistics / patch v7
References: <54C3FED3.1060600@2ndquadrant.com>
	<20150320.173307.249113775.horiguchi.kyotaro@lab.ntt.co.jp>
	<550C8E50.2010600@2ndquadrant.com>
	<20150324.143427.34552953.horiguchi.kyotaro@lab.ntt.co.jp>
	<5519E9B2.5080105@2ndquadrant.com>
	<CAMkU=1znGN-6-j9GAAM7XqgAYPNBfOMBWq5_6Hzym8bdVB4_iw@mail.gmail.com>
	<20150428161310.GR30322@tamriel.snowman.net>
 <CAMkU=1xrzZt3YGiux0tNkvXKGu4Jt3proMS+_X+UQhpB+qQ2Hw@mail.gmail.com>
 <553FCE56.2000008@2ndquadrant.com> <554A73A6.2060603@2ndquadrant.com>
 <55638982.40904@2ndquadrant.com>
In-Reply-To: <55638982.40904@2ndquadrant.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 05/25/2015 11:43 PM, Tomas Vondra wrote:
> There are 6 files attached, but only 0002-0006 are actually part of the
> multivariate statistics patch itself.

All of these patches are huge. In order to review this in a reasonable 
amount of time, we need to do this in several steps. So let's see what 
would be the minimal set of these patches that could be reviewed and 
committed, while still being useful.

The main patches are:

1. shared infrastructure and functional dependencies
2. clause reduction using functional dependencies
3. multivariate MCV lists
4. multivariate histograms
5. multi-statistics estimation

Would it make sense to commit only patches 1 and 2 first? Would that be 
enough to get a benefit from this?

I have some doubts about the clause reduction and functional 
dependencies part of this. It seems to treat functional dependency as a 
boolean property, but even with the classic zipcode and city case, it's 
not always an all or nothing thing. At least in some countries, there 
can be zipcodes that span multiple cities. So zipcode=X does not 
completely imply city=Y, although there is a strong correlation (if 
that's the right term). How strong does the correlation need to be for 
this patch to decide that zipcode implies city? I couldn't actually see 
a clear threshold stated anywhere.

So rather than treating functional dependence as a boolean, I think it 
would make more sense to put a 0.0-1.0 number to it. That means that you 
can't do clause reduction like it's done in this patch, where you 
actually remove clauses from the query for cost esimation purposes. 
Instead, you need to calculate the selectivity for each clause 
independently, but instead of just multiplying the selectivities 
together, apply the "dependence factor" to it.

Does that make sense? I haven't really looked at the MCV, histogram and 
"multi-statistics estimation" patches yet. Do those patches make the 
clause reduction patch obsolete? Should we forget about the clause 
reduction and functional dependency patch, and focus on those later 
patches instead?

- Heikki


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