From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Lewis <mlewis@entrata.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Yavuz Selim Sertoğlu (ETIYA) <yavuz.sertoglu@etiya.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Query slows when used with view
Date: Wed, 09 Oct 2019 12:07:47 -0400
Message-ID: <28166.1570637267@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mlewis@entrata.com, jeff.janes@gmail.com, yavuz.sertoglu@etiya.com, pgsql-performance@lists.postgresql.org
Subject: Re: Query slows when used with view
In-Reply-To: <28166.1570637267@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox