Re: BUG #16759: Estimation of the planner is wrong for hash join

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