Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.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@sss.pgh.pa.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.
-Kevin