Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0CDC565012B for ; Tue, 23 Dec 2008 13:45:24 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 23563-03 for ; Tue, 23 Dec 2008 13:45:21 -0400 (AST) X-Greylist: delayed 00:06:00.193761 by SQLgrey-1.7.6 Received: from mail.vex.net (smaug.vex.net [208.76.104.132]) by mail.postgresql.org (Postfix) with ESMTP id AA03564FEE7 for ; Tue, 23 Dec 2008 13:45:20 -0400 (AST) Received: from dilbert (dilbert.druid.net [207.35.13.14]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.vex.net (Postfix) with ESMTPSA id 2597B17032; Tue, 23 Dec 2008 12:39:17 -0500 (EST) Date: Tue, 23 Dec 2008 12:39:17 -0500 From: "D'Arcy J.M. Cain" To: "Kynn Jones" Cc: pgsql-performance@postgresql.org Subject: Re: How to "unique-ify" HUGE table? Message-Id: <20081223123917.df2f8c0e.darcy@druid.net> In-Reply-To: References: X-Mailer: Sylpheed 2.5.0 (GTK+ 2.14.3; i386--netbsdelf) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/204 X-Sequence-Number: 32240 On Tue, 23 Dec 2008 12:25:48 -0500 "Kynn Jones" wrote: > 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 Do you have an index on x and y? Also, does this work better? CREATE TEMP TABLE huge_table AS SELECT x, y FROM ... GROUP BY x, y; What does ANALYZE EXPLAIN have to say? -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.