"Stephen Denne" <Stephen.Denne@datamail.co.nz> writes:
> So dropping the fk constraint and index results in successful query execution with constant memory usage. Does this
confirmthat 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
runningout of memory, cancelling very long running queries, reloading config (to turn autovacuum off), and continues to
workas 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