Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E6A4B6501C8 for ; Tue, 23 Dec 2008 13:34:31 -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 21979-01 for ; Tue, 23 Dec 2008 13:34:29 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.181]) by mail.postgresql.org (Postfix) with ESMTP id 4224C6501D0 for ; Tue, 23 Dec 2008 13:34:29 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id j4so1570792wah.19 for ; Tue, 23 Dec 2008 09:34:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=M39t6qy3yRmNaUkgWn6tqOujHYIDOv03WwVVnHeUJDs=; b=UgMvqnZvXwB52ih1NhQqN6XAvEGPW7CPxkXOi6e8ZmuTqCuKIcw4CWK3b+2uHeMVJI XKoQDSJUyprz46CXMkj7bJQH/IMP0Uc9oanSN4ZOcrQxIgUWAIDOyzk6m1b6FBGMjcjM FWEQDj+OzCM6IG3ITZ4OJpNHISbx67gOtwyUI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=a8qf9fbXkgpylc+C9eAGwBpe6Fr0oAEXxHU1o9p4j9sKhm+I/5NjHJBTDs5jNIC6Pn 4mL3bKL9BpuOH4x4pcpw85Y1VC0AA0Fh6oUmwYBa0QNYsOJXtkmXCL14esdl4D7DrUfY HBIHadbX+hueq4Px1ZdkrVtWfyrTap4A9V89I= Received: by 10.115.19.16 with SMTP id w16mr4933799wai.51.1230053668291; Tue, 23 Dec 2008 09:34:28 -0800 (PST) Received: by 10.114.132.17 with HTTP; Tue, 23 Dec 2008 09:34:28 -0800 (PST) Message-ID: Date: Tue, 23 Dec 2008 10:34:28 -0700 From: "Scott Marlowe" To: "Kynn Jones" Subject: Re: How to "unique-ify" HUGE table? Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: 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/203 X-Sequence-Number: 32239 On Tue, Dec 23, 2008 at 10:25 AM, 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 > 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. Did you try cranking up work_mem to something that's a large percentage (25 to 50%) of total memory?