public inbox for [email protected]
help / color / mirror / Atom feedFrom: Vignesh K <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: Reg. evaluation of expression in HashCond
Date: Tue, 25 Jan 2022 09:44:54 +0530
Message-ID: <[email protected]> (raw)
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.
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Reg. evaluation of expression in HashCond
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox