Re: BUG #14642: Excessive sorting node appears in the plan

On 5 May 2017 at 02:12, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> bashtanov(at)imap(dot)cc writes:
>> Do blackbox-style subplans, such as subqueries with grouping, share only the
>> resulting stream order info with the enclosing query, not the equivalence
>> classes?
>
> Yes, see convert_subquery_pathkeys().

Not as a back-patchable fix, but I wonder if we couldn't improve this
situation by having pathkey_is_redundant() not throw pathkeys with a
constant in their eclass away, but rather keep these around as "no-op
pathkeys".

It would make the logic in compare_pathkeys() a bit more tricky, but
maybe it could be manageable if we kept the no-op pathkeys in a
separate list, and only lookup the pathkey in that list when the
pathkeys being compared are not the same. If it's in the no-op list,
then we can skip to the next path key after the no-op one. Although
I'm not quite sure in what cases PATHKEYS_EQUAL would be returned.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services