Scott Marlowe wrote:
On Tue, Dec 23, 2008 at 11:14 AM, George Pavlov <gpavlov@xxxxxxxxxxxxxx> wrote:
You don't say what PG version you are on, but just for kicks you may try
using GROUP BY instead of DISTINCT. Yes, the two should perform the
same, but with 8.1 (or maybe 8.0) I had seen situations where GROUP BY
was faster (admittedly this happened with more complex queries). So, try
this:
Even in 8.3 it looks like group by is faster. Tested it on a decent
sized table and group by used a hash agg and ran in ~600 ms, while
distinct used a sort and ran in 1300 ms. That was on 500k rows. On a
much larger table, one with about 10M rows, a similar statement runs
in 1500 ms with group by and in 2390 ms when run with distinct.
Not surprising - this is a known limitation in all released versions of
postgresql (GROUP BY can use hashing and sorting - DISTINCT only
sorting). 8.4 is going to improve that though.
Stefan
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
