From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Lewis <mlewis@entrata.com>
cc: Jeff Janes <jeff.janes@gmail.com>,
        =?UTF-8?Q?Yavuz_Selim_Serto=C4=9Flu_=28ETIYA=29?=
 <yavuz.sertoglu@etiya.com>,
        "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: Query slows when used with view
In-reply-to: 
 <CAHOFxGpi_CnCPhLr0ebudxm7Y0gpP1TTrRwDyfdUTv9Hn9WWoQ@mail.gmail.com>
References: 
 <AM6PR01MB4817E28570A83C86BED9B05C9B950@AM6PR01MB4817.eurprd01.prod.exchangelabs.com>
 <20080.1570629421@sss.pgh.pa.us>
 <AM6PR01MB4817847CE14487DF685CE0049B950@AM6PR01MB4817.eurprd01.prod.exchangelabs.com>
 <CAMkU=1xgK1pWKFssxOW8unAhvJdA6ub_xcFcCNzUdBGGQKOEYA@mail.gmail.com>
 <CAHOFxGpi_CnCPhLr0ebudxm7Y0gpP1TTrRwDyfdUTv9Hn9WWoQ@mail.gmail.com>
Comments: In-reply-to Michael Lewis <mlewis@entrata.com>
	message dated "Wed, 09 Oct 2019 09:32:37 -0600"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <28165.1570637267.1@sss.pgh.pa.us>
Date: Wed, 09 Oct 2019 12:07:47 -0400
Message-ID: <28166.1570637267@sss.pgh.pa.us>
Precedence: bulk

Michael Lewis <mlewis@entrata.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