Thread: Problem with SQL query (eats swap)

Hi, I have two tables into which I dump mail statistics.  The two tables are:

create table attachments ( id text, attachment text
);
create table mail ( id text, size int, whofrom text, subject text, date datetime, inout char
);

The table mail holds information about each mail message.  The table attachments holds the name of any attachments.
Linkingthe attachment(s) and the message is the id.
 

I want to delete all records relating to mail that is over two months old.  I tried:

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 mail table is 19Mb, the attachment one 1Mb.  I was up to 380Mb of swap used on a machine with
128MbRAM, and over 15 minutes run time.  At that point I killed the query.
 

I next tried is as follows:

drop table temp;
select id into temp from mail where date < now()-62;
delete from attachments where id in (select id from temp);
delete from mail where date < now()-62;

This worked fine and deleted the records as intended in a few minutes.  Can anyone see why the initial query ate so
muchswap?
 


Cheers
Kevin
kevin@sartorelli.gen.nz

pgsql-sql@ruby.sartorelli.gen.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