Обсуждение: default ordering of query result - are they always guarantee

I have the following table :

        CREATE TABLE measurement_1
        (
          measurement_id serial NOT NULL,
          fk_unit_id int NOT NULL,
          "value" double precision,
          measurement_type text NOT NULL,
          measurement_unit text NOT NULL
        );

When I want to retrieve the query. By default, the query result are ordered in ascending order, by using
measurement_id.

SELECT measurement_type, value, measurement_unit
    FROM
    measurement_1

This is the default behavior I want. However, I am not sure whether this is always guarantee? Or shall I explicitly
makethe query in the following form? Will this have performance impact on row with millions? 

SELECT measurement_type, value, measurement_unit
    FROM
    measurement_1 ORDER BY measurement_id ASC

Thanks.

by ensuring measurement_1 result are ordered in ascending
Thanks and Regards
Yan Cheng CHEOK

Le 19/05/2010 05:06, Yan Cheng CHEOK a écrit :
> I have the following table :
>
>         CREATE TABLE measurement_1
>         (
>           measurement_id serial NOT NULL,
>           fk_unit_id int NOT NULL,
>           "value" double precision,
>           measurement_type text NOT NULL,
>           measurement_unit text NOT NULL
>         );
>
> When I want to retrieve the query. By default, the query result are ordered in ascending order, by using
measurement_id.
>
> SELECT measurement_type, value, measurement_unit
>     FROM
>     measurement_1
>
> This is the default behavior I want. However, I am not sure whether this is always guarantee? Or shall I explicitly
makethe query in the following form? 

An you're right. It's not guaranted. The only guaranted way is to use
ORDER BY your_column.


--
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

In response to Guillaume Lelarge :
> > This is the default behavior I want. However, I am not sure whether this is always guarantee? Or shall I explicitly
makethe query in the following form? 
>
> An you're right. It's not guaranted. The only guaranted way is to use
> ORDER BY your_column.

ACK. One reason is the new feature 'synchronised scan' sind 8.3. It's a
big performance boost, but engendered unsorted results. (if there is not
the ORDER BY - statement)

Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

On Wed, May 19, 2010 at 1:38 AM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:
> In response to Guillaume Lelarge :
>> > This is the default behavior I want. However, I am not sure whether this is always guarantee? Or shall I
explicitlymake the query in the following form? 
>>
>> An you're right. It's not guaranted. The only guaranted way is to use
>> ORDER BY your_column.
>
> ACK. One reason is the new feature 'synchronised scan' sind 8.3. It's a
> big performance boost, but engendered unsorted results. (if there is not
> the ORDER BY - statement)

Just for reference I once had three separate oracle dbas saying my pg
server was broken because group by came out in random order (due to
hash aggregates).

> -----Original Message-----
> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> Sent: Wednesday, May 19, 2010 4:15 AM
> To: A. Kretschmer
> Cc: pgsql-general@postgresql.org
> Subject: Re: default ordering of query result - are they
> always guarantee
>
> ...................................
> ...................................
>
> Just for reference I once had three separate oracle dbas
> saying my pg server was broken because group by came out in
> random order (due to hash aggregates).
>

Must be not very good dbas :)
Oracle never guaranteed that "GROUP BY" returns sorted results.
It's just happened that in earlier Oracle versions they were sorted due
to algorithm being used for GROUP BY.
But again, Oracle never guaranteed it.

On Thu, May 20, 2010 at 8:11 AM, Igor Neyman <ineyman@perceptron.com> wrote:
>
>
>> -----Original Message-----
>> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
>> Sent: Wednesday, May 19, 2010 4:15 AM
>> To: A. Kretschmer
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: default ordering of query result - are they
>> always guarantee
>>
>> ...................................
>> ...................................
>>
>> Just for reference I once had three separate oracle dbas
>> saying my pg server was broken because group by came out in
>> random order (due to hash aggregates).
>>
>
> Must be not very good dbas :)
> Oracle never guaranteed that "GROUP BY" returns sorted results.
> It's just happened that in earlier Oracle versions they were sorted due
> to algorithm being used for GROUP BY.
> But again, Oracle never guaranteed it.

Actually they were pretty good, but this was several years ago, and
they only had experience with Oracle 8, 9 was all shiny and new to
them.  And they had the typical "If Oracle does X, it must be the way
things should be everywhere" Oracle DBA attitude.