pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (6+ messages)

Message-ID: <18e3e4e6-2816-77fa-84c2-b85448fbe6ce@enterprisedb.com>
Permalink:  ../18e3e4e6-2816-77fa-84c2-b85448fbe6ce@enterprisedb.com/
Also on:    postgresql.org/message-id/18e3e4e6-2816-77fa-84c2-b85448fbe6ce@enterprisedb.com

 · 

reply

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