Date: Sun, 6 Oct 2019 22:37:53 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Behrang Saeedzadeh <behrangsa@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@lists.postgresql.org
Subject: Re: Slow PostgreSQL 10.6 query
Message-ID: <20191006203753.wobkgsg625ej7k2m@development>
References: 
 <CAERAJ+-1buiJ+_JWEo0a9Ao-CVMWpgp=EnFx1dJtnB3WmMi2zQ@mail.gmail.com>
 <9624.1569936426@sss.pgh.pa.us>
 <CAERAJ+_C981pYAbjZm8Eqn2p+9uk2XJWaL7D934doP3+97+dUw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: 
 <CAERAJ+_C981pYAbjZm8Eqn2p+9uk2XJWaL7D934doP3+97+dUw@mail.gmail.com>
User-Agent: NeoMutt/20180716
Precedence: bulk

On Tue, Oct 01, 2019 at 11:42:33PM +1000, Behrang Saeedzadeh wrote:
>Thanks. That eliminated the bottleneck!
>
>Any ideas why adding ORDER BY to the subquery also changes the plan in a
>way that eliminates the bottleneck?
>

IIRC the ORDER BY clause makes it impossible to "collapse" the subquery
into the main (upper) one, and it probably happens to constrict the
choices so that the planner ends up picking a good plan. I guess adding
"OFFSET 0" to the subquery would have the same effect.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services