Thread: Order By Clause, Slows Query Performance?

Order By Clause, Slows Query Performance?

From
monalee_dba
Date:
Eg. SELECT col1, col2, col3,....col10 FROM table1;

For above query If I didn't mention ORDER BY clause, then I want to know
selected data will appear in which order by a query planner?

Because I have huge size table, and when I applied ORDER BY col1, col2..in
query the
performance is soo bad that I can't offred.
What should I do ? Because my requirement is data with ordered column.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Order-By-Clause-Slows-Query-Performance-tp5777633.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


Re: Order By Clause, Slows Query Performance?

From
Michael Paquier
Date:
On Sun, Nov 10, 2013 at 4:40 PM, monalee_dba
<monalee@sungraceinfotech.co.in> wrote:
> Eg. SELECT col1, col2, col3,....col10 FROM table1;
>
> For above query If I didn't mention ORDER BY clause, then I want to know
> selected data will appear in which order by a query planner?
The data will be selected in the order at which it is scanned.

> Because I have huge size table, and when I applied ORDER BY col1, col2..in
> query the
> performance is soo bad that I can't offred.
> What should I do ? Because my requirement is data with ordered column.
Redesign your application. The larger your relation "table1" would
get, the more data you would fetch and the slower it would get. The
best advice I got here would be first to analyze why you need to fetch
that much data back to your application. Particularly, if your
application fetches that much data and post-treats it internally, you
should try to maximize such processing on the database server side and
not the application side to minimize the amount of data exchanged
between the database server and the application client.

Note that you might as well consider changing your schema.
--
Michael


Re: Order By Clause, Slows Query Performance?

From
Albe Laurenz
Date:
monalee_dba wrote:
> Eg. SELECT col1, col2, col3,....col10 FROM table1;
> 
> For above query If I didn't mention ORDER BY clause, then I want to know
> selected data will appear in which order by a query planner?
> 
> Because I have huge size table, and when I applied ORDER BY col1, col2..in
> query the
> performance is soo bad that I can't offred.
> What should I do ? Because my requirement is data with ordered column.

A B-Tree index may help with that:
http://www.postgresql.org/docs/current/static/indexes-ordering.html
Consider a multicolumn index.

Yours,
Laurenz Albe