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 57E9CD1E30D for ; Mon, 15 Mar 2004 20:28:56 +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 74312-10 for ; Mon, 15 Mar 2004 16:28:55 -0400 (AST) Received: from www.totalcardinc.com (unknown [64.33.232.186]) by svr1.postgresql.org (Postfix) with ESMTP id A6F58D1E213 for ; Mon, 15 Mar 2004 16:28:50 -0400 (AST) Received: from RSCHWARZW2K ([10.250.0.37]) (authenticated bits=0) by www.totalcardinc.com (8.12.8/8.12.8) with ESMTP id i2FKSkCj016672 for ; Mon, 15 Mar 2004 14:28:47 -0600 From: "Rosser Schwarz" To: Subject: atrocious update performance Date: Mon, 15 Mar 2004 14:28:46 -0600 Message-ID: <001401c40acc$1e4e7180$2500fa0a@CardServices.TCI.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4510 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-MailScanner-From: rschwarz@totalcardinc.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/198 X-Sequence-Number: 6054 We're in the throes of an MS SQL to PostgreSQL migration; our databases include a number of ~5M row tables. We decided to take this opportunity to clean up and slightly re-normalize our schemas, given what we've learned about the data over its lifetime and such, else we wouldn't be experiencing any of the following (we could instead just dump and `copy from`). We have a temporary table, public.tempprod, containing 4.7M rows, one for each row in account.cust. account.cust has, among others, two columns, prod and subprod, which we're trying to update from tempprod joined against prod. The update tends to take unnecessarily long-- rather, we've had to finally kill it after its taking obscenely too long. The table: # \d account.cust Table "account.cust" Column | Type | Modifiers=20=20=20= =20=20=20=20 -----------+-----------------------------+---------------------------------- ---- custid | bigint | not null default | | nextval('account.custid_seq'::text) ownerid | integer | not null origid | text | not null pname | text | fname | text | mname | text | lname | text | suffix | text | addr1 | text | addr2 | text | addr3 | text | city | text | state | text | zip | text | zipplus | text | homeph | text | workph | text | otherph | text | ssn | text | isactive | boolean | default true createddt | timestamp without time zone | default now() prodid | bigint | subprodid | bigint | Indexes: "cust_pkey" primary key, btree (custid) "ix_addr1" btree (addr1) WHERE (addr1 IS NOT NULL) "ix_addr2" btree (addr2) WHERE (addr2 IS NOT NULL) "ix_city" btree (city) WHERE (city IS NOT NULL) "ix_fname" btree (fname) WHERE (fname IS NOT NULL) "ix_homeph" btree (homeph) WHERE (homeph IS NOT NULL) "ix_lname" btree (lname) WHERE (lname IS NOT NULL) "ix_mname" btree (mname) WHERE (mname IS NOT NULL) "ix_origid" btree (origid) "ix_ssn" btree (ssn) WHERE (ssn IS NOT NULL) "ix_state" btree (state) WHERE (state IS NOT NULL) "ix_workph" btree (workph) WHERE (workph IS NOT NULL) "ix_zip" btree (zip) WHERE (zip IS NOT NULL) We're currently running on a dual Xeon 700 (I know, I know; it's what we've got) with 2.5GB RAM and 4x36GB SCSI in hardware RAID 5 (Dell Perc3 something-or-other controller). If we can demonstrate that=20 PostgreSQL will meet our needs, we'll be going production on a dual Opteron, maxed memory, with a 12-disk Fibre Channel array. The query is: update account.cust set prodid =3D=20 (select p.prodid from account.prod p join public.tempprod t on t.pool =3D p.origid where custid =3D t.did) And then, upon its completion, s/prod/subprod/. That shouldn't run overnight, should it, let alone for -days-? In experimenting with ways of making the updates take less time, we tried adding product and subproduct columns to tempprod, and updating those. That seemed to work marginally better: explain analyze update public.tempprod set prodid =3D=20 (select account.prod.prodid::bigint=20 from account.prod=20 where public.tempprod.pool::text =3D account.prod.origid::text) Seq Scan on tempprod (cost=3D0.00..9637101.35 rows 4731410 width=3D56) (act= ual time=3D24273.467..16090470.438 rows=3D4731410 loops=3D1) SubPlan -> Limit (cost=3D0.00..2.02 rows=3D2 width=3D8) (actual time=3D0.134..= 0.315 rows=3D1 loops=3D4731410) -> Seq Scan on prod (cost=3D0.00..2.02 rows=3D2 width=3D8) (actu= al time=3D0.126..0.305 rows=3D1 loops=3D4731410) Filter: (($0)::text =3D (origid)::text) Total runtime: 2284551.962 ms But then going from public.tempprod to account.cust again takes days. I just cancelled an update that's been running since last Thursday. Alas, given how long the queries take to run, I can't supply an `explain analyze`. The `explain` seems reasonable enough: # explain update account.cust set prodid =3D tempprod.prodid where tempprod.did =3D origid; Merge Join (cost=3D0.00..232764.69 rows=3D4731410 width=3D252) Merge Cond: (("outer".origid)::text =3D ("inner".did)::text) -> Index Scan using ix_origid on cust (cost=3D0.00..94876.83 rows=3D4731410 width=3D244) -> Index Scan using ix_did on tempprod (cost=3D0.00..66916.71 rows=3D4731410 width=3D18) The relevant bits from my postgreql.conf (note, we built with a BLCKSZ of 16K): shared_buffers =3D 4096 sort_mem =3D 32768 vacuum_mem =3D 32768 wal_buffers =3D 16384 checkpoint_segments =3D 64 checkpoint_timeout =3D 1800 checkpoint_warning =3D 30 commit_delay =3D 50000 effective_cache_size =3D 131072 Any advice, suggestions or comments of the "You bleeding idiot, why do you have frob set to x?!" sort welcome. Unfortunately, if we can't improve this, significantly, the powers what be will probably pass on PostgreSQL, even though everything we've done so far--with this marked exception--performs pretty spectacularly, all told. /rls -- Rosser Schwarz Total Card, Inc.