Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id AF8DF2E2D97 for ; Mon, 28 Jan 2008 00:16:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 44668-05 for ; Mon, 28 Jan 2008 00:16:26 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 46E492E2D95 for ; Mon, 28 Jan 2008 00:16:25 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m0S4GKrW017437; Sun, 27 Jan 2008 23:16:21 -0500 (EST) To: "Stephen Denne" cc: pgsql-performance@postgresql.org Subject: Re: 8.3rc1 Out of memory when performing update In-reply-to: References: <21675.1201228274@sss.pgh.pa.us> <22975.1201236625@sss.pgh.pa.us> Comments: In-reply-to "Stephen Denne" message dated "Mon, 28 Jan 2008 10:55:10 +1300" Date: Sun, 27 Jan 2008 23:16:20 -0500 Message-ID: <17436.1201493780@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/254 X-Sequence-Number: 28493 "Stephen Denne" writes: > So dropping the fk constraint and index results in successful query execution with constant memory usage. Does this confirm that the memory leak you found is the one I was suffering from? Well, it confirms that you were suffering from that memory leak. What's not clear is whether the leak completely explains the bad performance you saw. The leak would have resulted in extra swapping, but I wouldn't have thought it would drive the machine completely into swap hell. Would the monitoring tools you were using have shown swapping I/O? If you want to try a focused experiment, please apply the patch shown here: http://archives.postgresql.org/pgsql-committers/2008-01/msg00322.php and see if the behavior gets better. > I'd also class the extremely poor performance of the alternate query as a bug. Yeah, we should look into that. The plan you showed before estimated about 16.9M rows in "document" --- is that about right? What have you got work_mem set to? > Aside: I must say that I am impressed with PostgreSQL's handling of this connection. It recovers extremely well from running out of memory, cancelling very long running queries, reloading config (to turn autovacuum off), and continues to work as expected (the 3 day old connection that is). Hey, we've had plenty of opportunity to improve the system's robustness in the face of such things ;-) regards, tom lane