Mason Harding <mason.harding@xxxxxxxxx> writes: > Hi all. I Have the following query (tested in postgres 8.4 and 9.0rc1) > SELECT distinct event0_.* > FROM event event0_ inner join account account1_ on > event0_.account_id_owner=account1_.account_id > LEFT OUTER JOIN friend friendcoll2_ ON > account1_.account_id=friendcoll2_.friend_account_id > WHERE (event0_.account_id_owner=2 or friendcoll2_.account_id=2 > AND friendcoll2_.status=2 AND (event0_.is_recomended is null OR > event0_.is_recomended=false)) > ORDER BY event0_.event_id DESC LIMIT 25 > None of the tables listed here have more than a couple of thousand rows, and > are all indexed. If I run that query as is, it will take up to 5 seconds, > if I remove the ORDER BY and LIMIT, it will run into about 200 ms. The reason it's sorting by all the columns is the DISTINCT: that's implemented by a sort-and-unique type of scheme so it has to be sure that all the columns are sorted. You didn't show the non-ORDER-BY plan, but I suspect it's preferring a hash aggregation approach to doing the DISTINCT if it doesn't have to produce sorted output. The easiest way to make that query faster would be to raise work_mem enough so that the sort doesn't have to spill to disk. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
