Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 513E56336BF for ; Fri, 8 Jan 2010 02:14:31 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 34500-06 for ; Fri, 8 Jan 2010 06:14:19 +0000 (UTC) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id CA4BA633591 for ; Fri, 8 Jan 2010 02:14:20 -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 o086EJ5i044631 for ; Fri, 8 Jan 2010 02:14:19 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.3/8.14.3/Submit) id o0861hDk040696 for pgsql-performance@postgresql.org; Fri, 8 Jan 2010 02:01:43 -0400 (AST) (envelope-from news) From: "Carlo Stonebanks" X-Newsgroups: pgsql.performance Subject: Re: Massive table (500M rows) update nightmare Date: Fri, 8 Jan 2010 01:02:25 -0500 Organization: Hub.Org Networking Services Lines: 42 Message-ID: References: <4B462563020000250002DFA3@gw.wicourts.gov> 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: <4B462563020000250002DFA3@gw.wicourts.gov> 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=0.002 tagged_above=-10 required=5 tests=BAYES_50=0.001, STOX_REPLY_TYPE=0.001 X-Spam-Level: X-Archive-Number: 201001/138 X-Sequence-Number: 37018 Already done in an earlier post, Kevin - I have included it again below. As you can see, it's pretty well wqhat you would expect, index scan plus a filter. One note: updates where no rows qualify run appreciably faster than the ones that do. That is, the update itself appears to be consuming a good deal of the processing time. This may be due to the 6 indexes. 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 ""Kevin Grittner"" wrote in message news:4B462563020000250002DFA3@gw.wicourts.gov... > "Carlo Stonebanks" wrote: > >> An interesting idea, if I can confirm that the performance problem >> is because of the WHERE clause, not the UPDATE. > > If you could show EXPLAIN ANALYZE output for one iteration, with > related queries and maybe more info on the environment, it would > take most of the guesswork out of things. > > -Kevin > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >