Re: Why hash join cost calculation need reduction

Hello:

Thanks for replying!

I understand it a little more.

And I compared the following statements:

First:

postgres=# explain analyze select * from sales s inner join customers c on s.cust_id = c.cust_id;

                                                    QUERY PLAN                                                   

------------------------------------------------------------------------------------------------------------------

 Hash Join  (cost=1.07..2.15 rows=3 width=84) (actual time=0.017..0.019 rows=3 loops=1)

   Hash Cond: (s.cust_id = c.cust_id)

   ->  Seq Scan on sales s  (cost=0.00..1.04 rows=4 width=42) (actual time=0.004..0.004 rows=4 loops=1)

   ->  Hash  (cost=1.03..1.03 rows=3 width=42) (actual time=0.004..0.004 rows=3 loops=1)

         Buckets: 1024  Batches: 1  Memory Usage: 1kB

         ->  Seq Scan on customers c  (cost=0.00..1.03 rows=3 width=42) (actual time=0.001..0.001 rows=3 loops=1)

 Total runtime: 0.046 ms

(7 rows)

Second:

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

                                                 QUERY PLAN                                                

------------------------------------------------------------------------------------------------------------

 Nested Loop  (cost=0.00..2.10 rows=1 width=84) (actual time=0.000..0.000 rows=1 loops=1)

   ->  Seq Scan on sales s  (cost=0.00..1.05 rows=1 width=42) (actual time=0.000..0.000 rows=1 loops=1)

         Filter: (cust_id = 2)

         Rows Removed by Filter: 3

   ->  Seq Scan on customers c  (cost=0.00..1.04 rows=1 width=42) (actual time=0.000..0.000 rows=1 loops=1)

         Filter: (cust_id = 2)

         Rows Removed by Filter: 2

 Total runtime: 0.000 ms

(8 rows)

Third:

postgres=# explain analyze select * from sales s inner join customers c on s.cust_id = c.cust_id and c.cust_id <4;

                                                 QUERY PLAN                                                 

------------------------------------------------------------------------------------------------------------

 Nested Loop  (cost=0.00..2.13 rows=1 width=84) (actual time=0.014..0.018 rows=3 loops=1)

   Join Filter: (s.cust_id = c.cust_id)

   Rows Removed by Join Filter: 9

   ->  Seq Scan on customers c  (cost=0.00..1.04 rows=1 width=42) (actual time=0.007..0.007 rows=3 loops=1)

         Filter: (cust_id < 4)

   ->  Seq Scan on sales s  (cost=0.00..1.04 rows=4 width=42) (actual time=0.000..0.000 rows=4 loops=3)

 Total runtime: 0.038 ms

(7 rows)

postgres=#

The first sql statement and third sql statment really drive the final_cost_hashjoin function to be called.

I think  For the above third one,

 cost_qual_eval(&hash_qual_cost, hashclauses, root)  is  for  <s.cust_id = c.cust_id>

And

cost_qual_eval(&qp_qual_cost, path->jpath.joinrestrictinfo, root) is for <s.cust_id = c.cust_id and c.cust_id <4>

I've  found the following calling relation:

hash_inner_and_outer  à try_hashjoin_path  à create_hashjoin_path à final_cost_hashjoin

For the second sql statement ,

In the hash_inner_and_outer function,

 the < if ( hashclauses) > condition is false,  So there is no chance to try a hashjoin path.

That is :

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 ?