From: | Vignesh K <vignesh(dot)kr(at)zohocorp(dot)com> |
---|---|
To: | "pgsql-hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Reg. evaluation of expression in HashCond |
Date: | 2022-01-25 04:14:54 |
Message-ID: | 17e8f719b8e.dd71207e6620.3953270461742247370@zohocorp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Yura Sokolov | 2022-01-25 04:35:45 | Re: Fix BUG #17335: Duplicate result rows in Gather node |
Previous Message | Dilip Kumar | 2022-01-25 04:14:35 | Re: autovacuum prioritization |