agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedQuestion on PostgreSQL DB behavior w.r.t JOIN and sort order.
17+ messages / 8 participants
[nested] [flat]
* Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
@ 2016-02-09 04:29 Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 16:14 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Stuart <sfbarbee@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Venkatesan, Sekhar @ 2016-02-09 04:29 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
@ 2016-02-09 05:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
1 sibling, 1 reply; 17+ messages in thread
From: Tom Lane @ 2016-02-09 05:00 UTC (permalink / raw)
To: Venkatesan, Sekhar <sekhar.venkatesan@emc.com>; +Cc: pgsql-sql
"Venkatesan, Sekhar" <sekhar.venkatesan@emc.com> writes:
> 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.
What sort order? You did not specify any ORDER BY clause, so the DBMS is
entitled to return rows in any order it feels like.
I do not know anything about this "top 10" modifier you've got in the
SQL Server version, but I suspect it's implying a sort order. In
Postgres, if you want a specific row ordering, you need to say ORDER BY.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-02-09 05:21 ` Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:33 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Rob Sargent <robjsargent@gmail.com>
2016-02-09 05:47 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 06:43 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 3 replies; 17+ messages in thread
From: Venkatesan, Sekhar @ 2016-02-09 05:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Hi Tom,
You can disregard the "TOP 10" modifier. That was added by me to bring down the huge number of results being returned.
Even without the TOP modifier, SQL server is returning rows in sorted order (sorting columns based on the r_object_id (1st) column I think) but PostgreSQL doesn't.
Is this anything to do with indexes?
So from what I understand, you say in postgres, if the sort order is not specified, postgres returns results in any order. Am I right?
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, February 09, 2016 10:30 AM
To: Venkatesan, Sekhar
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
"Venkatesan, Sekhar" <sekhar.venkatesan@emc.com<mailto:sekhar.venkatesan@emc.com>> writes:
> 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.
What sort order? You did not specify any ORDER BY clause, so the DBMS is entitled to return rows in any order it feels like.
I do not know anything about this "top 10" modifier you've got in the SQL Server version, but I suspect it's implying a sort order. In Postgres, if you want a specific row ordering, you need to say ORDER BY.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
@ 2016-02-09 05:33 ` Rob Sargent <robjsargent@gmail.com>
2 siblings, 0 replies; 17+ messages in thread
From: Rob Sargent @ 2016-02-09 05:33 UTC (permalink / raw)
To: pgsql-sql
On 02/08/2016 10:21 PM, Venkatesan, Sekhar wrote:
>
> Hi Tom,
>
> You can disregard the "TOP 10" modifier. That was added by me to bring
> down the huge number of results being returned.
>
> Even without the TOP modifier, SQL server is returning rows in sorted
> order (sorting columns based on the r_object_id (1^st ) column I
> think) but PostgreSQL doesn’t.
>
> Is this anything to do with indexes?
>
> So from what I understand, you say in postgres, if the sort order is
> not specified, postgres returns results in any order. Am I right?
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, February 09, 2016 10:30 AM
> To: Venkatesan, Sekhar
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Question on PostgreSQL DB behavior w.r.t JOIN and
> sort order.
>
> "Venkatesan, Sekhar" <sekhar.venkatesan@emc.com
> <mailto:sekhar.venkatesan@emc.com>> writes:
>
> > 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.
>
> What sort order? You did not specify any ORDER BY clause, so the DBMS
> is entitled to return rows in any order it feels like.
>
> I do not know anything about this "top 10" modifier you've got in the
> SQL Server version, but I suspect it's implying a sort order. In
> Postgres, if you want a specific row ordering, you need to say ORDER BY.
>
> regards, tom lane
>
In my experience, this is ofter termed "disc order", implying what ever
order the resultant tuples were discovered while processing the data.
If MSSQL server is giving an order without explicit instruction to do so
you may be incurring an unwanted sort operation. Is (any of) the data
in a "clustered index": iirc that implies an on-disc ordering and the
result set my be reflecting that.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
@ 2016-02-09 05:47 ` David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:48 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2016-02-09 05:47 UTC (permalink / raw)
To: Venkatesan, Sekhar <sekhar.venkatesan@emc.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On Monday, February 8, 2016, Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
wrote:
>
> So from what I understand, you say in postgres, if the sort order is not
> specified, postgres returns results in any order. Am I right?
>
Yes. It will optimize for speed without any regard for maintaining any
kind of ordering. You may get the desired order for various reasons but
without ORDER BY you cannot be guaranteed.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:47 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
@ 2016-02-09 05:48 ` Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:50 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Venkatesan, Sekhar @ 2016-02-09 05:48 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Is there a way to tell the optimizer to retain the sort order if that is possible please?
From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Tuesday, February 09, 2016 11:17 AM
To: Venkatesan, Sekhar
Cc: Tom Lane; pgsql-sql@postgresql.org
Subject: Re: [SQL] Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
On Monday, February 8, 2016, Venkatesan, Sekhar <sekhar.venkatesan@emc.com<mailto:sekhar.venkatesan@emc.com>> wrote:
So from what I understand, you say in postgres, if the sort order is not specified, postgres returns results in any order. Am I right?
Yes. It will optimize for speed without any regard for maintaining any kind of ordering. You may get the desired order for various reasons but without ORDER BY you cannot be guaranteed.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:47 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:48 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
@ 2016-02-09 05:50 ` David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:53 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
0 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2016-02-09 05:50 UTC (permalink / raw)
To: Venkatesan, Sekhar <sekhar.venkatesan@emc.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On Monday, February 8, 2016, Venkatesan, Sekhar <sekhar.venkatesan@emc.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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:47 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:48 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:50 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
@ 2016-02-09 05:53 ` Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:57 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Rob Sargent <robjsargent@gmail.com>
2016-02-09 06:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 06:01 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 3 replies; 17+ messages in thread
From: Venkatesan, Sekhar @ 2016-02-09 05:53 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Yes. is there an option/configuration to tell the postgres optimizer/planner to generate plans to include the sort order instead of speed?
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 sort order.
On Monday, February 8, 2016, Venkatesan, Sekhar <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 is possible please?
You mean, besides the ORDER BY clause?
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:47 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:48 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:50 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:53 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
@ 2016-02-09 05:57 ` Rob Sargent <robjsargent@gmail.com>
2016-02-09 06:03 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 1 reply; 17+ messages in thread
From: Rob Sargent @ 2016-02-09 05:57 UTC (permalink / raw)
To: pgsql-sql
On 02/08/2016 10: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?
>
> *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
> sort order.
>
> On Monday, February 8, 2016, Venkatesan, Sekhar
> <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
> is possible please?
>
> You mean, besides the ORDER BY clause?
>
> David J.
>
Which order would that be?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:47 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:48 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:50 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:53 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:57 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Rob Sargent <robjsargent@gmail.com>
@ 2016-02-09 06:03 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: David G. Johnston @ 2016-02-09 06:03 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql
On Monday, February 8, 2016, Rob Sargent <robjsargent@gmail.com> wrote:
> On 02/08/2016 10: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?
>
>
>
> *From:* David G. Johnston [mailto:david.g.johnston@gmail.com
> <javascript:_e(%7B%7D,'cvml','david.g.johnston@gmail.com');>]
> *Sent:* Tuesday, February 09, 2016 11:20 AM
> *To:* Venkatesan, Sekhar
> *Cc:* Tom Lane; pgsql-sql@postgresql.org
> <javascript:_e(%7B%7D,'cvml','pgsql-sql@postgresql.org');>
> *Subject:* Re: [SQL] Question on PostgreSQL DB behavior w.r.t JOIN and
> sort order.
>
>
>
> On Monday, February 8, 2016, Venkatesan, Sekhar <sekhar.venkatesan@emc.com
> <javascript:_e(%7B%7D,'cvml','sekhar.venkatesan@emc.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.
>
> Which order would that be?
>
>
I presume the order of the joining column(s) given the subject. Not that
it matters but I don't know how generalized the OP expects such a mechanic
to ultimately function, or thinks it does from limited observations in a
different database product.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:47 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:48 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:50 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:53 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
@ 2016-02-09 06:00 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 17+ messages in thread
From: David G. Johnston @ 2016-02-09 06:00 UTC (permalink / raw)
To: Venkatesan, Sekhar <sekhar.venkatesan@emc.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On Monday, February 8, 2016, Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
wrote:
> Yes. is there an option/configuration to tell the postgres
> optimizer/planner to generate plans to include the sort order instead of
> speed?
>
No. The planner chooses based upon least cost of the exact query given to
it. If that query does not have order by the system will not guarantee any
specific output order.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:47 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:48 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:50 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:53 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
@ 2016-02-09 06:01 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-09 06:14 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2 siblings, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2016-02-09 06:01 UTC (permalink / raw)
To: Venkatesan, Sekhar <sekhar.venkatesan@emc.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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.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
> sort order.
>
> On Monday, February 8, 2016, Venkatesan, Sekhar
> <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 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:47 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:48 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:50 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:53 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 06:01 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Adrian Klaver <adrian.klaver@aklaver.com>
@ 2016-02-09 06:14 ` Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 06:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 12:41 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Mike Sofen <msofen@runbox.com>
0 siblings, 2 replies; 17+ messages in thread
From: Venkatesan, Sekhar @ 2016-02-09 06:14 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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.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 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.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
> sort order.
>
> On Monday, February 8, 2016, Venkatesan, Sekhar
> <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
> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:47 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:48 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:50 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:53 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 06:01 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-09 06:14 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
@ 2016-02-09 06:21 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: David G. Johnston @ 2016-02-09 06:21 UTC (permalink / raw)
To: Venkatesan, Sekhar <sekhar.venkatesan@emc.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On Monday, February 8, 2016, Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
wrote:
> 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?
>
>
ORDER BY is SQL standard. Add it and call it a day. You are relying on
undocumented implementation details otherwise.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:47 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:48 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:50 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:53 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 06:01 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-09 06:14 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
@ 2016-02-09 12:41 ` Mike Sofen <msofen@runbox.com>
1 sibling, 0 replies; 17+ messages in thread
From: Mike Sofen @ 2016-02-09 12:41 UTC (permalink / raw)
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
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@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.johnston@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 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.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 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.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
> sort order.
>
> On Monday, February 8, 2016, Venkatesan, Sekhar
> <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
> 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
@ 2016-02-09 06:43 ` Thomas Kellerer <spam_eater@gmx.net>
2 siblings, 0 replies; 17+ messages in thread
From: Thomas Kellerer @ 2016-02-09 06:43 UTC (permalink / raw)
To: pgsql-sql
Venkatesan, Sekhar schrieb am 09.02.2016 um 06:21:
> So from what I understand, you say in postgres, if the sort order is not specified,
> postgres returns results in any order. Am I right?
This is nothing Postgres specific. This is true for *every* DBMS.
Without an order by, the DBMS is free to return the rows in any order it wants.
If you have seen a specific order in SQL Server that was pure coincidence and can *not* be relied upon.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Question on PostgreSQL DB behavior w.r.t JOIN and sort order.
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
@ 2016-02-09 16:14 ` Stuart <sfbarbee@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Stuart @ 2016-02-09 16:14 UTC (permalink / raw)
To: Venkatesan, Sekhar <sekhar.venkatesan@emc.com>; +Cc: pgsql-sql
Sekhar,
You will have to specify a sort order with "order by <field>" clause before
the limit clause. It's the only way I know the order to be guaranteed to
remain the same. Hope this helps.
Stuart
On Feb 9, 2016 08:30, "Venkatesan, Sekhar" <sekhar.venkatesan@emc.com>
wrote:
> 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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2016-02-09 16:14 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-02-09 04:29 Question on PostgreSQL DB behavior w.r.t JOIN and sort order. Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-02-09 05:21 ` Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:33 ` Rob Sargent <robjsargent@gmail.com>
2016-02-09 05:47 ` David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:48 ` Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:50 ` David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 05:53 ` Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 05:57 ` Rob Sargent <robjsargent@gmail.com>
2016-02-09 06:03 ` David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 06:00 ` David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 06:01 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-02-09 06:14 ` Venkatesan, Sekhar <sekhar.venkatesan@emc.com>
2016-02-09 06:21 ` David G. Johnston <david.g.johnston@gmail.com>
2016-02-09 12:41 ` Mike Sofen <msofen@runbox.com>
2016-02-09 06:43 ` Thomas Kellerer <spam_eater@gmx.net>
2016-02-09 16:14 ` Stuart <sfbarbee@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox