Optimizer sorting an already sorted result

------=_Part_15201_25777672.1209518730967
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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?

postgres=# explain select * from del where ctid in ( select ('''(' || i ||
',' || j || ')''')::tid from generate_series( 0, 1) s1(i),
generate_series(
1, 1 ) s2(j) );
                                                       QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=177447.07..182043.29 rows=40000 width=97)
   Merge Cond: ((((((('''('::text || (s1.i)::text) || ','::text) ||
(s2.j)::text) || ')'''::text))::tid) = del.ctid)
   ->  Sort  (cost=155639.89..155739.89 rows=40000 width=8)
         Sort Key: (((((('''('::text || (s1.i)::text) || ','::text) ||
(s2.j)::text) || ')'''::text))::tid)
         ->  Unique  (cost=147032.84..152032.84 rows=40000 width=8)
               ->  Sort  (cost=147032.84..149532.84 rows=1000000 width=8)
                     Sort Key: (((((('''('::text || (s1.i)::text) ||
','::text) || (s2.j)::text) || ')'''::text))::tid)
                     ->  Nested Loop  (cost=13.50..20026.00 rows=1000000
width=8)
                           ->  Function Scan on generate_series s1
(cost=0.00..12.50 rows=1000 width=4)
                           ->  Materialize  (cost=13.50..23.50 rows=1000
width=4)
                                 ->  Function Scan on generate_series s2
(cost=0.00..12.50 rows=1000 width=4)
   ->  Materialize  (cost=21807.19..23055.61 rows=99874 width=103)
         ->  Sort  (cost=21807.19..22056.87 rows=99874 width=103)
               Sort Key: del.ctid
               ->  Seq Scan on del  (cost=0.00..2586.74 rows=99874
width=103)
(15 rows)

Best regards,

-- 
gurjeet[.singh]@[EMAIL PROTECTED]
 gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

------=_Part_15201_25777672.1209518730967
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<span style="font-family: courier new,monospace;">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?<br>
<br>postgres=# explain select * from del where ctid in ( select
(&#39;&#39;&#39;(&#39; || i || &#39;,&#39; || j ||
&#39;)&#39;&#39;&#39;)::tid from 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;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
QUERY
PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier
new,monospace;">------------------------------------------------------------------------------------------------------------------------</span><br
style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">&nbsp;Merge Join&nbsp;
(cost=177447.07..182043.29 rows=40000 width=97)</span><br
style="font-family: courier new,monospace;"><span style="font-family:
courier new,monospace;">&nbsp;&nbsp; Merge Cond:
(((((((&#39;&#39;&#39;(&#39;::text || (s1.i)::text) || &#39;,&#39;::text)
|| (s2.j)::text) || &#39;)&#39;&#39;&#39;::text))::tid) =
del.ctid)</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">&nbsp;&nbsp; -&gt;&nbsp;
Sort&nbsp; (cost=155639.89..155739.89 rows=40000 width=8)</span><br
style="font-family: courier new,monospace;"><span style="font-family:
courier new,monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Sort Key: ((((((&#39;&#39;&#39;(&#39;::text || (s1.i)::text) ||
&#39;,&#39;::text) || (s2.j)::text) ||
&#39;)&#39;&#39;&#39;::text))::tid)</span><br style="font-family: courier
new,monospace;">
<span style="font-family: courier
new,monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-&gt;&nbsp; Unique&nbsp; (cost=147032.84..152032.84 rows=40000
width=8)</span><br style="font-family: courier new,monospace;"><span
style="font-family: courier
new,monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-&gt;&nbsp; Sort&nbsp; (cost=147032.84..149532.84 rows=1000000
width=8)</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier
new,monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Sort Key: ((((((&#39;&#39;&#39;(&#39;::text || (s1.i)::text) ||
&#39;,&#39;::text) || (s2.j)::text) ||
&#39;)&#39;&#39;&#39;::text))::tid)</span><br style="font-family: courier
new,monospace;">
<span style="font-family: courier
new,monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-&gt;&nbsp; Nested Loop&nbsp; (cost=13.50..20026.00 rows=1000000
width=8)</span><br style="font-family: courier new,monospace;"><span
style="font-family: courier
new,monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-&gt;&nbsp; Function Scan on generate_series s1&nbsp; (cost=0.00..12.50
rows=1000 width=4)</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier
new,monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-&gt;&nbsp; Materialize&nbsp; (cost=13.50..23.50 rows=1000
width=4)</span><br style="font-family: courier new,monospace;"><span
style="font-family: courier
new,monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-&gt;&nbsp; Function Scan on generate_series s2&nbsp; (cost=0.00..12.50
rows=1000 width=4)</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">&nbsp;&nbsp; -&gt;&nbsp;
Materialize&nbsp; (cost=21807.19..23055.61 rows=99874 width=103)</span><br
style="font-family: courier new,monospace;"><span style="font-family:
courier new,monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-&gt;&nbsp; Sort&nbsp; (cost=21807.19..22056.87 rows=99874
width=103)</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier
new,monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Sort Key: del.ctid</span><br style="font-family: courier
new,monospace;"><span style="font-family: courier
new,monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-&gt;&nbsp; Seq Scan on del&nbsp; (cost=0.00..2586.74 rows=99874
width=103)</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">(15 rows)</span><br
style="font-family: courier new,monospace;"><br style="font-family:
courier new,monospace;" clear="all"><span style="font-family: courier
new,monospace;">Best regards,</span><br>
<br style="font-family: courier new,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]@[EMAIL PROTECTED]
><br
style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">singh.gurjeet@[EMAIL PROTECTED]
 gmail |
hotmail | indiatimes | yahoo }.com</span><br style="font-family: courier
new,monospace;"><br style="font-family: courier new,monospace;"><span
style="font-family: courier new,monospace;">EnterpriseDB <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a></span><br
style="font-family: courier new,monospace;">
<br style="font-family: courier new,monospace;"><span style="font-family:
courier new,monospace;">Mail sent from my BlackLaptop device
</span>

------=_Part_15201_25777672.1209518730967--