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:
CREATE TEMP TABLE huge_table AS SELECT x, y FROM foo GROUP BY 1, 2;
Note that you may be tempted to add an index on foo(x,y), but I don't
think that helps (or at least I have not been able to hit the index in
similar situations).
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-
> owner@postgresql.org] On Behalf Of Kynn Jones
> Sent: Tuesday, December 23, 2008 9:26 AM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] How to "unique-ify" HUGE table?
>
> Hi everyone!
>
> I have a very large 2-column table (about 500M records) from which I
> want to remove duplicate records.
>
> I have tried many approaches, but they all take forever.
>
> The table's definition consists of two short TEXT columns. It is a
> temporary table generated from a query:
>
>
> CREATE TEMP TABLE huge_table AS SELECT x, y FROM ... ;
>
> Initially I tried
>
> CREATE TEMP TABLE huge_table AS SELECT DISTINCT x, y FROM ... ;
>
> but after waiting for nearly an hour I aborted the query, and repeated
> it after getting rid of the DISTINCT clause.
>
> Everything takes forever with this monster! It's uncanny. Even
> printing it out to a file takes forever, let alone creating an index
> for it.
>
> Any words of wisdom on how to speed this up would be appreciated.
>
> TIA!
>
> Kynn
>
>
