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

["Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>]

On Tue, Dec 23, 2008 at 11:14 AM, George Pavlov <gpavlov(at)mynewplace(dot)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.