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 7C393D1E2A6 for ; Wed, 17 Mar 2004 00:20:46 +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 89825-08 for ; Tue, 16 Mar 2004 20:20:46 -0400 (AST) Received: from www.totalcardinc.com (unknown [64.33.232.186]) by svr1.postgresql.org (Postfix) with ESMTP id 7C52CD1E180 for ; Tue, 16 Mar 2004 20:20:40 -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 i2H0KdDx029283; Tue, 16 Mar 2004 18:20:39 -0600 From: "Rosser Schwarz" To: "'Tom Lane'" Cc: Subject: Re: atrocious update performance Date: Tue, 16 Mar 2004 18:20:39 -0600 Message-ID: <002301c40bb5$ad50db40$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 In-Reply-To: <294.1079476193@sss.pgh.pa.us> 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-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200403/227 X-Sequence-Number: 6083 while you weren't looking, Tom Lane wrote: [trace] `strace -p 21882` run behind the below query and plan ... below that. # explain update account.cust set prodid =3D tempprod.prodid, subprodid =3D tempprod.subprodid where origid =3D tempprod.debtid; QUERY PLAN ------------------------------------------------------------------------- Merge Join (cost=3D0.00..232764.69 rows=3D4731410 width=3D252) Merge Cond: (("outer".origid)::text =3D ("inner".debtid)::text) -> Index Scan using ix_origid on cust (cost=3D0.00..94876.83 rows=3D4731410 width=3D236) -> Index Scan using ix_debtid on tempprod (cost=3D0.00..66916.71 rows=3D4731410 width=3D26) (4 rows) ---------- recv(9, "Q\0\0\0}explain update account.cust"..., 8192, 0) =3D 126 gettimeofday({1079482151, 106228}, NULL) =3D 0 brk(0) =3D 0x82d9000 brk(0x82db000) =3D 0x82db000 open("/var/lib/pgsql/data/base/495616/6834170", O_RDWR|O_LARGEFILE) =3D 8 _llseek(8, 212402176, [212402176], SEEK_SET) =3D 0 write(8, "\342\1\0\0\0\314\374\6\24\0\0\0\214\7pG\360\177\1\200\320"..., 32768) =3D 32768 close(8) =3D 0 open("/var/lib/pgsql/data/base/495616/16635", O_RDWR|O_LARGEFILE) =3D 8 read(8, "\0\0\0\0\20\0\0\0\1\0\0\0\24\0\360\177\360\177\1\200b1"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834168", O_RDWR|O_LARGEFILE) =3D 10 _llseek(10, 60817408, [60817408], SEEK_SET) =3D 0 write(10, "\342\1\0\0`\334\5\7\24\0\0\0t\0010x\360\177\1\200\330\377"..., 32768) =3D 32768 close(10) =3D 0 read(8, "\334\1\0\0h\217\270n\24\0\0\0H\0H|\360\177\1\200@\376\220"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834165", O_RDWR|O_LARGEFILE) =3D 10 _llseek(10, 130777088, [130777088], SEEK_SET) =3D 0 write(10, "\342\1\0\0<\341\7\7\24\0\0\0004\t0I\360\177\1\200\330\377"..., 32768) =3D 32768 close(10) =3D 0 open("/var/lib/pgsql/data/base/495616/16595", O_RDWR|O_LARGEFILE) =3D 10 read(10, "\334\1\0\0\360\216\270n\24\0\0\0X\0@y\0\200\1\200\320\371"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834168", O_RDWR|O_LARGEFILE) =3D 11 _llseek(11, 145915904, [145915904], SEEK_SET) =3D 0 write(11, "\342\1\0\0\300\350\n\7\24\0\0\0\224\6\310Z\360\177\1\200"..., 32768) =3D 32768 close(11) =3D 0 open("/var/lib/pgsql/data/base/495616/16614", O_RDWR|O_LARGEFILE) =3D 11 read(11, "\0\0\0\0\24\231P\306\16\0\0\0\24\0\360\177\360\177\1\200"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834166", O_RDWR|O_LARGEFILE) =3D 12 _llseek(12, 148570112, [148570112], SEEK_SET) =3D 0 write(12, "\342\1\0\0\274\365\22\7\24\0\0\0X\3\234o\360\177\1\200"..., 32768) =3D 32768 close(12) =3D 0 _llseek(11, 98304, [98304], SEEK_SET) =3D 0 read(11, "\0\0\0\0\24\231P\306\16\0\0\0\34\0\234\177\360\177\1\200"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834163", O_RDWR|O_LARGEFILE) =3D 12 _llseek(12, 251789312, [251789312], SEEK_SET) =3D 0 write(12, "\342\1\0\0l\366\23\7\24\0\0\0\364\10\260J\360\177\1\200"..., 32768) =3D 32768 close(12) =3D 0 _llseek(11, 32768, [32768], SEEK_SET) =3D 0 read(11, "\340\1\0\0\324\231\273\241\24\0\0\0\234\5\330\26\360\177"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834165", O_RDWR|O_LARGEFILE) =3D 12 _llseek(12, 117309440, [117309440], SEEK_SET) =3D 0 write(12, "\342\1\0\0d\36)\7\24\0\0\0000\tHI\360\177\1\200\330\377"..., 32768) =3D 32768 close(12) =3D 0 open("/var/lib/pgsql/data/base/495616/1259", O_RDWR|O_LARGEFILE) =3D 12 _llseek(12, 32768, [32768], SEEK_SET) =3D 0 read(12, "\334\1\0\0\324v-p\24\0\0\0000\3\304\3\0\200\1\200<\377"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834173", O_RDWR|O_LARGEFILE) =3D 13 _llseek(13, 247824384, [247824384], SEEK_SET) =3D 0 write(13, "\342\1\0\0h *\7\24\0\0\0\204\4dm\360\177\1\200\340\377"..., 32768) =3D 32768 close(13) =3D 0 open("/var/lib/pgsql/data/base/495616/16613", O_RDWR|O_LARGEFILE) =3D 13 read(13, "\0\0\0\0\20\0\0\0\1\0\0\0\24\0\360\177\360\177\1\200b1"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834168", O_RDWR|O_LARGEFILE) =3D 14 _llseek(14, 204472320, [204472320], SEEK_SET) =3D 0 write(14, "\342\1\0\0\314\272:\7\24\0\0\0\324\t\354K\360\177\1\200"..., 32768) =3D 32768 close(14) =3D 0 read(13, "\340\1\0\0X\231\273\241\24\0\0\0\370\6Dk\360\177\1\200"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834166", O_RDWR|O_LARGEFILE) =3D 14 _llseek(14, 152010752, [152010752], SEEK_SET) =3D 0 write(14, "\342\1\0\0p\277<\7\24\0\0\0\364\n\220I\360\177\1\200\334"..., 32768) =3D 32768 close(14) =3D 0 open("/var/lib/pgsql/data/base/495616/16610", O_RDWR|O_LARGEFILE) =3D 14 read(14, "\0\0\0\0\10\317\27\t\16\0\0\0\24\0\360\177\360\177\1\200"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834170", O_RDWR|O_LARGEFILE) =3D 15 _llseek(15, 86441984, [86441984], SEEK_SET) =3D 0 write(15, "\342\1\0\0\330B?\7\24\0\0\0\370\6 N\360\177\1\200\310\377"..., 32768) =3D 32768 close(15) =3D 0 _llseek(14, 98304, [98304], SEEK_SET) =3D 0 read(14, "\340\1\0\0,l\257\241\24\0\0\0(\0\250\177\360\177\1\200"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834166", O_RDWR|O_LARGEFILE) =3D 15 _llseek(15, 121896960, [121896960], SEEK_SET) =3D 0 write(15, "\342\1\0\0\264\303?\7\24\0\0\0\234\tHP\360\177\1\200\334"..., 32768) =3D 32768 close(15) =3D 0 _llseek(14, 65536, [65536], SEEK_SET) =3D 0 read(14, "\334\1\0\0\310u\252n\23\0\0\0\234\20\320=3D\360\177\1\200"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834173", O_RDWR|O_LARGEFILE) =3D 15 _llseek(15, 41549824, [41549824], SEEK_SET) =3D 0 write(15, "\342\1\0\0\0\312B\7\24\0\0\0\234\7\350T\360\177\1\200\330"..., 32768) =3D 32768 close(15) =3D 0 open("/var/lib/pgsql/data/base/495616/1249", O_RDWR|O_LARGEFILE) =3D 15 _llseek(15, 229376, [229376], SEEK_SET) =3D 0 read(15, "O\1\0\0\214\241\200\0\23\0\0\0\364\3\0\4\0\200\1\200\200"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834163", O_RDWR|O_LARGEFILE) =3D 16 _llseek(16, 57147392, [57147392], SEEK_SET) =3D 0 write(16, "\342\1\0\0004\320G\7\24\0\0\0\374\7\200P\360\177\1\200"..., 32768) =3D 32768 close(16) =3D 0 _llseek(15, 163840, [163840], SEEK_SET) =3D 0 read(15, "\21\1\0\0\214\3\224R\23\0\0\0\364\3\0\4\0\200\1\200\200"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834163", O_RDWR|O_LARGEFILE) =3D 16 _llseek(16, 241893376, [241893376], SEEK_SET) =3D 0 write(16, "\342\1\0\0\220TK\7\24\0\0\0,\t`I\360\177\1\200\330\377"..., 32768) =3D 32768 close(16) =3D 0 _llseek(12, 0, [0], SEEK_SET) =3D 0 read(12, "O\1\0\0\350\340\316,\23\0\0\0X\3\230\3\0\200\1\200d\304"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834171", O_RDWR|O_LARGEFILE) =3D 16 _llseek(16, 88702976, [88702976], SEEK_SET) =3D 0 write(16, "\342\1\0\0\324\326K\7\24\0\0\0`\v\370E\360\177\1\200\334"..., 32768) =3D 32768 close(16) =3D 0 _llseek(14, 32768, [32768], SEEK_SET) =3D 0 read(14, "\0\0\0\0\10\317\27\t\16\0\0\0\234\20\320=3D\360\177\1\200"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834173", O_RDWR|O_LARGEFILE) =3D 16 _llseek(16, 152043520, [152043520], SEEK_SET) =3D 0 write(16, "\342\1\0\0\220fU\7\24\0\0\0l\n\320K\360\177\1\200\334\377"..., 32768) =3D 32768 close(16) =3D 0 _llseek(15, 0, [0], SEEK_SET) =3D 0 read(15, "\0\0\0\0\20\0\0\0\1\0\0\0\364\3\0\4\0\200\1\200\200\377"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834163", O_RDWR|O_LARGEFILE) =3D 16 _llseek(16, 70025216, [70025216], SEEK_SET) =3D 0 write(16, "\342\1\0\0\370\rk\7\24\0\0\0 \10\250O\360\177\1\200\330"..., 32768) =3D 32768 close(16) =3D 0 read(15, "\0\0\0\0\20\0\0\0\1\0\0\0\364\3\0\4\0\200\1\200\200\377"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834163", O_RDWR|O_LARGEFILE) =3D 16 _llseek(16, 152764416, [152764416], SEEK_SET) =3D 0 write(16, "\342\1\0\0008\222m\7\24\0\0\0\370\10\230J\360\177\1\200"..., 32768) =3D 32768 close(16) =3D 0 open("/var/lib/pgsql/data/base/495616/16630", O_RDWR|O_LARGEFILE) =3D 16 read(16, "\0\0\0\0\20\0\0\0\1\0\0\0\24\0\360\177\360\177\1\200b1"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834163", O_RDWR|O_LARGEFILE) =3D 17 _llseek(17, 143753216, [143753216], SEEK_SET) =3D 0 write(17, "\342\1\0\0\314!w\7\24\0\0\0\20\t\10J\360\177\1\200\330"..., 32768) =3D 32768 close(17) =3D 0 read(16, "\340\1\0\0\340\204\264\241\24\0\0\0H\2Ty\360\177\1\200"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834170", O_RDWR|O_LARGEFILE) =3D 17 _llseek(17, 192512000, [192512000], SEEK_SET) =3D 0 write(17, "\342\1\0\0`\253y\7\24\0\0\0\250\7\330G\360\177\1\200\324"..., 32768) =3D 32768 close(17) =3D 0 open("/var/lib/pgsql/data/base/495616/16390", O_RDWR|O_LARGEFILE) =3D 17 read(17, "\334\1\0\0t\242\23p\24\0\0\0\0\2\210\2\0\200\1\200\24\377"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/16396", O_RDWR|O_LARGEFILE) =3D 18 _llseek(18, 0, [32768], SEEK_END) =3D 0 open("/var/lib/pgsql/data/base/495616/6834168", O_RDWR|O_LARGEFILE) =3D 19 _llseek(19, 63471616, [63471616], SEEK_SET) =3D 0 write(19, "\342\1\0\0\2444\200\7\24\0\0\0$\10\240O\360\177\1\200\330"..., 32768) =3D 32768 close(19) =3D 0 _llseek(18, 0, [0], SEEK_SET) =3D 0 read(18, "\0\0\0\0\20\0\0\0\1\0\0\0$\0\240}\0\200\1\200h\3770\1\320"..., 32768) =3D 32768 brk(0) =3D 0x82db000 brk(0x82dd000) =3D 0x82dd000 open("/var/lib/pgsql/data/base/495616/6834166", O_RDWR|O_LARGEFILE) =3D 19 _llseek(19, 64290816, [64290816], SEEK_SET) =3D 0 write(19, "\342\1\0\0<\265\200\7\24\0\0\0d\t`Q\360\177\1\200\334\377"..., 32768) =3D 32768 close(19) =3D 0 open("/var/lib/pgsql/data/base/495616/16605", O_RDWR|O_LARGEFILE) =3D 19 read(19, "\0\0\0\0\20\0\0\0\1\0\0\0\24\0\360\177\360\177\1\200b1"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834163", O_RDWR|O_LARGEFILE) =3D 20 _llseek(20, 152731648, [152731648], SEEK_SET) =3D 0 write(20, "\342\1\0\0\264=3D\206\7\24\0\0\0\370\10\230J\360\177\1\200"..., 32768) =3D 32768 close(20) =3D 0 read(19, "\0\0\0\0\20\0\0\0\1\0\0\0\264\3pq\360\177\1\200\300\363"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834170", O_RDWR|O_LARGEFILE) =3D 20 _llseek(20, 150274048, [150274048], SEEK_SET) =3D 0 write(20, "\342\1\0\0\230\310\212\7\24\0\0\0\210\7lO\360\177\1\200"..., 32768) =3D 32768 close(20) =3D 0 open("/var/lib/pgsql/data/base/495616/16398", O_RDWR|O_LARGEFILE) =3D 20 read(20, "\0\0\0\0\20\0\0\0\1\0\0\0\264\3`_\0\200\1\200\334\377H"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834166", O_RDWR|O_LARGEFILE) =3D 21 _llseek(21, 260046848, [260046848], SEEK_SET) =3D 0 write(21, "\342\1\0\0\4\322\220\7\24\0\0\0\264\2\320r\360\177\1\200"..., 32768) =3D 32768 close(21) =3D 0 open("/var/lib/pgsql/data/base/495616/16639", O_RDWR|O_LARGEFILE) =3D 21 read(21, "\0\0\0\0\20\0\0\0\1\0\0\0\24\0\360\177\360\177\1\200b1"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834170", O_RDWR|O_LARGEFILE) =3D 22 _llseek(22, 174424064, [174424064], SEEK_SET) =3D 0 write(22, "\342\1\0\0\200\\\225\7\24\0\0\0D\t$H\360\177\1\200\330"..., 32768) =3D 32768 close(22) =3D 0 read(21, "\0\0\0\0\20\0\0\0\1\0\0\0\200\t\254c\360\177\1\200\344"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834163", O_RDWR|O_LARGEFILE) =3D 22 _llseek(22, 109084672, [109084672], SEEK_SET) =3D 0 write(22, "\342\1\0\0\310\335\226\7\24\0\0\0 \10\250O\360\177\1\200"..., 32768) =3D 32768 close(22) =3D 0 open("/var/lib/pgsql/data/base/495616/16392", O_RDWR|O_LARGEFILE) =3D 22 read(22, "\0\0\0\0\20\0\0\0\1\0\0\0X\3\350\3\0\200\1\200h\3770\1"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834170", O_RDWR|O_LARGEFILE) =3D 23 _llseek(23, 200900608, [200900608], SEEK_SET) =3D 0 write(23, "\342\1\0\0\314\344\232\7\24\0\0\0\344\7\304G\360\177\1"..., 32768) =3D 32768 close(23) =3D 0 open("/var/lib/pgsql/data/base/495616/16606", O_RDWR|O_LARGEFILE) =3D 23 read(23, "\0\0\0\0\20\0\0\0\1\0\0\0\24\0\360\177\360\177\1\200b1"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834168", O_RDWR|O_LARGEFILE) =3D 24 _llseek(24, 85426176, [85426176], SEEK_SET) =3D 0 write(24, "\342\1\0\0\30\345\232\7\24\0\0\0\264\7\360V\360\177\1\200"..., 32768) =3D 32768 close(24) =3D 0 read(23, "\0\0\0\0\20\0\0\0\1\0\0\0H\1 {\360\177\1\200P\377 \0@\377"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834166", O_RDWR|O_LARGEFILE) =3D 24 _llseek(24, 156729344, [156729344], SEEK_SET) =3D 0 write(24, "\342\1\0\0\260e\233\7\24\0\0\0\30\n\334M\360\177\1\200"..., 32768) =3D 32768 close(24) =3D 0 open("/var/lib/pgsql/data/base/495616/16400", O_RDWR|O_LARGEFILE) =3D 24 read(24, "\0\0\0\0\20\0\0\0\1\0\0\0H\1,u\0\200\1\200\334\377H\0\270"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834168", O_RDWR|O_LARGEFILE) =3D 25 _llseek(25, 92995584, [92995584], SEEK_SET) =3D 0 write(25, "\342\1\0\0\244i\235\7\24\0\0\0\360\ttO\360\177\1\200\324"..., 32768) =3D 32768 close(25) =3D 0 open("/var/lib/pgsql/data/base/495616/16607", O_RDWR|O_LARGEFILE) =3D 25 read(25, "\0\0\0\0\264\341\v\t\16\0\0\0\24\0\360\177\360\177\1\200"..., 32768) =3D 32768 open("/var/lib/pgsql/data/base/495616/6834170", O_RDWR|O_LARGEFILE) =3D 26 _llseek(26, 209387520, [209387520], SEEK_SET) =3D 0 write(26, "\342\1\0\0