|To:||Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>|
|Cc:||Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Florian Schoppmann <Florian(dot)Schoppmann(at)emc(dot)com>, pgsql-hackers(at)postgresql(dot)org|
|Subject:||Re: Invalid optimization of VOLATILE function in WHERE clause?|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
On Wed, Sep 19, 2012 at 02:39:12PM -0500, Kevin Grittner wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >> It still seems like awfully weird behavior.
> > Why? The WHERE condition relates only to the output of the _stats
> > subquery, so why shouldn't it be evaluated there, rather than
> > after the join?
> In another thread, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > It's easier to understand why this is if you realize that SQL has
> > a very clear model of a "pipeline" of query execution.
> > Conceptually, what happens is:
> > 1. Form the cartesian product of the tables listed in FROM (ie,
> > all combinations of rows).
> > 2. Apply the WHERE condition to each row from 1, and drop rows
> > that don't pass it.
> People expect that the results will be consistent with this model,
> even if the implementation is optimized "under the covers". I think
> correct semantics should trump performance here.
It seems like this is what happens here except that the function is
evaluated once for the WHERE and not once per ROW. Both of these meet
the criterion for 2 above and Tom's earlier comments both hold.
|Next Message||Daniel Browning||2012-09-19 20:05:44||CTE optimization fence on the todo list?|
|Previous Message||Kevin Grittner||2012-09-19 19:39:12||Re: Invalid optimization of VOLATILE function in WHERE clause?|