Re: How to "unique-ify" HUGE table?

Scott Marlowe wrote:
> On Tue, Dec 23, 2008 at 11:14 AM, George Pavlov <gpavlov@mynewplace.com> 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