Re: Expression push down from Join Node to below node. - Mailing list pgsql-hackers

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