Subject: Re: multivariate statistics (v19)
To: Dilip Kumar <dilipbalaut@gmail.com>
References: <5d1d62a6-6228-188c-e079-c1be59942168@2ndquadrant.com>
 <CAB7nPqS1obvpYL1_pt6i4XizWZZ96CEo_BOU4yTExr6RWG2TtQ@mail.gmail.com>
 <CAEZATCUtGR+U5+QTwjHhe9rLG2nguEysHQ5NaqcK=VbJ78VQFA@mail.gmail.com>
 <0ce73e37-7be4-8d9f-1ec4-46b0ca1d90c6@2ndquadrant.com>
 <1c7e4e63-769b-f8ce-f245-85ef4f59fcba@iki.fi>
 <CAEZATCV5ZPqvsbJJ77jr4R9beqd=xwVUnMwBkMeCw5zDdrqRNw@mail.gmail.com>
 <9f7d5c73-71d6-fbe0-c190-b321db46f88c@iki.fi>
 <CAEZATCWKEm1VhLdWFF5SPk7yVW0ZzH4MQFTxgDswopOwmVY+cw@mail.gmail.com>
 <277d9678-7a35-a746-0eb5-41d4bcd4ef55@2ndquadrant.com>
 <61e71067-9461-d785-b4a6-6e8a08996d5f@2ndquadrant.com>
 <8508ad05-54b9-f402-e736-e992ea014a32@lab.ntt.co.jp>
 <72eeb3d5-c406-93b0-8ff8-11b31789f683@2ndquadrant.com>
 <CAFiTN-scNndU0BiYUqyM2qvyuNLjWJvJ1=9gdA9SXwvKsw0ELQ@mail.gmail.com>
 <7c4b2088-5cbd-dfec-0b98-16e5a7db5308@2ndquadrant.com>
 <696aa95c-2411-9b2b-f36e-65b66bf47c88@2ndquadrant.com>
 <CAFiTN-vjNHSEWn9M5RqZQV7KWoFT97W=Nc14YikgUxbw2qcxDg@mail.gmail.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
 Dean Rasheed <dean.a.rasheed@gmail.com>, Heikki Linnakangas
 <hlinnaka@iki.fi>, Michael Paquier <michael.paquier@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>, Tatsuo Ishii <ishii@postgresql.org>,
 David Steele <david@pgmasters.net>, Tom Lane <tgl@sss.pgh.pa.us>,
 =?UTF-8?Q?=c3=81lvaro_Herrera?= <alvherre@2ndquadrant.com>,
 Petr Jelinek <petr@2ndquadrant.com>, Jeff Janes <jeff.janes@gmail.com>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <6f7ff2aa-b2b8-dbde-b39b-a9099f615466@2ndquadrant.com>
Date: Wed, 4 Jan 2017 22:57:09 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.4.0
MIME-Version: 1.0
In-Reply-To: 
 <CAFiTN-vjNHSEWn9M5RqZQV7KWoFT97W=Nc14YikgUxbw2qcxDg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 01/04/2017 03:21 PM, Dilip Kumar wrote:
> On Wed, Jan 4, 2017 at 8:05 AM, Tomas Vondra
> <tomas.vondra@2ndquadrant.com> wrote:
>> Attached is v22 of the patch series, rebased to current master and fixing
>> the reported bug. I haven't made any other changes - the issues reported by
>> Petr are mostly minor, so I've decided to wait a bit more for (hopefully)
>> other reviews.
>
> v22 fixes the problem, I reported.  In my test, I observed that group
> by estimation is much better with ndistinct stat.
>
> Here is one example:
>
> postgres=# explain analyze select p_brand, p_type, p_size from part
> group by p_brand, p_type, p_size;
>                                                       QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------
>  HashAggregate  (cost=37992.00..38992.00 rows=100000 width=36) (actual
> time=953.359..1011.302 rows=186607 loops=1)
>    Group Key: p_brand, p_type, p_size
>    ->  Seq Scan on part  (cost=0.00..30492.00 rows=1000000 width=36)
> (actual time=0.013..163.672 rows=1000000 loops=1)
>  Planning time: 0.194 ms
>  Execution time: 1020.776 ms
> (5 rows)
>
> postgres=# CREATE STATISTICS s2  WITH (ndistinct) on (p_brand, p_type,
> p_size) from part;
> CREATE STATISTICS
> postgres=# analyze part;
> ANALYZE
> postgres=# explain analyze select p_brand, p_type, p_size from part
> group by p_brand, p_type, p_size;
>                                                       QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------
>  HashAggregate  (cost=37992.00..39622.46 rows=163046 width=36) (actual
> time=935.162..992.944 rows=186607 loops=1)
>    Group Key: p_brand, p_type, p_size
>    ->  Seq Scan on part  (cost=0.00..30492.00 rows=1000000 width=36)
> (actual time=0.013..156.746 rows=1000000 loops=1)
>  Planning time: 0.308 ms
>  Execution time: 1001.889 ms
>
> In above example,
> Without MVStat-> estimated: 100000 Actual: 186607
> With MVStat-> estimated: 163046 Actual: 186607
>

Thanks. Those plans match my experiments with the TPC-H data set, 
although I've been playing with the smallest scale (1GB).

It's not very difficult to make the estimation error arbitrary large, 
e.g. by using perfectly correlated (identical) columns.

regard

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