Reg. evaluation of expression in HashCond - Mailing list pgsql-hackers

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.