On Mon, Oct 26, 2020 at 03:58:05PM +0000, Ehrenreich, Sigrid wrote:
> Hi Performance Guys,
> 
> I hope you can help me. I am joining two tables, that have a foreign key 
> relationship. So I expect the optimizer to estimate the number of the 
> resulting rows to be the same as the number of the returned rows of one of 
> the tables. But the estimate is way too low.
> 
> I have built a test case, where the problem is easily to be seen.

I reproduced the problem on v14dev.

Note the different estimates between these:

postgres=# explain analyze SELECT * FROM fact INNER JOIN dim USING 
(low_card,anydata1,anydata2) WHERE fact.low_card=2;
 Hash Join  (cost=161.58..358.85 rows=112 width=12) (actual time=8.707..15.717 
rows=3289 loops=1)

postgres=# explain analyze SELECT * FROM fact INNER JOIN dim USING 
(low_card,anydata1,anydata2) WHERE fact.low_card BETWEEN 2 AND 2;
 Hash Join  (cost=324.71..555.61 rows=3289 width=12) (actual 
time=15.966..23.394 rows=3289 loops=1)

I think because low_card has an equality comparison in addition to the equijoin,
it's being disqualified from the planner's mechanism to consider FKs in join
selectivity.
https://doxygen.postgresql.org/costsize_8c_source.html#l05024

I don't know enough about this to help more than that.


Reply via email to