Re: Slow PostgreSQL 10.6 query

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