From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | truncate_useless_pathkeys() doesn't account for window function queries |
Date: | 2025-10-06 03:42:45 |
Message-ID: | CAApHDvrj3HTKmXoLMbUjTO=_MNMxM=cnuCSyBKidAVibmYPnrg@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Make-truncate_useless_pathkeys-consider-WindowFun.patch | application/octet-stream | 4.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2025-10-06 03:58:07 | Re: Resetting recovery target parameters in pg_createsubscriber |
Previous Message | Michael Paquier | 2025-10-06 03:17:46 | Re: [PROPOSAL] Termination of Background Workers for ALTER/DROP DATABASE |