Subject: Re: multivariate statistics v14
To: Tatsuo Ishii <ishii@postgresql.org>
References: <060fe904-7210-cc5a-65d7-02f6e8f0cb1e@2ndquadrant.com>
 <26597.1458941172@sss.pgh.pa.us>
 <ef0d75d9-f8fd-5a81-850e-e5e42579fba8@2ndquadrant.com>
 <20160326.181833.1902389187691662331.t-ishii@sraoss.co.jp>
Cc: tgl@sss.pgh.pa.us, alvherre@2ndquadrant.com, petr@2ndquadrant.com,
 jeff.janes@gmail.com, pgsql-hackers@postgresql.org
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <95089064-e388-2cd1-ab62-7c88890eaf67@2ndquadrant.com>
Date: Mon, 28 Mar 2016 10:42:28 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:46.0) Gecko/20100101
 Thunderbird/46.0a2
MIME-Version: 1.0
In-Reply-To: <20160326.181833.1902389187691662331.t-ishii@sraoss.co.jp>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

Hi,

On 03/26/2016 10:18 AM, Tatsuo Ishii wrote:
>> Fair point. Attached is v18 of the patch, after pgindent cleanup.
>
> Here are some feedbacks to v18 patch.
>
> 1) regarding examples in create_statistics manual
>
> Here are numbers I got. "with statistics" referrers to the case where
> multivariate statistics are used.  "without statistics" referrers to the
> case where multivariate statistics are not used. The numbers denote
> estimated_rows/actual_rows. Thus closer to 1.0 is better. Some numbers
> are shown as a fraction to avoid 0 division. In my understanding case
> 1, 3, 4 showed that multivariate statistics superior.
>
> 	with statistics	without statistics
> case1	0.98		0.01
> case2	98/0		1/0

The case2 shows that functional dependencies assume that the conditions 
used in queries won't be incompatible - that's something this type of 
statistics can't fix.

> case3	1.05		0.01
> case4	1/0		103/0
> case5	18.50		18.33
> case6	111123/0	1111123/0

The last two lines (case5 + case6) seem a bit suspicious. I believe 
those are for the histogram data, and I do get these numbers:

case5    0.93 (5517 / 5949)         42.0 (249943 / 5949)
case6    100/0                      100/0

Perhaps you've been using the version before the bugfix, with ANALYZE on 
the wrong table?

>
> 2) following comments by me are not addressed in the v18 patch.
>
>> - There's no docs for pg_mv_statistic (should be added to "49. System
>>   Catalogs")
>>
>> - The word "multivariate statistics" or something like that should
>>   appear in the index.
>>
>> - There are some explanation how to deal with multivariate statistics
>>   in "14.1 Using Explain" and "14.2 Statistics used by the Planner"
>>   section.

Yes, those are valid omissions. I plan to address them, and I'd also 
considering adding a section to 65.1 (How the Planner Uses Statistics), 
explaining more thoroughly how the planner uses multivariate stats.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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