Question on PostgreSQL DB behavior w.r.t JOIN and sort order. - Mailing list pgsql-sql

<div class="WordSection1"><p class="MsoNormal">Hi folks,<p class="MsoNormal"> <p class="MsoNormal">I am seeing this
behaviorchange in postgreSQL DB when compared to SQL Server DB when JOIN is performed. The sort order is not retained
whenJOIN is performed in PostgreSQL DB.<p class="MsoNormal">Is it expected? Is there a solution available to retain the
sortorder during JOIN? We have applications that expects the same sort order during JOIN and we want to support our
applicationon PostgreSQL DB.<p class="MsoNormal">DO we need to indicate to the PostgreSQL DB optimizer to not change
thesort order? If so, how to do it and what are it’s implications.<p class="MsoNormal"> <p class="MsoNormal">From the
belowexample, you can see that the results are not in sorted order in PostgreSQL when compared to SQL Server DB.<p
class="MsoNormal"> <pclass="MsoNormal"><b>SQLServer:</b><p class="MsoNormal"> <p class="MsoNormal"
style="text-autospace:none"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue">SELECT</span><span
style="font-size:10.0pt;font-family:"CourierNew""> <span style="color:blue">top</span> 10    KH_<span
style="color:gray">.</span>r_object_id<spanstyle="color:gray">,</span> KH_<span style="color:gray">.</span><span
style="color:fuchsia">object_name</span><span style="color:blue">FROM</span>         dbo<span
style="color:gray">.</span>dm_location_s<span style="color:blue">AS</span> ZS_ <span style="color:gray">INNER</span>
<spanstyle="color:gray"> JOIN</span></span><p class="MsoNormal" style="text-autospace:none"><span
style="font-size:10.0pt;font-family:"CourierNew"">                      dbo<span
style="color:gray">.</span>dm_sysobject_s<span style="color:blue">AS</span> KH_ <span style="color:blue">ON</span>
ZS_<spanstyle="color:gray">.</span>r_object_id <span style="color:gray">=</span> KH_<span
style="color:gray">.</span>r_object_id</span><p class="MsoNormal">3a00d5128000013f           storage_01<p
class="MsoNormal">3a00d51280000140         common<p class="MsoNormal">3a00d51280000141          events<p
class="MsoNormal">3a00d51280000142         log<p class="MsoNormal">3a00d51280000143          config<p
class="MsoNormal">3a00d51280000144         dm_dba<p class="MsoNormal">3a00d51280000145          auth_plugin<p
class="MsoNormal">3a00d51280000146         ldapcertdb_loc<p class="MsoNormal">3a00d51280000147          temp<p
class="MsoNormal">3a00d51280000148         dm_ca_store_fetch_location<p class="MsoNormal"> <p
class="MsoNormal"><b>PostgreSQL:</b><pclass="MsoNormal"> <p class="MsoNormal">dm_repo6_docbase=> SELECT
KH_.r_object_id,KH_.object_name FROM dm_location_s AS  ZS_ INNER JOIN dm_sysobject_s AS KH_ ON ZS_.r_object_id =
KH_.r_object_idlimit  10;<p class="MsoNormal"> <p class="MsoNormal">   r_object_id    |        object_name<p
class="MsoNormal">------------------+---------------------------<pclass="MsoNormal">3a0003e98000a597 |
TDfFXMigrateRMOPDQ71486_1<pclass="MsoNormal">3a0003e980007679 | 738296_2<p class="MsoNormal">3a0003e980000142 | log<p
class="MsoNormal">3a0003e980000143| config<p class="MsoNormal">3a0003e980000140 | common<p
class="MsoNormal">3a0003e98000013f| storage_01<p class="MsoNormal">3a0003e980000141 | events<p
class="MsoNormal">3a0003e980000144| dm_dba<p class="MsoNormal">3a0003e980000145 | auth_plugin<p
class="MsoNormal">3a0003e980000146| ldapcertdb_loc<p class="MsoNormal">(10 rows)<p class="MsoNormal"> <p
class="MsoNormal">Thanks,<pclass="MsoNormal">Sekhar</div>