Horton

Thread

Commits

  1. Make truncate_useless_pathkeys() consider WindowFuncs

  1. truncate_useless_pathkeys() doesn't account for window function queries

    David Rowley <dgrowleyml@gmail.com> — 2025-10-06T03:42:45Z

    Looking at truncate_useless_pathkeys(), it tries to be fairly liberal
and not truncate anything that might be useful for various operations
that require sorted Paths. However, it's not quite liberal enough to
consider WindowClause sort orders.

There are a couple of things truncate_useless_pathkeys() neglects:

1. Doesn't look at sort_pathkeys. It looks at query_pathkeys, which if
you look at standard_qp_callback(), gets set to the first top-level
operation that will be applied atop of the final join rel. This
results in the function not doing anything to consider the final ORDER
BY clause when the ORDER BY isn't the first operation handled after
the join search.
2. Doesn't explicitly account for window_pathkeys. Only in the case
where query_pathkeys is set to that.

This results in the pathkeys useful for the ORDER BY in the following
case being truncated away from the IndexPath:

create table ab(a int, b int);
create index on ab(a,b);
explain select *, row_number() over (order by a) from ab order by a,b;

Master:
 Incremental Sort  (cost=0.96..183.74 rows=2260 width=16)
   Sort Key: a, b
   Presorted Key: a
   ->  WindowAgg  (cost=0.20..111.96 rows=2260 width=16)
         Window: w1 AS (ORDER BY a ROWS UNBOUNDED PRECEDING)
         ->  Index Only Scan using ab_a_b_idx on ab  (cost=0.15..78.06
rows=2260 width=8)

Patched:
------------------------------------------------------------------------------------
 WindowAgg  (cost=0.20..111.96 rows=2260 width=16)
   Window: w1 AS (ORDER BY a ROWS UNBOUNDED PRECEDING)
   ->  Index Only Scan using ab_a_b_idx on ab  (cost=0.15..78.06
rows=2260 width=8)

A patch to fix this is attached.

(truncate_useless_pathkeys() also isn't quite the best example of code
reusability, but if we do anything about that, let's do it in another
commit.)

David

  2. Re: truncate_useless_pathkeys() doesn't account for window function queries

    David Rowley <dgrowleyml@gmail.com> — 2025-10-08T23:41:01Z

    On Mon, 6 Oct 2025 at 16:42, David Rowley <dgrowleyml@gmail.com> wrote:
> Looking at truncate_useless_pathkeys(), it tries to be fairly liberal
> and not truncate anything that might be useful for various operations
> that require sorted Paths. However, it's not quite liberal enough to
> consider WindowClause sort orders.

I suspect there are not going to be many arguments against fixing
this, so I've gone ahead and pushed it to master.

David