Thread: Expression push down from Join Node to below node.

Expression push down from Join Node to below node.

From

Shubhankar Anand Kulkarni

Date:

22 May, 11:01:08

Hi Hackers,

While dealing with a few queries, I noticed that when the join expression (join clause) is used in projection as well, the expression will be computed twice.
For a better understanding, please take reference from the following example:

SELECT sensitive_data1, column1 FROM benchmark_encytion AS t1 LEFT JOIN ( SELECT aes256_cbc_decrypt( c1, '\x1234' :: bytea, '\x5678' :: bytea ) AS column1 FROM cipher ) AS t2 ON t1.sensitive_data1 = t2.column1;

As you can see in the above Query, the join clause involves the column which needs to be decrypted first and then joined with other table and then in projection we need the needed decrypted values to print as well, in this case the plan generated by the PG is as mentioned below (refer to the image as well):

QUERY PLAN

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

Hash Right Join (cost=22.74..73.43 rows=1 width=65)

Output: t1.sensitive_data1, aes256_cbc_decrypt(cipher.c1, '4696e67'::bytea, '6e67'::bytea)

Hash Cond: (aes256_cbc_decrypt(cipher.c1, '4696e67'::bytea, '6e67'::bytea) = t1.sensitive_data1)

-> Foreign Scan on public.cipher (cost=0.00..50.68 rows=1 width=49)

Output: cipher.c1, cipher.c2, cipher.c3, cipher.c4, cipher.c5, cipher.c6

CStore Dir: /home/shubha/Documents/zoho/postgres17/data/cstore_fdw/116838/116931

CStore Table Size: 2424 kB

-> Hash (cost=22.72..22.72 rows=1 width=33)

Output: t1.sensitive_data1

-> Foreign Scan on public.benchmark_encytion t1 (cost=0.00..22.72 rows=1 width=33)

Output: t1.sensitive_data1

CStore Dir: /home/shubha/Documents/zoho/postgres17/data/cstore_fdw/116838/135230

CStore Table Size: 1268 kB

Query Identifier: 1810637692808683603

(14 rows)

As seen in the plan, join clause uses aes256_cbc_decrypt funcExpr to join columns and we are selecting the same as projection from hasjJoin node resulting in computing the expr twice, which is very costly.

My doubt here is, while planing this join, why can't we parse the join clause and pass the expressions involved there to the respective scan nodes and use it above that wherever needed as a Var?
In this particular case, we can push_down the expression (decrypt funcExpr) from join clause to the foreign scan of cipher table. Why have we not handled this case in PG?
Pls share your thoughts on the same, also pls correct me if my understanding is wrong here.

Thanks and Regards.
Shubhankar Kulkarni
ZLabs-CStore

Attachment

Sample_case_for_expr_push_down.png

Re: Expression push down from Join Node to below node.

From

Andy Fan

Date:

22 May, 11:51:51

Shubhankar Anand Kulkarni <shubhankar.ak@zohocorp.com> writes:

Hi,

> SELECT sensitive_data1, column1 FROM benchmark_encytion AS t1 LEFT JOIN ( SELECT aes256_cbc_decrypt( c1, '\x1234' ::

> bytea, '\x5678' :: bytea ) AS column1 FROM cipher ) AS t2 ON t1.sensitive_data1 = t2.column1; 
>
> As you can see in the above Query, the join clause involves the column which needs to be decrypted first and then
joined
> with other table and then in projection we need the needed decrypted values to print as well, in this case the plan
> generated by the PG is as mentioned below (refer to the image as well):
>
>                                                                                                        QUERY PLAN

>                                                                                                 
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Hash Right Join  (cost=22.74..73.43 rows=1 width=65)
>    Output: t1.sensitive_data1, aes256_cbc_decrypt(cipher.c1, '4696e67'::bytea, '6e67'::bytea)
>    Hash Cond: (aes256_cbc_decrypt(cipher.c1, '4696e67'::bytea, '6e67'::bytea) = t1.sensitive_data1)
>    ->  Foreign Scan on public.cipher  (cost=0.00..50.68 rows=1 width=49)
>          Output: cipher.c1, cipher.c2, cipher.c3, cipher.c4, cipher.c5, cipher.c6
>          CStore Dir: /home/shubha/Documents/zoho/postgres17/data/cstore_fdw/116838/116931
>          CStore Table Size: 2424 kB
>    ->  Hash  (cost=22.72..22.72 rows=1 width=33)
>          Output: t1.sensitive_data1
>          ->  Foreign Scan on public.benchmark_encytion t1  (cost=0.00..22.72 rows=1 width=33)
>                Output: t1.sensitive_data1
>                CStore Dir: /home/shubha/Documents/zoho/postgres17/data/cstore_fdw/116838/135230
>                CStore Table Size: 1268 kB
> Query Identifier: 1810637692808683603
> (14 rows)
>
> My doubt here is, while planing this join, why can't we parse the join clause and pass the expressions involved there
to
> the respective scan nodes and use it above that wherever needed as a
> Var?

I guess the reason would be once we push the function down to the "foreign scan"
node, we need to run these function *before any other filter*, which may
increase the number of calls of the function. e.g.

SELECT udf1(t1.a) FROM t1_1000row t1, t2_1row t2 where t2.fid = t1.id;

If we push down the udf1 to the timing of scaning t1, udf1 would be
called 1000 times, but without the push down, it is called 1 times in
the above case. IIRC, PostgreSQL assumes after the join, the total rows
will be less. 

To your case especially,

1. the number call of  aes256_cbc_decrypt will not be increased even we
push down, however figuring out this fact needs some work being done in
the very early of planing stage. which might be kind of complex. 

2. You can simply rewrite your query with materialized cte, I think
that probably resolve your issue.

WITH t2 MATERIALIZED as
(SELECT aes256_cbc_decrypt( c1, '\x1234' ::bytea, '\x5678' :: bytea ) AS
column1 FROM cipher)
SELECT sensitive_data1, column1 FROM
benchmark_encytion AS t1 left join t2
on t1.sensitive_data1 = t2.column1; . 

For a general case,  I do want to share some intermediate result between
ExecQual and ExecProject by storing the intermediate result into some
special tts_values in TupleTableSlot. e.g. the case is:

SELECT costly_udf(f1.a) FROM t1 JOIN t2 WHERE costly_udf(f1.a) = f2.a;

In the past I want to use similar idea to bypass some duplicated detoast
effort at [1], but Robert thought it was unaccptable, then the project
is dead. Your case makes me think about it again.

[1]
https://www.postgresql.org/message-id/CA%2BTgmoZfpruG%3DVvqeKLiRC95VbbxEyxBm8d1r3YOpaedkQuL4A%40mail.gmail.com

-- 
Best Regards
Andy Fan