Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.

From: "Mike Sofen" <msofen(at)runbox(dot)com>
To: "'Venkatesan, Sekhar'" <sekhar(dot)venkatesan(at)emc(dot)com>, "'Adrian Klaver'" <adrian(dot)klaver(at)aklaver(dot)com>, "'David G(dot) Johnston'" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
Date: 2016-02-09 12:41:16
Message-ID: 029801d16337$2ca30f90$85e92eb0$@runbox.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Actually, the behavior you've seen in SQL Server may be a pure artifact of the table structures underneath your queries.

Most database architects will (appropriately) put a primary key on every table and the default in SQL Server is to make primary keys clustered...and clustering arranges the physical storage of the rows in the increasing order of that key. If that is what exists in the SQL Server db, then KH_.r_object_id would be a clustered PK and so of course would return rows in that order, automatically. As Kellerer said, otherwise it is random ordering, without an Order By clause.

Postgres PKs are not clustered by default, so you'll experience the random row ordering you mentioned. Cluster that column and you'll get the same behavior...but read up on postgres clustering since it works very differently than SQL Server.

Mike

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Venkatesan, Sekhar
Sent: Monday, February 08, 2016 10:15 PM
To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>; David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Question on PostgreSQL DB behavior w.r.t JOIN and sort order.

My concern here is that I want to maintain consistency ( in our application to retain sort order) between different databases.
I don't see the issue in SQL Server and Oracle databases.
"SELECT 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 "

The above query is sorted based on the first column in the select list. Same is not happening in PostgreSQL.
Is this something to do with collation setting in database?

-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
Sent: Tuesday, February 09, 2016 11:32 AM
To: Venkatesan, Sekhar; David G. Johnston
Cc: Tom Lane; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Question on PostgreSQL DB behavior w.r.t JOIN and sort order.

On 02/08/2016 09:53 PM, Venkatesan, Sekhar wrote:
> Yes. is there an option/configuration to tell the postgres
> optimizer/planner to generate plans to include the sort order instead
> of speed?

What columns in a table would that be and then what order?

>
> *From:*David G. Johnston [mailto:david(dot)g(dot)johnston(at)gmail(dot)com]
> *Sent:* Tuesday, February 09, 2016 11:20 AM
> *To:* Venkatesan, Sekhar
> *Cc:* Tom Lane; pgsql-sql(at)postgresql(dot)org
> *Subject:* Re: [SQL] Question on PostgreSQL DB behavior w.r.t JOIN and
> sort order.
>
> On Monday, February 8, 2016, Venkatesan, Sekhar
> <sekhar(dot)venkatesan(at)emc(dot)com <mailto:sekhar(dot)venkatesan(at)emc(dot)com>> wrote:
>
> Is there a way to tell the optimizer to retain the sort order if that
> is possible please?
>
> You mean, besides the ORDER BY clause?
>
> David J.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

--
Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Stuart 2016-02-09 16:14:06 Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
Previous Message Thomas Kellerer 2016-02-09 06:43:03 Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.