Re: Why hash join cost calculation need reduction

* 高健 (luckyjackgao(at)gmail(dot)com) wrote:
[...]
> postgres=# explain analyze select * from sales s inner join customers c on
> s.cust_id = c.cust_id and c.cust_id =2;
[...]
> When I use the where condition such as <cust_id=2>,
>
> postgresql is clever enough to know it is better to make seqscan and
> filter ?

I havn't bothered to go look through the code specifics, but what I
expect is happening here is that PG realizes that c.cust_id and
s.cust_id are the same, and c.cust_id = 2, therefore the statement is
equivilant to:

explain analyze select * from sales s inner join customers c on
s.cust_id = 2 and c.cust_id = 2

and it's not going to try and build a hash to support an equality
operation against a constant- there's no point. It can simply do a
nested loop with a filter because all it needs to do is find all cases
of "sales.cust_id = 2" and all cases of "customers.cust_id = 2" and
return the cartesian product of them.

Thanks,

Stephen