Thread: Reg. evaluation of expression in HashCond

Reg. evaluation of expression in HashCond

From
Vignesh K
Date:
Hi,

      I Recently noted that expressions involved in either side of HashCondition in HashJoin is not being pushed down to foreign scan. This leads to evaluation of the same expression multiple times - (for hashvalue computation from hashkeys, for HashCondition expr evaluation, for Projection). Not sure if intended behavior is to not push down expressions in HashCond. Kindly clarify this case. Have attached sample plan for reference.

 contrib_regression=# explain verbose select x_vec.a*2, y_vec.a*2 as a from x_vec, y_vec where x_vec.a*2 = y_vec.a*2 and x_vec.a*2 != 10;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Hash Join  (cost=2.09..4.40 rows=4 width=12)
   Output: (x_vec.a * 2), (y_vec.a * 2)
   Hash Cond: ((x_vec.a * 2) = (y_vec.a * 2))
   ->  Foreign Scan on public.x_vec  (cost=0.00..2.18 rows=12 width=4)
         Output: x_vec.a, x_vec.b
         Filter: ((x_vec.a * 2) <> 10)
         CStore Dir: /home/test/postgres/datasets11/cstore_fdw/452395/453195
         CStore Table Size: 28 kB
   ->  Hash  (cost=2.04..2.04 rows=4 width=8)
         Output: y_vec.a
         ->  Foreign Scan on public.y_vec  (cost=0.00..2.04 rows=4 width=8)
               Output: y_vec.a
               CStore Dir: /home/test/postgres/datasets11/cstore_fdw/452395/453068
               CStore Table Size: 28 kB
(14 rows)
      Here the same expression is being used in HashCond, Projection. Since its not being pushed down to Scan its being evaluated multiple times for HashValue, HashCond and Projection.
Have used a simple expression for an example. If the expression is complex, query execution slows down due to this.

The same is also being done even if the expression is used in multiple levels.
contrib_regression=# explain verbose select * from (select x_vec.a*2 as xa2, y_vec.a*2 as ya2 from x_vec, y_vec where x_vec.a*2 = y_vec.a*2) q1 join a on q1.xa2 = a.a;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Hash Join  (cost=4.37..8.51 rows=2 width=28)
   Output: (x_vec.a * 2), (y_vec.a * 2), a.a, a.b
   Hash Cond: (a.a = (x_vec.a * 2))
   ->  Foreign Scan on public.a  (cost=0.00..4.07 rows=7 width=16)
         Output: a.a, a.b
         CStore Dir: /home/test/postgres/datasets11/cstore_fdw/452395/453149
         CStore Table Size: 28 kB
   ->  Hash  (cost=4.32..4.32 rows=4 width=12)
         Output: x_vec.a, y_vec.a
         ->  Hash Join  (cost=2.09..4.32 rows=4 width=12)
               Output: x_vec.a, y_vec.a
               Hash Cond: ((x_vec.a * 2) = (y_vec.a * 2))
               ->  Foreign Scan on public.x_vec  (cost=0.00..2.12 rows=12 width=4)
                     Output: x_vec.a, x_vec.b
                     CStore Dir: /home/test/postgres/datasets11/cstore_fdw/452395/453195
                     CStore Table Size: 28 kB
               ->  Hash  (cost=2.04..2.04 rows=4 width=8)
                     Output: y_vec.a
                     ->  Foreign Scan on public.y_vec  (cost=0.00..2.04 rows=4 width=8)
                           Output: y_vec.a
                           CStore Dir: /home/test/postgres/datasets11/cstore_fdw/452395/453068
                           CStore Table Size: 28 kB
(22 rows)


Thanks and regards,
Vignesh K.