Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 61639632C85 for ; Thu, 7 Jan 2010 14:14:25 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 42772-09-2 for ; Thu, 7 Jan 2010 18:14:14 +0000 (UTC) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id AF5F8633B6B for ; Thu, 7 Jan 2010 14:14:14 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.3/8.14.3) with ESMTP id o07IEDtE062323 for ; Thu, 7 Jan 2010 14:14:13 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.3/8.14.3/Submit) id o07HnE27051745 for pgsql-performance@postgresql.org; Thu, 7 Jan 2010 13:49:14 -0400 (AST) (envelope-from news) From: "Carlo Stonebanks" X-Newsgroups: pgsql.performance Subject: Re: Massive table (500M rows) update nightmare Date: Thu, 7 Jan 2010 12:49:54 -0500 Organization: Hub.Org Networking Services Lines: 20 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org In-Reply-To: X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Windows Mail 6.0.6002.18005 X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.19 tagged_above=-10 required=5 tests=BAYES_50=0.001, FH_DATE_PAST_20XX=3.188, STOX_REPLY_TYPE=0.001 X-Spam-Level: *** X-Archive-Number: 201001/115 X-Sequence-Number: 36995 > Got an explain analyze of the delete query? UPDATE mdx_core.audit_impt SET source_table = 'mdx_import.'||impt_name WHERE audit_impt_id >= 319400001 AND audit_impt_id <= 319400010 AND coalesce(source_table, '') = '' Index Scan using audit_impt_pkey on audit_impt (cost=0.00..92.63 rows=1 width=608) (actual time=0.081..0.244 rows=10 loops=1) Index Cond: ((audit_impt_id >= 319400001) AND (audit_impt_id <= 319400010)) Filter: ((COALESCE(source_table, ''::character varying))::text = ''::text) Total runtime: 372.141 ms Hard to tell how reliable these numbers are, because the caches are likely spun up for the WHERE clause - in particular, SELECT queries have been run to test whether the rows actually qualify for the update. The coalesce may be slowing things down slightly, but is a necessary evil.