"Order by" degraded performance in sql

Hi i have one problem while executing sql in postgresql.

I have a similar query like this:

sel开发者_如何学Goect A, B , lower(C) from myTable ORDER BY A, B ;

WIthout ORDER BY clause, I get the result in 11 ms , but with order by , it took more than 4 minutes to retrieve the same results.

These column contains lots of data (1000000 or more) and has lot of duplicate data Can any one suggest me solution??

Thank you

---

but with order by , it took more than 4 minutes to retrieve the same results.

udo already explained how indexes can be used to speed up sorting, this is probably the way you want to go.

But another solution (probably) is increasing the work_mem variable. This is almost always beneficial, unless you have many queries running at the same time.

When sorting large result sets, which don't fit in your work_mem setting, PostgreSQL resorts to a slow disk-based sort. If you allow it to use more memory, it will do fast in-memory sorts instead.

NB! Whenever you ask questions about PostgreSQL performance, you should post the EXPLAIN ANALYZE output for your query, and also the version of Postgres.

---

Have you tried putting an index on A,B?

That should speed things up.

---

Did you try using a DISTINCT for eliminating duplicates? This should be more efficient than an order by statement.