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