On Fri, Oct 2, 2020 at 4:56 PM Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > >And I don't see any reason why the CASE statement couldn't in theory > >(I don't know the internals enough to know when it actually happens) > >be done as part of the base relation scan (in this case, the seq > >scan). It's not dependent on any information from the join. > > > > Imagine a query like this: > > select t1.id, volatile_func() from t1 join t2 using (id); > > and now assume t2 contains duplicate values. If the volatile_func gets > evaluated as part of the t1 scan, then we'll get multiple occurrences > in the results, due to the t2 duplicates. I belive volatile functions > are not expected to behave like that - the docs say: > > A query using a volatile function will re-evaluate the function at > every row where its value is needed.. > > And I assume this references to rows at the level where the function is > used, i.e. after the join.
Ah, this makes sense. I was thinking exactly the opposite: that you'd want not to execute volatile functions multiple times for the same base rel row, but now that you describe it it makes sense why they shouldn't be. James
