Thread

1. Expression push down from Join Node to below node.

   Shubhankar Anand Kulkarni <shubhankar.ak@zohocorp.com> — 2025-05-22T08:01:08Z

   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

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

   Andy Fan <zhihuifan1213@163.com> — 2025-05-22T08:51:51Z

   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
   
   
   
   
   

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

   Shubhankar Anand Kulkarni <shubhankar.ak@zohocorp.com> — 2025-05-26T11:28:12Z

   Hi Andy,
   
   Thanks for the reply. As suggested, I tried using CTE which seems to solve the case. This is the updated plan: 
   
   
                                                                                                  QUERY PLAN                                                                                                
   
   ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   
   Hash Right Join  (cost=2500502930.90..2501167775.90 rows=10000000 width=65) (actual time=1450.743..12200.977 rows=10000000 loops=1)
   
      Output: t1.sensitive_data1, t2.column1
   
      Hash Cond: (t2.column1 = t1.sensitive_data1)
   
      CTE t2
   
        ->  Foreign Scan on public.cipher  (cost=0.00..2500163471.33 rows=10000000 width=32) (actual time=0.095..6288.407 rows=10000000 loops=1)
   
              Output: aes256_cbc_decrypt(cipher.c1, '\x323032356b6579666f726373746f726561657332353663626374657374696e67'::bytea, '\x323032356373746f72656976666f7261657332353663626374657374696e67'::bytea)
   
              Read blocks: 2442 Skipped: 0
   
              File Mode: File Per Table
   
              CStore Dir: /home/shubha-18887/Documents/zoho/postgres17/data/cstore_fdw/116838/116931
   
              CStore Table Size: 2975 MB
   
      ->  CTE Scan on t2  (cost=0.00..200000.00 rows=10000000 width=32) (actual time=0.097..7626.693 rows=10000000 loops=1)
   
            Output: t2.column1
   
      ->  Hash  (cost=136334.57..136334.57 rows=10000000 width=33) (actual time=1448.676..1448.678 rows=10000000 loops=1)
   
            Output: t1.sensitive_data1
   
            Buckets: 131072  Batches: 128  Memory Usage: 5999kB
   
            ->  Foreign Scan on public.benchmark_encytion t1  (cost=0.00..136334.57 rows=10000000 width=33) (actual time=0.143..513.782 rows=10000000 loops=1)
   
                  Output: t1.sensitive_data1
   
                  Read blocks: 2442 Skipped: 0
   
                  File Mode: File Per Table
   
                  CStore Dir: /home/shubha-18887/Documents/zoho/postgres17/data/cstore_fdw/116838/135230
   
                  CStore Table Size: 1987 MB
   
   Query Identifier: 2330480792947742169
   
   Planning Time: 3.829 ms
   
   Execution Time: 12401.482 ms
   
   (24 rows)
   
   
   
   
   
   As seen, the expression got pushed down to respective foreign scan, reducing the overall query time reduced significantly, but there is an increase in the memory footprint.
   
   
   
   Also the thing which you mentioned, 
   
   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.
   
   
   After looking into the code, it looks like we will first evaluate the qual, if it qualifies then only we will go for the projection. Meaning, even after expression push-down we will evaluate the expression on top filtered rows only. So I don't think it should have any concern in this case.
   
   
   Also for this particular case, I tried playing around create_hashjoin_plan function in createplan.c, to push my hash-clause expression to below foreign-scan. Currently, if there is a single hash clause of form funcExpr op Var, I am appending the funcExpr to pathTarget of  respective table and replacing the funcExpr with a var. Is it a right place/approach to check or should we do changes while parsing in deconstruct_jointree(), in distiribute_quals_to_rels we can  update the appropriate baserestrictinfo. 
   Pls share your thoughts on the same.
   
   
   
   
   
   
   Thanks & Regards
   Shubhankar K
   ZLabs-CStore
   
   
   
   
   
   
   
   ---- On Thu, 22 May 2025 14:21:51 +0530 Andy Fan <zhihuifan1213@163.com> wrote ---
   
   
   
   Shubhankar Anand Kulkarni < mailto: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

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

   Shubhankar Anand Kulkarni <shubhankar.ak@zohocorp.com> — 2025-05-28T18:23:46Z

   Can you please resolve the doubt mentioned in the below thread?
   
   Thanks &Regards
   
   
   Shubhankar K
   Zlabs-CStore
   Member of Technical Staff, Zoho
   
   
   
   
   
   
   
   ---- On Mon, 26 May 2025 16:58:12 +0530 Shubhankar Anand Kulkarni <shubhankar.ak@zohocorp.com> wrote ---
   
   
   
   Hi Andy,
   
   Thanks for the reply. As suggested, I tried using CTE which seems to solve the case. This is the updated plan: 
   
   
                                                                                                  QUERY PLAN                                                                                                
   
   ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   
   Hash Right Join  (cost=2500502930.90..2501167775.90 rows=10000000 width=65) (actual time=1450.743..12200.977 rows=10000000 loops=1)
   
      Output: t1.sensitive_data1, t2.column1
   
      Hash Cond: (t2.column1 = t1.sensitive_data1)
   
      CTE t2
   
        ->  Foreign Scan on public.cipher  (cost=0.00..2500163471.33 rows=10000000 width=32) (actual time=0.095..6288.407 rows=10000000 loops=1)
   
              Output: aes256_cbc_decrypt(cipher.c1, '\x323032356b6579666f726373746f726561657332353663626374657374696e67'::bytea, '\x323032356373746f72656976666f7261657332353663626374657374696e67'::bytea)
   
              Read blocks: 2442 Skipped: 0
   
              File Mode: File Per Table
   
              CStore Dir: /home/shubha-18887/Documents/zoho/postgres17/data/cstore_fdw/116838/116931
   
              CStore Table Size: 2975 MB
   
      ->  CTE Scan on t2  (cost=0.00..200000.00 rows=10000000 width=32) (actual time=0.097..7626.693 rows=10000000 loops=1)
   
            Output: t2.column1
   
      ->  Hash  (cost=136334.57..136334.57 rows=10000000 width=33) (actual time=1448.676..1448.678 rows=10000000 loops=1)
   
            Output: t1.sensitive_data1
   
            Buckets: 131072  Batches: 128  Memory Usage: 5999kB
   
            ->  Foreign Scan on public.benchmark_encytion t1  (cost=0.00..136334.57 rows=10000000 width=33) (actual time=0.143..513.782 rows=10000000 loops=1)
   
                  Output: t1.sensitive_data1
   
                  Read blocks: 2442 Skipped: 0
   
                  File Mode: File Per Table
   
                  CStore Dir: /home/shubha-18887/Documents/zoho/postgres17/data/cstore_fdw/116838/135230
   
                  CStore Table Size: 1987 MB
   
   Query Identifier: 2330480792947742169
   
   Planning Time: 3.829 ms
   
   Execution Time: 12401.482 ms
   
   (24 rows)
   
   
   
   
   
   As seen, the expression got pushed down to respective foreign scan, reducing the overall query time reduced significantly, but there is an increase in the memory footprint.
   
   
   
   Also the thing which you mentioned, 
   
   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.
   
   
   After looking into the code, it looks like we will first evaluate the qual, if it qualifies then only we will go for the projection. Meaning, even after expression push-down we will evaluate the expression on top filtered rows only. So I don't think it should have any concern in this case.
   
   
   Also for this particular case, I tried playing around create_hashjoin_plan function in createplan.c, to push my hash-clause expression to below foreign-scan. Currently, if there is a single hash clause of form funcExpr op Var, I am appending the funcExpr to pathTarget of  respective table and replacing the funcExpr with a var. Is it a right place/approach to check or should we do changes while parsing in deconstruct_jointree(), in distiribute_quals_to_rels we can  update the appropriate baserestrictinfo. 
   Pls share your thoughts on the same.
   
   
   
   
   
   
   Thanks & Regards
   
   Shubhankar K
   ZLabs-CStore
   
   
   
   
   
   
   
   ---- On Thu, 22 May 2025 14:21:51 +0530 Andy Fan < mailto:zhihuifan1213@163.com > wrote ---
   
   
   
   
   
   
   
   
   
   
   
   
   Shubhankar Anand Kulkarni < mailto: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

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

   Andy Fan <zhihuifan1213@163.com> — 2025-05-30T01:23:13Z

   Shubhankar Anand Kulkarni <shubhankar.ak@zohocorp.com> writes:
   
   Hi, 
   
   > As seen, the expression got pushed down to respective foreign scan, reducing the overall query time reduced
   > significantly, but there is an increase in the memory footprint.
   
   That's great on the query time improvement. When you say the memory
   footprint or we have two scans because of the CTE (one is build the cte,
   one is the cte scan), I admit it is not good, hence there is a better
   result for your query.
   
   
   > Also the thing which you mentioned, 
   >
   >  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.
   >
   > After looking into the code, it looks like we will first evaluate the qual, if it qualifies then only we will go for the
   > projection. Meaning, even after expression push-down we will evaluate the expression on top filtered rows only. So I
   > don't think it should have any concern in this case.
   
   I am not sure which case you are talking about. In my previously answer,
   I have a example and explaination for this idea, but you didn't metion
   it when you were replying it.
   
   """
   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 (*based on t1_1000row has
   1000 rows*), it is called 1 times in the above case (*based on afrer the
   join, there are only 1 row returned *).
   
   IIRC, PostgreSQL assumes after the join, the total rows will be less. 
   """
   >
   > Also for this particular case, I tried playing around create_hashjoin_plan function in createplan.c, to push my
   > hash-clause expression to below foreign-scan. Currently, if there is a single hash clause of form funcExpr op Var, I am
   > appending the funcExpr to pathTarget of  respective table and replacing the funcExpr with a var. Is it a right
   > place/approach to check or should we do changes while parsing in deconstruct_jointree(), in distiribute_quals_to_rels we
   > can  update the appropriate baserestrictinfo. 
   > Pls share your thoughts on the same.
   
   Hash Cond invovles two sides of the relations, how could we push down
   it under one side of it? I can't follow up on this well, have you a draft
   plan / code for your idea? 
   
   -- 
   Best Regards
   Andy Fan
   
   
   
   
   

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

   Shubhankar Anand Kulkarni <shubhankar.ak@zohocorp.com> — 2025-06-13T13:04:45Z

   Hi,
   
   
   Apologies for delayed response, 
   I believe there may be a slight misunderstanding regarding the use case we're aiming to improve. Let me clarify it from the beginning.
   
   
   
   """
   
   Example :
   
   SELECT udf1(t1.a) FROM t1_1000row t1, t2_1row t2 where udf1(t1.a) = t2.a and t1.a > 5;
   
   """
   
   
   
   In this query, the expression udf1(t1.a) appears both in the join condition
   
   and in the top-level projection. Without optimization, udf1(t1.a) would be
   
   evaluated twice: once during join qualification and again during projection.
   
   
   
   Our goal is to detect such common expressions and pushes them down to
   
   the projection list of the appropriate lower-level plan node—whether it is
   
   on the outer or inner side of the join—based on where the expression's input
   
   columns originate from.
   
   
   
   This ensures that the expression is evaluated only once during execution
   
   and the computed result is reused in both the join condition and the final
   
   projection. This is particularly beneficial when dealing with expensive
   
   computations (e.g., Encrypt/Decrypt UDFs), where reducing redundant
   
   evaluations can significantly improve performance.
   
   
   
   Also,if we push the udf1  to foreign scan of t1 the filter ( t1.a > 5 ) will be
   
   evaluated first and then we will call the udf1 for the qualifing rows.
   
   
   
   Whereas, in your first reply you stated that we need to call the udf1  prior
   
   to the filter( here t1.a >5 ), hence needed to discuss this point once with you.
   
   
   
   From execScan.c, we can see that the qual is evaluated first and then
   
   the projection is formed.
   
   if (qual == NULL || ExecQual(qual, econtext))
   
   {
   
        /*
   
        * Found a satisfactory scan tuple.
   
        */
   
        if (projInfo)
   
        {
   
             /*
   
             * Form a projection tuple, store it in the result tuple slot
   
             * and return it.
   
             */
   
             return ExecProject(projInfo);
   
        }
   
        else
   
        {
   
             /*
   
             * Here, we aren't projecting, so just return scan tuple.
   
             */
   
             return slot;
   
        }
   
   }
   
   
   
   
   > Hash Cond involves two sides of the relations, how could we push down
   
   > it under one side of it? I can't follow up on this well, have you a draft
   
   > plan / code for your idea?
   
   
   
   We are not pushing down the entire hash condition as a whole which is not possible.
   
   Instead, we just push those expression, which involves only columns from any one side ( inner side or outer side)
   
   of the join.
   
   
   
   In our case, udf1(t1.a) is a function expression that only involves
   
   columns from the outer side (t1). Therefore, we can safely push it down to the
   
   outer join path's target list.
   
   
   
   Actually in general, we are assuming that it is safe and not a overhead
   
   to push down any such expression involved in hash-condition to the respective
   
   lower node's targetlist, as the expression will be evaluated same number of times
   
   as it would have been evaluated without the push down.
   
   This enables us to reuse the result of the expression that's present  in both the join condition and the final projection.
   
   
   
   If you see in createplan.c, we are calling the following functions:
   
   
   
   hashclauses = get_actual_clauses(best_path->path_hashclauses);
   
   /*
   
   * Rearrange hashclauses, if needed, so that the outer variable is always
   
   * on the left.
   
   */
   
   hashclauses = get_switched_clauses(best_path->path_hashclauses,
   
      best_path->jpath.outerjoinpath->parent->relids);
   
   OpExpr *clause = (OpExpr *) linitial(hashclauses);
   
   
   
   
   
   After this, we know that the outer varaiable is on left side and inner is in right side
   
   of opExpr so we can recurse into, linitial(clause) and find any FuncExpr ( or any other expr node)
   
   and then push it to the outer join-path as shown below:
   
   
   
   FuncExpr *fexpr = (FuncExpr *) node;
   
   
   
   best_path->jpath.outerjoinpath->pathtarget->exprs =
   
   lappend(best_path->jpath.outerjoinpath->pathtarget->exprs, fexpr);
   
   
   
   
   
   
   
   Hence the tentative draft code would be like:
   
   
   
   OpExpr *clause = (OpExpr *) linitial(hashclauses);
   
   node = (Node *) linitial(clause->args)
   
   if(IsA(node, FuncExpr)) {               //for now directly checking for funcexpr
   
   
   
   FuncExpr *fexpr = (FuncExpr *) node;
   
   best_path->jpath.outerjoinpath->pathtarget->exprs =
   
   lappend(best_path->jpath.outerjoinpath->pathtarget->exprs, fexpr);
   
   }
   
   
   
   The next step would be to replace the funcExpr in this clause, with a new Var
   
   
   
   newVar = makeVar(
   
   OUTER_VAR,
   
   list_length(best_path->jpath.outerjoinpath->pathtarget->exprs),
   
   exprType(((Node *) fexpr)), exprTypmod((Node *) fexpr),
   
   exprCollation((Node *) fexpr), 0
   
               );
   
   
   
   linitial(clause->args) = (Node *) newVar; //replace the funcExpr with the new Var.
   
   
   
   Modified PG PLAN:
   
   Observe the targetlist of cipher table, present in the below query plan,
   
   the decrypt function is coming from the foreign scan of cipher table itself.
   
   
   
   QUERY PLAN
   
   -----------------------------------------------------------------------------------------------------------
   
   CStore Version: "5.1.0"
   
   Branch: "check_decrypt_slowness"
   
   Build Date: "Mon May 19 13:27:24 2025"
   
   Executor: Vectorized exec
   
   
   
   Hash Left Join (cost=22.74..73.43 rows=1 width=65)
   
     Output: t1.sensitive_data1, aes256_cbc_decrypt(cipher c1 ,'\x32303235'::bytea, '\x32303235::bytea)
   
     Hash Cond: (t1.sensitive_data1 = (aes256_cbc_decrypt(cipher.c1, "\x32303235'::bytea, '\x32303235::bytea)))
   
     -> 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
   
     -> Hash (cost=22.72.. 22.72 rows=1 width=33)
   
      Output: cipher. c1, (aes256_cbc_decrypt(cipher.c1, '\x32303235"::bytea, '\x32303235:: bytea))
   
      -> Foreign Scan on public.cipher (cost-0.00..50.68 rows=1 width=49)
   
   Output: cipher.c1, aes256_cbc_decrypt(cipher.c1, '\x32303235'::bytea, '\x32303235 :: bytea)
   
   CStore Dir: /home/shubha/Documents/zoho/postgres17/data/cstore_fdw/116838/116931
   
   CStore Table Size: 2424 kB 
   
   
   
   Planning Time: 9.436 ms
   
   (19 rows)
   
   
   
   Please share your thoughts on this approach hope this clarifies the use case
   
   and the approach we're taking to optimize the query execution. If you have
   
   any further questions or suggestions, please feel free to share.
   
   
   
   Thanks and Regards
   
   Shubhankar K.
   
   ZLabs-CStore
   Member of Technical Staff, Zoho
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   ---- On Wed, 28 May 2025 23:53:46 +0530 Shubhankar Anand Kulkarni <shubhankar.ak@zohocorp.com> wrote ---
   
   
   
   Can you please resolve the doubt mentioned in the below thread?
   
   
   Thanks &Regards
   
   
   Shubhankar K
   Zlabs-CStore
   Member of Technical Staff, Zoho
   
   
   
   
   
   
   
   ---- On Mon, 26 May 2025 16:58:12 +0530 Shubhankar Anand Kulkarni < mailto:shubhankar.ak@zohocorp.com > wrote ---
   
   
   
   
   
   
   
   
   
   
   
   
   Hi Andy,
   
   Thanks for the reply. As suggested, I tried using CTE which seems to solve the case. This is the updated plan: 
   
   
                                                                                                  QUERY PLAN                                                                                                
   
   ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   
   Hash Right Join  (cost=2500502930.90..2501167775.90 rows=10000000 width=65) (actual time=1450.743..12200.977 rows=10000000 loops=1)
   
      Output: t1.sensitive_data1, t2.column1
   
      Hash Cond: (t2.column1 = t1.sensitive_data1)
   
      CTE t2
   
        ->  Foreign Scan on public.cipher  (cost=0.00..2500163471.33 rows=10000000 width=32) (actual time=0.095..6288.407 rows=10000000 loops=1)
   
              Output: aes256_cbc_decrypt(cipher.c1, '\x323032356b6579666f726373746f726561657332353663626374657374696e67'::bytea, '\x323032356373746f72656976666f7261657332353663626374657374696e67'::bytea)
   
              Read blocks: 2442 Skipped: 0
   
              File Mode: File Per Table
   
              CStore Dir: /home/shubha-18887/Documents/zoho/postgres17/data/cstore_fdw/116838/116931
   
              CStore Table Size: 2975 MB
   
      ->  CTE Scan on t2  (cost=0.00..200000.00 rows=10000000 width=32) (actual time=0.097..7626.693 rows=10000000 loops=1)
   
            Output: t2.column1
   
      ->  Hash  (cost=136334.57..136334.57 rows=10000000 width=33) (actual time=1448.676..1448.678 rows=10000000 loops=1)
   
            Output: t1.sensitive_data1
   
            Buckets: 131072  Batches: 128  Memory Usage: 5999kB
   
            ->  Foreign Scan on public.benchmark_encytion t1  (cost=0.00..136334.57 rows=10000000 width=33) (actual time=0.143..513.782 rows=10000000 loops=1)
   
                  Output: t1.sensitive_data1
   
                  Read blocks: 2442 Skipped: 0
   
                  File Mode: File Per Table
   
                  CStore Dir: /home/shubha-18887/Documents/zoho/postgres17/data/cstore_fdw/116838/135230
   
                  CStore Table Size: 1987 MB
   
   Query Identifier: 2330480792947742169
   
   Planning Time: 3.829 ms
   
   Execution Time: 12401.482 ms
   
   (24 rows)
   
   
   
   
   
   As seen, the expression got pushed down to respective foreign scan, reducing the overall query time reduced significantly, but there is an increase in the memory footprint.
   
   
   
   Also the thing which you mentioned, 
   
   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.
   
   
   After looking into the code, it looks like we will first evaluate the qual, if it qualifies then only we will go for the projection. Meaning, even after expression push-down we will evaluate the expression on top filtered rows only. So I don't think it should have any concern in this case.
   
   
   Also for this particular case, I tried playing around create_hashjoin_plan function in createplan.c, to push my hash-clause expression to below foreign-scan. Currently, if there is a single hash clause of form funcExpr op Var, I am appending the funcExpr to pathTarget of  respective table and replacing the funcExpr with a var. Is it a right place/approach to check or should we do changes while parsing in deconstruct_jointree(), in distiribute_quals_to_rels we can  update the appropriate baserestrictinfo. 
   Pls share your thoughts on the same.
   
   
   
   
   
   
   Thanks & Regards
   
   Shubhankar K
   ZLabs-CStore
   
   
   
   
   
   
   
   ---- On Thu, 22 May 2025 14:21:51 +0530 Andy Fan < mailto:zhihuifan1213@163.com > wrote ---
   
   
   
   
   
   
   
   
   
   
   
   
   Shubhankar Anand Kulkarni < mailto: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

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

   Andy Fan <zhihuifan1213@163.com> — 2025-06-14T03:33:25Z

   Shubhankar Anand Kulkarni <shubhankar.ak@zohocorp.com> writes:
   
   Hi, 
   > Apologies for delayed response, 
   > I believe there may be a slight misunderstanding regarding the use case we're aiming to improve. Let me clarify it from
   > the beginning.
   >
   > """
   > Example :
   > SELECT udf1(t1.a) FROM t1_1000row t1, t2_1row t2 where udf1(t1.a) = t2.a and t1.a > 5;
   > """
   >
   ...
   >
   > Whereas, in your first reply you stated that we need to call the udf1  prior
   > to the filter( here t1.a >5 ), hence needed to discuss this point once
   > with you.
   
   You changed my query at [1] and then reuse the rest words there.
   My query was:
   
   """
   SELECT udf1(t1.a) FROM t1_1000row t1, t2_1row t2 where t2.fid = t1.id; 
   """
   
   and the filter I meant was the "join filter (t2.fid = t1.id)" rather than
   the scan filter (t1.a > 5) in your case. 
   
   You may argue that my query is not exactly same with yours, you query
   is (where there is a same UDF as targetlist in the join qual): 
   
   """
   SELECT udf1(t1.a) FROM t1_1000row t1, t2_1row t2 where udf1(t1.a) = t2.a and t1.a > 5;
   """
   
   But the keypoint we need to pay much more attention to impacts of such
   push down. we have to distinguish the middle cases. e.g.
   
   SELECT udf1(t1.a) FROM t1_1000row t1, t2_1row t2
   where udf1(t1.a) = t2.a
   and t2.fid = t1.id  (*****)  -- this is added by me.
   and t1.a > 5;
   
   Otherwise the UDF would be called more than the current master code.
   
   NOTE: it is possible to run "t2.fid = t1.id" before udf1(t1.a) = t2.a. 
   
   > From execScan.c, we can see that the qual is evaluated first and then
   > the projection is formed.
   
   sure..
   
   >> Hash Cond involves two sides of the relations, how could we push down
   >> it under one side of it? I can't follow up on this well, have you a draft
   >> plan / code for your idea?
   >
   > We are not pushing down the entire hash condition as a whole which is not possible.
   > Instead, we just push those expression, which involves only columns from any one side ( inner side or outer side)
   > of the join.
   >
   > In our case, udf1(t1.a) is a function expression that only involves
   > columns from the outer side (t1). Therefore, we can safely push it down to the
   > outer join path's target list.
   >
   > Actually in general, we are assuming that it is safe and not a overhead
   > to push down any such expression involved in hash-condition to the respective
   > lower node's targetlist, as the expression will be evaluated same number of times
   > as it would have been evaluated without the push down.
   > This enables us to reuse the result of the expression that's present  in both the join condition and the final
   > projection.
   >
   > If you see in createplan.c, we are calling the following functions:
   >
   >  hashclauses = get_actual_clauses(best_path->path_hashclauses);
   >  /*
   >  * Rearrange hashclauses, if needed, so that the outer variable is always
   >  * on the left.
   >  */
   >  hashclauses = get_switched_clauses(best_path->path_hashclauses,
   >     best_path->jpath.outerjoinpath->parent->relids);
   >  OpExpr *clause = (OpExpr *) linitial(hashclauses);
   >
   > After this, we know that the outer varaiable is on left side and inner is in right side
   > of opExpr so we can recurse into, linitial(clause) and find any FuncExpr ( or any other expr node)
   > and then push it to the outer join-path as shown below:
   >
   >  FuncExpr *fexpr = (FuncExpr *) node;
   >
   >  best_path->jpath.outerjoinpath->pathtarget->exprs =
   >  lappend(best_path->jpath.outerjoinpath->pathtarget->exprs, fexpr);
   >
   > Hence the tentative draft code would be like:
   >
   >  OpExpr *clause = (OpExpr *) linitial(hashclauses);
   >  node = (Node *) linitial(clause->args)
   >  if(IsA(node, FuncExpr)) {               //for now directly checking for funcexpr
   >
   >  FuncExpr *fexpr = (FuncExpr *) node;
   >  best_path->jpath.outerjoinpath->pathtarget->exprs =
   >  lappend(best_path->jpath.outerjoinpath->pathtarget->exprs, fexpr);
   >  }
   >
   >  The next step would be to replace the funcExpr in this clause, with a new Var
   >
   >  newVar = makeVar(
   >  OUTER_VAR,
   >  list_length(best_path->jpath.outerjoinpath->pathtarget->exprs),
   >  exprType(((Node *) fexpr)), exprTypmod((Node *) fexpr),
   >  exprCollation((Node *) fexpr), 0
   >              );
   >
   >  linitial(clause->args) = (Node *) newVar; //replace the funcExpr with
   >  the new Var.
   
   I am worried about if this is safe to do and there is no such kind of
   change in create_plan stage with my current knowledge. But I am sure
   the costing will be mismatched between the real (udf is called only in
   scan stage after your patch) and the number in explain  (udf is called in
   join stage, both targetlist and qual). and It is not easy to say that
   the costing mismatch doesn't matter.
   
   In my knowledge, if we could know a UDF push down would never increase
   the calls of the UDF before costing with a limitted effort, then we
   could push down it and costing with the result and get the bestpath at
   the final. 
   
   -- 
   Best Regards
   Andy Fan
   
   
   
   
   

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

   Shubhankar Anand Kulkarni <shubhankar.ak@zohocorp.com> — 2025-06-19T07:45:08Z

   Hi,
   Thanks for your valuable feedback on the patch.
   Will check on the cases mentioned by you and do the needful.
   
   
   Best Regards
   
   Shubhankar K.
   
   Member Technical Staff
   
   Zoho Corporation
   
   
   
   
   
   ---- On Sat, 14 Jun 2025 09:03:25 +0530 Andy Fan <zhihuifan1213@163.com> wrote ---
   
   
   
   Shubhankar Anand Kulkarni < mailto:shubhankar.ak@zohocorp.com > writes: 
    
   Hi, 
   > Apologies for delayed response, 
   > I believe there may be a slight misunderstanding regarding the use case we're aiming to improve. Let me clarify it from 
   > the beginning. 
   > 
   > """ 
   > Example : 
   > SELECT udf1(t1.a) FROM t1_1000row t1, t2_1row t2 where udf1(t1.a) = t2.a and t1.a > 5; 
   > """ 
   > 
   ... 
   > 
   > Whereas, in your first reply you stated that we need to call the udf1  prior 
   > to the filter( here t1.a >5 ), hence needed to discuss this point once 
   > with you. 
    
   You changed my query at [1] and then reuse the rest words there. 
   My query was: 
    
   """ 
   SELECT udf1(t1.a) FROM t1_1000row t1, t2_1row t2 where t2.fid = t1.id; 
   """ 
    
   and the filter I meant was the "join filter (t2.fid = t1.id)" rather than 
   the scan filter (t1.a > 5) in your case. 
    
   You may argue that my query is not exactly same with yours, you query 
   is (where there is a same UDF as targetlist in the join qual): 
    
   """ 
   SELECT udf1(t1.a) FROM t1_1000row t1, t2_1row t2 where udf1(t1.a) = t2.a and t1.a > 5; 
   """ 
    
   But the keypoint we need to pay much more attention to impacts of such 
   push down. we have to distinguish the middle cases. e.g. 
    
   SELECT udf1(t1.a) FROM t1_1000row t1, t2_1row t2 
   where udf1(t1.a) = t2.a 
   and t2.fid = t1.id  (*****)  -- this is added by me. 
   and t1.a > 5; 
    
   Otherwise the UDF would be called more than the current master code. 
    
   NOTE: it is possible to run "t2.fid = t1.id" before udf1(t1.a) = t2.a. 
    
   > From execScan.c, we can see that the qual is evaluated first and then 
   > the projection is formed. 
    
   sure.. 
    
   >> Hash Cond involves two sides of the relations, how could we push down 
   >> it under one side of it? I can't follow up on this well, have you a draft 
   >> plan / code for your idea? 
   > 
   > We are not pushing down the entire hash condition as a whole which is not possible. 
   > Instead, we just push those expression, which involves only columns from any one side ( inner side or outer side) 
   > of the join. 
   > 
   > In our case, udf1(t1.a) is a function expression that only involves 
   > columns from the outer side (t1). Therefore, we can safely push it down to the 
   > outer join path's target list. 
   > 
   > Actually in general, we are assuming that it is safe and not a overhead 
   > to push down any such expression involved in hash-condition to the respective 
   > lower node's targetlist, as the expression will be evaluated same number of times 
   > as it would have been evaluated without the push down. 
   > This enables us to reuse the result of the expression that's present  in both the join condition and the final 
   > projection. 
   > 
   > If you see in createplan.c, we are calling the following functions: 
   > 
   >  hashclauses = get_actual_clauses(best_path->path_hashclauses); 
   >  /* 
   >  * Rearrange hashclauses, if needed, so that the outer variable is always 
   >  * on the left. 
   >  */ 
   >  hashclauses = get_switched_clauses(best_path->path_hashclauses, 
   >     best_path->jpath.outerjoinpath->parent->relids); 
   >  OpExpr *clause = (OpExpr *) linitial(hashclauses); 
   > 
   > After this, we know that the outer varaiable is on left side and inner is in right side 
   > of opExpr so we can recurse into, linitial(clause) and find any FuncExpr ( or any other expr node) 
   > and then push it to the outer join-path as shown below: 
   > 
   >  FuncExpr *fexpr = (FuncExpr *) node; 
   > 
   >  best_path->jpath.outerjoinpath->pathtarget->exprs = 
   >  lappend(best_path->jpath.outerjoinpath->pathtarget->exprs, fexpr); 
   > 
   > Hence the tentative draft code would be like: 
   > 
   >  OpExpr *clause = (OpExpr *) linitial(hashclauses); 
   >  node = (Node *) linitial(clause->args) 
   >  if(IsA(node, FuncExpr)) {               //for now directly checking for funcexpr 
   > 
   >  FuncExpr *fexpr = (FuncExpr *) node; 
   >  best_path->jpath.outerjoinpath->pathtarget->exprs = 
   >  lappend(best_path->jpath.outerjoinpath->pathtarget->exprs, fexpr); 
   >  } 
   > 
   >  The next step would be to replace the funcExpr in this clause, with a new Var 
   > 
   >  newVar = makeVar( 
   >  OUTER_VAR, 
   >  list_length(best_path->jpath.outerjoinpath->pathtarget->exprs), 
   >  exprType(((Node *) fexpr)), exprTypmod((Node *) fexpr), 
   >  exprCollation((Node *) fexpr), 0 
   >              ); 
   > 
   >  linitial(clause->args) = (Node *) newVar; //replace the funcExpr with 
   >  the new Var. 
    
   I am worried about if this is safe to do and there is no such kind of 
   change in create_plan stage with my current knowledge. But I am sure 
   the costing will be mismatched between the real (udf is called only in 
   scan stage after your patch) and the number in explain  (udf is called in 
   join stage, both targetlist and qual). and It is not easy to say that 
   the costing mismatch doesn't matter. 
    
   In my knowledge, if we could know a UDF push down would never increase 
   the calls of the UDF before costing with a limitted effort, then we 
   could push down it and costing with the result and get the bestpath at 
   the final. 
    
   -- 
   Best Regards 
   Andy Fan