From: | "jg" <jg(at)rilk(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Coalesce bug ? |
Date: | 2012-12-22 10:28:00 |
Message-ID: | 4104-50d58b00-d-6b8b4580@74608595 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2012-12-22 12:06:21 | Re: Simple Query Very Slow |
Previous Message | Tatsuo Ishii | 2012-12-22 10:13:57 | Re: Streaming replication + pgpool-II tutorial |