Thread

Re: Try a presorted outer path when referenced by an ORDER BY prefix

Andrei Lepikhov <lepihov@gmail.com> — 2026-05-09T11:22:00Z

On 03/04/2026 13:35, Andrei Lepikhov wrote:
> Feedback and review welcome.
It seems this approach should be reconsidered a little.
By watching how it works in real life, I see cases of planning regression in
massive queries. Just to demonstrate what it looks like, you may check a
concrete example in the attachment. For this specific query, I see the following
difference:

Presorted path enabled:

 Planning:
   Buffers: shared hit=1682
   Memory: used=136930kB  allocated=139488kB
 Planning Time: 3194.900 ms
 Execution Time: 17.681 ms

Presorted path disabled:

 Planning:
   Buffers: shared hit=1775
   Memory: used=136225kB  allocated=139488kB
 Planning Time: 1328.901 ms
 Execution Time: 11.785 ms

In other cases, it also causes memory consumption growth.

The relation_can_be_sorted_early is not cheap. It walks rel->reltarget->exprs,
calls find_ec_member_matching_expr per expression (which walks the EC's
ec_members list), then falls through to find_computable_ec_member, which
traverses the EC again with parser machinery to detect computability. For CASE
expressions across multiple tables — the user's actual sort keys - the EC has
multiple members, none of which match an existing reltarget exactly, so the slow
path runs to completion before returning false.

IMO, the main issue lies in multiple calls of relation_can_be_sorted_early. It
happens multiple times, but may be done once during RelOptInfo creation. We just
need to introduce a useful_query_pathkeys cache.

-- 
regards, Andrei Lepikhov,
pgEdge