Re: Query slows when used with view

Michael Lewis <mlewis(at)entrata(dot)com> writes:
>> When you join to a view, the view sticks together, as if they were all in
>> parentheses. But when you substitute the text of a view into another
>> query, then they are all on the same level and can be parsed differently.
>>
>> Consider the difference between "1+1 * 3", and "(1+1) * 3"

> I thought from_collapse_limit being high enough meant that it will get
> re-written and inlined into the same level. To extend your metaphor, that
> it would be 1 * 3 + 1 * 3.

The point is that the semantics are actually different --- in Jeff's
example, the answer is 4 vs. 6, and in the OP's query, the joins have
different scopes. from_collapse_limit has to do with whether the
planner can rewrite the query into a different form, but it's not
allowed to change the semantics by doing so.

In some cases you can re-order joins without changing the semantics,
just as arithmetic has associative and commutative laws. But you
can't always re-order outer joins like that. I didn't dig into
the details of the OP's query too much, but I believe that the two
forms of his join tree are semantically different, resulting
in different runtimes.

regards, tom lane