X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 16C459DC942 for ; Sun, 8 Jan 2006 14:21:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 58980-04 for ; Sun, 8 Jan 2006 14:21:28 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id EE9359DC97C for ; Sun, 8 Jan 2006 14:21:26 -0400 (AST) Received: from r1.mycybernet.net (r1.mycybernet.net [209.5.206.8]) by svr4.postgresql.org (Postfix) with ESMTP id B25165AF064 for ; Sun, 8 Jan 2006 18:21:27 +0000 (GMT) Received: from 227-54-222-209.mycybernet.net ([209.222.54.227]:57508 helo=phlogiston.dydns.org) by r1.mycybernet.net with esmtp (Exim 4.50 (FreeBSD)) id 1EvfAM-0008yW-Id for pgsql-sql@postgresql.org; Sun, 08 Jan 2006 13:21:18 -0500 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id 4B696406B; Sun, 8 Jan 2006 13:21:14 -0500 (EST) Date: Sun, 8 Jan 2006 13:21:14 -0500 From: Andrew Sullivan To: pgsql-sql@postgresql.org Subject: Re: INSERT waiting under heavy load Message-ID: <20060108182114.GA31582@phlogiston.dyndns.org> References: <20060106184227.GA1890@s2.yuriev.com> <22727.1136580728@sss.pgh.pa.us> <20060106201657.GB1890@s2.yuriev.com> <23072.1136584149@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <23072.1136584149@sss.pgh.pa.us> User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.065 required=5 tests=[AWL=0.065] X-Spam-Score: 0.065 X-Spam-Level: X-Archive-Number: 200601/33 X-Sequence-Number: 23822 On Fri, Jan 06, 2006 at 04:49:09PM -0500, Tom Lane wrote: > performance risk in the sort of scenario you are describing. > (I'm not sure why it would manifest as transactions showing "INSERT > waiting" state though.) It's because of I/O. When you have a large number of updates, the planner always assumes an indexscan (correctly), but you end up scanning megabytes of dead tuples. With a large number of open transactions, most of the time VACUUM can't recover the space. I agree with what Tom said earlier in this thread: the design is guaranteed to lose. A -- Andrew Sullivan | ajs@crankycanuck.ca "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler