Hi,
On Jul 18, 2025 at 22:52 +0800, feichanghong <feichanghong@qq.com>, wrote:
explain select * from (select * from t where a = 1 and b > 1 order by a, b) order by a, b limit 1;
QUERY PLAN
-----------------------------------------------------------------------------------------
Limit (cost=366.26..366.27 rows=1 width=8)
-> Sort (cost=366.26..391.26 rows=9999 width=8)
Sort Key: t.a, t.b
-> Index Only Scan using t_a_b_idx on t (cost=0.29..316.27 rows=9999 width=8)
Index Cond: ((a = 1) AND (b > 1))
(5 rows)
```
Should we retain the complete `pathkeys` in `Path->pathkeys` for use by the
upper layers of the subquery, rather than just keeping the portion trimmed by
`PlannerInfo->query_pathkeys`? I'm not sure if my understanding is correct.
The subquery has a qualifier a = 1 that forms an Equivalence Class (EC) whose ec_member contains a constant.
As a result, subroot->sort_pathkeys doesn't need to include column a.
However, in the outer query, there are no such qualifiers to form a similar EC, and the ORDER BY a, b clause means root->sort_pathkeys requires both columns a and b.
When convert_subquery_pathkeys is called, the subpath lacks the pathkeys for column a.
Furthermore, is there a more efficient way to write this, to avoid the
`Sort` node mentioned above?
A simple solution is to add an EC using a qual:
EXPLAIN SELECT * FROM (SELECT * FROM t WHERE a = 1 AND b > 1 ORDER BY a, b) WHERE a = 1 ORDER BY a, b LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------
Limit (cost=0.29..0.32 rows=1 width=8)
-> Index Only Scan using t_a_b_idx on t (cost=0.29..316.27 rows=9999 width=8)
Index Cond: ((a = 1) AND (b > 1))
(3 rows)
--
Zhang Mingli
HashData
