Re: Problem with large select - PostgreSQL starts eating memory/disk

On Thu, Aug 30, 2001 at 05:51:14PM -0700, Martin wrote:
> I am having a problem with an INSERT into a table from a SELECT which
> joins over 15 tables none of which have over 3K tuples. I run it
> multiple times with different values as part of my application and it
> randomly locks up when executed with different parameters. When I say
> lock up, I mean that it never finishes executing and that memory usage
> creeps up to the maximum (according to "top" - 1 GB from ~120 MB
> during normal operations) and it starts taking up the disk space for
> the partition with the data files - /var/lib/pgsql (goes from ~100 MB
> to ~12 GB over 45 minutes). It finally dies when there is no disk
> space left. If I drop the database and recreate, this goes back to 120
> MB.

Have you running VACUUM ANALYZE recently? When you do an EXPLAIN on the
query, how long does it think it will take?

--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> It would be nice if someone came up with a certification system that
> actually separated those who can barely regurgitate what they crammed over
> the last few weeks from those who command secret ninja networking powers.