Re: Even when the data is already ordered, MergeAppend still adds a Sort node

feichanghong <feichanghong@qq.com> writes:
> Currently, I have not found a better way to rewrite this, except by optimizing
> this scenario from the pg kernel side.

If you're willing to modify your query, you could fake it out by
spelling the subquery's "a = 1" condition in a way that won't produce an
EquivalenceClass.  For example,

regression=# explain analyze select a, b from (
    (select a, b from t t1 where a > 19000 order by a, b)
    union all
    (select a, b from t t2 where a >= 1 and a <= 1 and b > 1 order by a, b)
) t order by a, b limit 1;
                                                                QUERY PLAN
                  

------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.58..0.63 rows=1 width=8) (actual time=0.070..0.070 rows=1.00 loops=1)
   Buffers: shared hit=8 read=1
   ->  Merge Append  (cost=0.58..481.10 rows=11000 width=8) (actual time=0.069..0.069 rows=1.00 loops=1)
         Sort Key: t1.a, t1.b
         Buffers: shared hit=8 read=1
         ->  Index Only Scan using t_a_b_idx on t t1  (cost=0.29..29.79 rows=1000 width=8) (actual time=0.027..0.027
rows=1.00loops=1) 
               Index Cond: (a > 19000)
               Heap Fetches: 0
               Index Searches: 1
               Buffers: shared hit=2 read=1
         ->  Index Only Scan using t_a_b_idx on t t2  (cost=0.29..341.30 rows=10000 width=8) (actual time=0.041..0.041
rows=1.00loops=1) 
               Index Cond: ((a >= 1) AND (a <= 1) AND (b > 1))
               Heap Fetches: 0
               Index Searches: 1
               Buffers: shared hit=6
 Planning:
   Buffers: shared hit=6
 Planning Time: 0.174 ms
 Execution Time: 0.089 ms
(19 rows)


I'd be the first to agree that that's a hack not a nice solution.
But I think getting to something that's not a hack is going to
involve a lot more work than this edge case seems worth.  We're
not likely to accept a patch that pessimizes planning within
subqueries on the small chance that that will result in a path
whose apparent sort order matches the needs of the outer query
better.  Maybe something could be done inside
convert_subquery_pathkeys, but I suspect we don't really have
enough information at that point to decide what to do.

            regards, tom lane