From: "Mike Sofen" <msofen@runbox.com>
To: "'Venkatesan, Sekhar'" <sekhar.venkatesan@emc.com>,
 "'Adrian Klaver'" <adrian.klaver@aklaver.com>,
 "'David G. Johnston'" <david.g.johnston@gmail.com>
Cc: "'Tom Lane'" <tgl@sss.pgh.pa.us>,
	<pgsql-sql@postgresql.org>
References: <F84DE43FDACD4C45AA84E2DA016FAE2F1C65BE54@MX105CL01.corp.emc.com>
 <21082.1454994009@sss.pgh.pa.us>
 <F84DE43FDACD4C45AA84E2DA016FAE2F1C65BEC8@MX105CL01.corp.emc.com>
 <CAKFQuwbye-t24D59BUR05YyUtuaiFgCQWHMVq6qcz0axsKrT=A@mail.gmail.com>
 <F84DE43FDACD4C45AA84E2DA016FAE2F1C65BF07@MX105CL01.corp.emc.com>
 <CAKFQuwbmAtkv8tHq3CuiRH0encj59SRHBMAMezW1x-6CVauLnw@mail.gmail.com>
 <F84DE43FDACD4C45AA84E2DA016FAE2F1C65BF1C@MX105CL01.corp.emc.com>
 <56B980D1.7010509@aklaver.com>
 <F84DE43FDACD4C45AA84E2DA016FAE2F1C65BF60@MX105CL01.corp.emc.com>
In-Reply-To: <F84DE43FDACD4C45AA84E2DA016FAE2F1C65BF60@MX105CL01.corp.emc.com>
Subject: Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
Date: Tue, 9 Feb 2016 04:41:16 -0800
Message-ID: <029801d16337$2ca30f90$85e92eb0$@runbox.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Language: en-us
Thread-Index: 
 AQMXg80j7mym7Udm3sOj/1xyLd/21wJLCRJGAb94P/gAosthCwGaKjVFArpir0AC6zWOVQF0mHl4AkkkdaCcGg20kA==
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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

Most database architects will (appropriately) put a primary key on every ta=
ble and the default in SQL Server is to make primary keys clustered...and c=
lustering 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_obje=
ct_id would be a clustered PK and so of course would return rows in that or=
der, automatically.  As Kellerer said, otherwise it is random ordering, wit=
hout 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 be=
havior...but read up on postgres clustering since it works very differently=
 than SQL Server.

Mike

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

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.=20
"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 =3D KH_.=
r_object_id "

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

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

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

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

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


--
Adrian Klaver
adrian.klaver@aklaver.com

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


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