"Stephen Denne" <Stephen.Denne@xxxxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
