truncate_useless_pathkeys() doesn't account for window function queries
От | David Rowley |
---|---|
Тема | truncate_useless_pathkeys() doesn't account for window function queries |
Дата | |
Msg-id | CAApHDvrj3HTKmXoLMbUjTO=_MNMxM=cnuCSyBKidAVibmYPnrg@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: truncate_useless_pathkeys() doesn't account for window function queries
|
Список | 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
Вложения
В списке pgsql-hackers по дате отправления: