From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Dilip Kumar <dilipbalaut@gmail.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Tatsuo Ishii <ishii@postgresql.org>
Cc: David Steele <david@pgmasters.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Álvaro Herrera <alvherre@2ndquadrant.com>
Cc: Petr Jelinek <petr@2ndquadrant.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: multivariate statistics (v19)
Date: Wed, 4 Jan 2017 22:57:09 +0100
Message-ID: <6f7ff2aa-b2b8-dbde-b39b-a9099f615466@2ndquadrant.com> (raw)
In-Reply-To: <CAFiTN-vjNHSEWn9M5RqZQV7KWoFT97W=Nc14YikgUxbw2qcxDg@mail.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, dilipbalaut@gmail.com, Langote_Amit_f8@lab.ntt.co.jp, dean.a.rasheed@gmail.com, hlinnaka@iki.fi, michael.paquier@gmail.com, robertmhaas@gmail.com, ishii@postgresql.org, david@pgmasters.net, tgl@sss.pgh.pa.us, alvherre@2ndquadrant.com, petr@2ndquadrant.com, jeff.janes@gmail.com
Subject: Re: multivariate statistics (v19)
In-Reply-To: <6f7ff2aa-b2b8-dbde-b39b-a9099f615466@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox