Hi, On Jul 18, 2025 at 22:52 +0800, feichanghong <feichangh...@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
