From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Bertrand Guillaumin <bertrand.guillaumin@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16759: Estimation of the planner is wrong for hash join
Date: Fri, 18 Dec 2020 02:14:54 +0100
Message-ID: <18e3e4e6-2816-77fa-84c2-b85448fbe6ce@enterprisedb.com> (raw)
In-Reply-To: <CAC-tRewKy9hQZiqaWknvHR4ZCK8m0EJoRNQeFJ5sJ9RBFNsoyQ@mail.gmail.com>
References: <16759-f0980add03d8f152@postgresql.org>
<1672657.1606940979@sss.pgh.pa.us>
<CAC-tRezpoX=Jn7XGJz4KM6=o_3+RKgoH3BQigf9bBbRy7Opzeg@mail.gmail.com>
<1677883.1606948395@sss.pgh.pa.us>
<CAC-tRewx4SOyU6jrGb5KpWq-8Njre6NP-54Y-7+3H31B6W2FLg@mail.gmail.com>
<CAC-tRewjTRf7y0iBjWK7MgM5bsgB0qCJuFXBLAKE73+dCPT=iA@mail.gmail.com>
<CAC-tRewKy9hQZiqaWknvHR4ZCK8m0EJoRNQeFJ5sJ9RBFNsoyQ@mail.gmail.com>
On 12/17/20 6:36 PM, Bertrand Guillaumin wrote:
> Hello,
> I think I just made a test that shows that even with MCV on both sides
> the estimated selectivity can be pretty wrong.
> test=# create table test_bug_hash2 as SELECT mod(id,500) as id , case
> when id<=500 then 1 else 2 end parent_id, null::text as attrib from
> (select generate_series(1,1000) as id) alias0;
> SELECT 1000
> test=# update test_bug_hash2 set attrib='BEL' where id=2;
> UPDATE 2
> test=# analyze test_bug_hash2;
> ANALYZE
> test=# explain select * from test_bug_hash2 a, test_bug_hash2 b where
> a.parent_id=b.id <http://b.id; and b.attrib='BEL';
> QUERY PLAN
> ------------------------------------------------------------------------------
> Hash Join (cost=17.52..37.56 rows=4 width=24)
> Hash Cond: (a.parent_id = b.id <http://b.id;)
> -> Seq Scan on test_bug_hash2 a (cost=0.00..15.00 rows=1000 width=12)
> -> Hash (cost=17.50..17.50 rows=2 width=12)
> -> Seq Scan on test_bug_hash2 b (cost=0.00..17.50 rows=2
> width=12)
> Filter: (attrib = 'BEL'::text)
>
> test=# select count(*) from test_bug_hash2 a, test_bug_hash2 b where
> a.parent_id=b.id <http://b.id; and b.attrib='BEL';
> count
> -------
> 1000
>
> I won't copy paste the pg_stats lines but most_common_vals and
> most_common_freq have values for all three columns.
>
> I'm not a programmer but I've looked into the code of the planner a
> little bit and it seems you try to estimate the selectivity of a join in
> itself without any regards to the filters that can be applied on any
> side of the join ( if I understood correctly).
> I think that it's ultimately where the problem lies, if the filter is
> not too important the selectivity stays more or less the same but with
> filters like the one in this query the selectivity of the join can
> change a lot so in the end you get estimations which can be totally wrong.
>
>
Right. The problem is that the two columns are correlated, thanks to how
you set the attrib value only for id=2, but the join estimation code is
oblivious to that.
Perhaps the multi-column/extended stats might allow us to improve this,
but the code has not been written yet.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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-bugs@postgresql.org
Cc: tomas.vondra@enterprisedb.com, bertrand.guillaumin@gmail.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16759: Estimation of the planner is wrong for hash join
In-Reply-To: <18e3e4e6-2816-77fa-84c2-b85448fbe6ce@enterprisedb.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