Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 114CCB5DBEA for ; Fri, 24 Jun 2011 08:16:38 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 73112-09 for ; Fri, 24 Jun 2011 11:16:32 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from server515.appriver.com (server515k.exghost.com [72.32.253.100]) by mail.postgresql.org (Postfix) with ESMTP id E850CB5D803 for ; Fri, 24 Jun 2011 08:16:31 -0300 (ADT) X-Note-AR-ScanTimeLocal: 6/24/2011 6:14:54 AM X-Policy: GLOBAL - riskcontrollimited.com X-Policy: GLOBAL - riskcontrollimited.com X-Primary: harry.mantheakis@riskcontrollimited.com X-Note: This Email was scanned by AppRiver SecureTide X-ALLOW: @riskcontrollimited.com ALLOWED X-Virus-Scan: V- X-Note: Spam Tests Failed: X-Country-Path: PRIVATE->PRIVATE->UNITED STATES->UNITED STATES X-Note-Sending-IP: 72.32.49.6 X-Note-Reverse-DNS: X-Note-WHTLIST: harry.mantheakis@riskcontrollimited.com X-Note: User Rule Hits: X-Note: Global Rule Hits: G200 G201 G202 G203 G207 G208 G219 G309 X-Note: Encrypt Rule Hits: X-Note: Mail Class: ALLOWEDSENDER X-Note: Headers Injected Received: from [72.32.49.6] (HELO FE2.exchange.rackspace.com) by server515.appriver.com (CommuniGate Pro SMTP 5.4.1c) with ESMTP id 151390039; Fri, 24 Jun 2011 06:14:54 -0500 Received: from FE11.exchange.rackspace.com ([192.168.1.63]) by FE2.exchange.rackspace.com with Microsoft SMTPSVC(6.0.3790.4675); Fri, 24 Jun 2011 06:16:31 -0500 Received: from [192.168.20.134] ([87.194.70.108]) by FE11.exchange.rackspace.com with Microsoft SMTPSVC(6.0.3790.4675); Fri, 24 Jun 2011 06:16:30 -0500 Message-ID: <4E047210.4020400@riskcontrollimited.com> Date: Fri, 24 Jun 2011 12:16:32 +0100 From: Harry Mantheakis User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB; rv:1.9.2.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11 MIME-Version: 1.0 To: Kevin Grittner CC: pgsql-performance@postgresql.org Subject: Re: Long Running Update References: <4E035649.20007@riskcontrollimited.com> <4E034E78020000250003EB37@gw.wicourts.gov> In-Reply-To: <4E034E78020000250003EB37@gw.wicourts.gov> X-TagToolbar-Keys: D20110624121632458 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 24 Jun 2011 11:16:30.0603 (UTC) FILETIME=[2AF7BDB0:01CC3260] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201106/222 X-Sequence-Number: 44155 Thank you Kevin. > SET field_1 = table_B.field_1, field_2 = table_B.field_2 I will try that, if I have to next time. > add something like this toyour WHERE clause, > to prevent unnecessary updates: > > AND (table_B.field_1 IS DISTINCT FROM table_A.field_1 > OR table_B.field_2 IS DISTINCT FROM table_A.field_2); Thank you for that explanation - I will keep that in mind in future. (In this case, the two fields that are being updated are all known to be empty - hence, distinct - in the target table.) > EXPLAIN the statement Here is the EXPLAIN result: ---------------------------------------------------------------------- QUERY PLAN ---------------------------------------------------------------------- Hash Join (cost=2589312.08..16596998.47 rows=74558048 width=63) Hash Cond: (table_A.id = table_B.id) -> Seq Scan on table_A(cost=0.00..1941825.05 rows=95612705 width=47) -> Hash (cost=1220472.48..1220472.48 rows=74558048 width=20) -> Seq Scan on table_B(cost=0.00..1220472.48 rows=74558048 width=20) ---------------------------------------------------------------------- The documentation says the 'cost' numbers are 'units of disk page fetches'. Do you, by any chance, have any notion of how many disk page fetches can be processed per second in practice - at least a rough idea? IOW how do I convert - guesstimate! - these numbers into (plausible) time values? Kind regards Harry Mantheakis London, UK