Hi folks,
I am seeing this behavior change in postgreSQL DB when compared to SQL Server DB when JOIN is performed. The sort order is not retained when JOIN is performed in PostgreSQL DB.
Is it expected? Is there a solution available to retain the sort order during JOIN? We have applications that expects the same sort order during JOIN and we want to support our application on PostgreSQL DB.
DO we need to indicate to the PostgreSQL DB optimizer to not change the sort order? If so, how to do it and what are it’s implications.
From the below example, you can see that the results are not in sorted order in PostgreSQL when compared to SQL Server DB.
SQLServer:
SELECT top 10 KH_.r_object_id, KH_.object_name FROM dbo.dm_location_s AS ZS_ INNER JOIN
dbo.dm_sysobject_s AS KH_ ON ZS_.r_object_id = KH_.r_object_id
3a00d5128000013f storage_01
3a00d51280000140 common
3a00d51280000141 events
3a00d51280000142 log
3a00d51280000143 config
3a00d51280000144 dm_dba
3a00d51280000145 auth_plugin
3a00d51280000146 ldapcertdb_loc
3a00d51280000147 temp
3a00d51280000148 dm_ca_store_fetch_location
PostgreSQL:
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_id limit 10;
r_object_id | object_name
------------------+---------------------------
3a0003e98000a597 | TDfFXMigrateRMOPDQ71486_1
3a0003e980007679 | 738296_2
3a0003e980000142 | log
3a0003e980000143 | config
3a0003e980000140 | common
3a0003e98000013f | storage_01
3a0003e980000141 | events
3a0003e980000144 | dm_dba
3a0003e980000145 | auth_plugin
3a0003e980000146 | ldapcertdb_loc
(10 rows)
Thanks,
Sekhar