Обсуждение: distribute_restrictinfo_to_rels if restrictinfo contains volatile functions

                                       QUERY PLAN

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

 Nested Loop

   Output: t.a, t.b, a.a, a_1.a

   ->  Nested Loop

         Output: a.a, a_1.a

         Join Filter: (((((a.a + a_1.a))::numeric / 4.0))::double precision > random())

         ->  Function Scan on pg_catalog.generate_series a

               Output: a.a

               Function Call: generate_series(1, 1)

         ->  Function Scan on pg_catalog.generate_series a_1

               Output: a_1.a

               Function Call: generate_series(1, 1)

   ->  Seq Scan on public.t

         Output: t.a, t.b

(13 rows)

FromExpr []

        [fromlist]

                RangeTblRef [rtindex=1]

                RangeTblRef [rtindex=4]

                RangeTblRef [rtindex=5]

        [quals]

                OpExpr [opno=674 opfuncid=297 opresulttype=16 opretset=false]

                        FuncExpr [funcid=1746 funcresulttype=701 funcretset=false funcvariadic=false      

                                  funcformat=COERCE_IMPLICIT_CAST]

                                OpExpr [opno=1761 opfuncid=1727 opresulttype=1700 opretset=false]

                                        FuncExpr [funcid=1740 funcresulttype=1700 funcretset=false funcvariadic=false 

                                                         funcformat=COERCE_IMPLICIT_CAST]

                                                OpExpr [opno=551 opfuncid=177 opresulttype=23 opretset=false]

                                                        Var [varno=4 varattno=1 vartype=23 varnoold=4 varoattno=1]

                                                        Var [varno=5 varattno=1 vartype=23 varnoold=5 varoattno=1]

                                        Const [consttype=1700 constlen=-1 constvalue=94908966309104 constisnull=false 

                                                     constbyval=false]

                        FuncExpr [funcid=1598 funcresulttype=701 funcretset=false funcvariadic=false 

                                          funcformat=COERCE_EXPLICIT_CALL]

Nested Loop 

 Join Filter: (((((a.a + a_1.a))::numeric / 4.0))::double precision > random())  

 ->  Nested Loop  

         ->  Function Scan on generate_series a  

         ->  Function Scan on generate_series a_1  

   ->  Seq Scan on t  (cost=0.00..32.60 rows=2260 width=8)

Zhenghua Lyu <zlyu@vmware.com> writes:
>         The where clause is "pushed down to the x,y" because it only references these two relations.

Yeah.  I agree that it's somewhat unprincipled, but changing it doesn't
seem like a great idea.  There are a lot of users out there who aren't
terribly careful about marking their UDFs as non-volatile, but would be
unhappy if the optimizer suddenly crippled their queries because of
being picky about this.

Also, we specifically document that order of evaluation in WHERE clauses
is not guaranteed, so I feel no need to make promises about how often
volatile functions there will be evaluated.  (Volatiles in SELECT lists
are a different story.)

This behavior has stood for a couple of decades with few user complaints,
so why are you concerned about changing it?

            regards, tom lane