Hi, > Ah. The reason for that is a bit subtle: constant-folding of immutable > functions happens in the same pass over the query tree as simplification > of simplifiable constructs --- including COALESCE. So what's happening > is that eval_const_expressions, working on the COALESCE construct, first > calls itself recursively to simplify the first argument. That leads to > evaluation of ps3(1), and we get back a constant 1. Now we reach a > block of code with this comment: > > /* > * We can remove null constants from the list. For a > * non-null constant, if it has not been preceded by any > * other non-null-constant expressions then it is the > * result. Otherwise, it's the next argument, but we can > * drop following arguments since they will never be > * reached. > */ > > So at this point we realize that the result of the COALESCE() is 1, and > we don't bother to do const-simplification of its remaining arguments. > They're just thrown away, and the final command for execution is nothing > more than "SELECT 1" (as you can see if you do EXPLAIN VERBOSE). > > The other example with sub-SELECTs acts differently because the > sub-SELECT is something of an optimization fence --- "(SELECT 1)" does > not look like a simple Const to eval_const_expressions. > > As you noted upthread, none of this is a bug. Labeling a function > immutable is an explicit statement that it has no side-effects of > interest and can be evaluated whenever the system chooses. If you stick > in side-effects like a RAISE statement, then that lets you peer into > some inner workings of the optimizer, but it's you that's breaking the > rules not the optimizer. > > regards, tom lane Thank you for the explanation. It was tricky to get it, and I got this question as a side effect of some other optimisation works. -- Cordialement, Jean-Gérard Pailloncy -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
