Thread: Optimizer sorting an already sorted result

<span style="font-family: courier new,monospace;">In the plan below, we can see that the optimizer is sorting an
alreadysorted result. It seems to forget the sort order across the UNIQUE node. My question is, do we make any attempts
inthe optimizer to remember the sort order of a result, to avoid any further sorting on same sort-key? If not, can we
dosomething about it?<br /><br />postgres=# explain select * from del where ctid in ( select ('''(' || i || ',' || j ||
')''')::tidfrom generate_series( 0, 1) s1(i), generate_series( 1, 1 ) s2(j) );</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier
new,monospace;">                                                      QUERY
PLAN                                                      </span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier
new,monospace;">------------------------------------------------------------------------------------------------------------------------</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> Merge Join 
(cost=177447.07..182043.29rows=40000 width=97)</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">   Merge Cond: ((((((('''('::text || (s1.i)::text) || ','::text) ||
(s2.j)::text)|| ')'''::text))::tid) = del.ctid)</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">   ->  Sort  (cost=155639.89..155739.89 rows=40000 width=8)</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">         Sort Key:
(((((('''('::text|| (s1.i)::text) || ','::text) || (s2.j)::text) || ')'''::text))::tid)</span><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;">         ->  Unique 
(cost=147032.84..152032.84rows=40000 width=8)</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">               ->  Sort  (cost=147032.84..149532.84 rows=1000000
width=8)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">                    Sort Key: (((((('''('::text || (s1.i)::text) || ','::text) || (s2.j)::text) ||
')'''::text))::tid)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">                    ->  Nested Loop  (cost=13.50..20026.00 rows=1000000 width=8)</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier
new,monospace;">                          ->  Function Scan on generate_series s1  (cost=0.00..12.50 rows=1000
width=4)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">                          ->  Materialize  (cost=13.50..23.50 rows=1000 width=4)</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier
new,monospace;">                                ->  Function Scan on generate_series s2  (cost=0.00..12.50 rows=1000
width=4)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">  
-> Materialize  (cost=21807.19..23055.61 rows=99874 width=103)</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;">         ->  Sort  (cost=21807.19..22056.87 rows=99874
width=103)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">              Sort Key: del.ctid</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">               ->  Seq Scan on del  (cost=0.00..2586.74 rows=99874
width=103)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">(15
rows)</span><brstyle="font-family: courier new,monospace;" /><br clear="all" style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">Best regards,</span><br /><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;">-- </span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">gurjeet[.singh]@EnterpriseDB.com</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">singh.gurjeet@{ gmail |
hotmail| indiatimes | yahoo }.com</span><br style="font-family: courier new,monospace;" /><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;">EnterpriseDB <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a></span><brstyle="font-family: courier new,monospace;"
/><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">Mail sent from my
BlackLaptopdevice </span> 

"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
> In the plan below, we can see that the optimizer is sorting an already
> sorted result. It seems to forget the sort order across the UNIQUE node. My
> question is, do we make any attempts in the optimizer to remember the sort
> order of a result, to avoid any further sorting on same sort-key? If not,
> can we do something about it?

Per the comment in create_unique_path:
   /*    * Treat the output as always unsorted, since we don't necessarily have    * pathkeys to represent it.    */
pathnode->path.pathkeys= NIL;
 

No doubt this could be improved, but I'm unsure about the effort/reward
ratio.
        regards, tom lane