Re: [RFE] auto ORDER BY for SELECT - Mailing list pgsql-general

From Pavel Stehule
Subject Re: [RFE] auto ORDER BY for SELECT
Date
Msg-id CAFj8pRAH2G7EjGZOqJNp5vR-Qeu5gST4gCt85dPjiYfCP77_-w@mail.gmail.com
Whole thread Raw
In response to [RFE] auto ORDER BY for SELECT  (Douglas Eric <sekkuar@hotmail.com>)
List pgsql-general
Hello

2012/1/23 Douglas Eric <sekkuar@hotmail.com>:
> I'm not sure if this is the right list to discuss this, but, I have a
> suggestion:
>
> ORDER BY clause, as defined in the SELECT documentation says:
>
> "If ORDER BY is not given, the rows are returned in whatever order the
> system finds fastest to produce"
>
> This order is usually not wanted, as it is not predictable. I believe many
> people would expect  the order of rows
> returned in this case, to be ordered as the primary key of the table, or the
> same order the rows were inserted.
>
> I suggest to change this behavior. If one makes a SELECT statement without
> any ORDER BY, it would be
> clever to automatically sort by the first primary key found in the query, if
> any.
> The present behavior would still be used in case of queries without any
> primary key fields.
>
> This would save a lot of repeated clauses "ORDER BY <table primary key>"
> that we have to add to every SELECT, even the most simple one
> "SELECT * FROM <table>"
> If we actually want the order of the rows to make any sense.

* I don't think so this is good idea. Any sort is not cheap - so ORDER
BY "hint" - yes, user can do expensive operation.

* second argument

SELECT * FROM longtab LIMIT 100

is significantly faster than

SELECT * FROM longtab ORDER BY PK LIMIT 100;

so implicit ORDER BY can significantly increase a load of server

Regards

Pavel Stehule

pgsql-general by date:

Previous
From: Bill Moran
Date:
Subject: Re: Schema version control
Next
From: Andreas Kretschmer
Date:
Subject: Re: [RFE] auto ORDER BY for SELECT