Re: Problem with SQL query (eats swap)

pgsql-sql(at)ruby(dot)sartorelli(dot)gen(dot)nz (Mailing List Expander) writes:
> select count(*) from attachments a where a.id in (select m.id from
> mail m where m.date < now()-62);

> but ran out of swap.

The problem is that "now()-62" leaks memory to the tune of a few dozen
bytes per evaluation. In existing releases that memory won't be
reclaimed till end of query. (This problem is fixed for 7.1, but that
won't help you today.) Since the inner select is re-executed for each
iteration of the outer select, you have a lot of executions of the
inner WHERE clause, and so even a small leak is a problem.

regards, tom lane