X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4B9F1D1BACC for ; Tue, 16 Mar 2004 00:08:26 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 47697-04 for ; Mon, 15 Mar 2004 20:08:21 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 0181AD1E2AA for ; Mon, 15 Mar 2004 20:08:17 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i2G08Ko0008913; Mon, 15 Mar 2004 19:08:20 -0500 (EST) To: "Rosser Schwarz" Cc: pgsql-performance@postgresql.org Subject: Re: atrocious update performance In-reply-to: <001a01c40ae4$1cdd4160$2500fa0a@CardServices.TCI.com> References: <001a01c40ae4$1cdd4160$2500fa0a@CardServices.TCI.com> Comments: In-reply-to "Rosser Schwarz" message dated "Mon, 15 Mar 2004 17:20:32 -0600" Date: Mon, 15 Mar 2004 19:08:19 -0500 Message-ID: <8912.1079395699@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/210 X-Sequence-Number: 6066 "Rosser Schwarz" writes: >> You can create a new table using 'create table as' to produce your >> target results. This is real fast ... >> I often see 2 orders of magnitude improvement doing this, and no >> need to vacuum. > Indeed: > "Query returned successfully with no result in 582761 ms." > Though I must say, ten minutes is nominally more than two orders of > mangitude performance improvement, versus several days. Hm. There is no way that inserting a row is two orders of magnitude faster than updating a row --- they both require storing a new row and making whatever index entries are needed. The only additional cost of the update is finding the old row (not a very big deal AFAICS in the examples you gave) and marking it deleted (definitely cheap). So there's something awfully fishy going on here. I'm inclined to suspect an issue with foreign-key checking. You didn't give us any details about foreign key relationships your "cust" table is involved in --- could we see those? And the schemas of the other tables involved? Also, exactly which PG version is this? regards, tom lane