Received: from ruby.sartorelli.gen.nz (topnz129.topnz.ac.nz [202.37.11.129]) by hub.org (8.10.1/8.10.1) with ESMTP id e7B2kVG34237 for ; Thu, 10 Aug 2000 22:46:32 -0400 (EDT) Received: by ruby.sartorelli.gen.nz :(Smail-3.2.0.111 2000-Feb-17 #1) id m13N4px-003bmOC; Fri, 11 Aug 2000 14:46:21 +1200 (NZST) Message-Id: Date: Fri, 11 Aug 2000 14:46:21 +1200 (NZST) From: pgsql-sql@ruby.sartorelli.gen.nz (Mailing List Expander) To: pgsql-sql@hub.org Subject: Problem with SQL query (eats swap) X-Archive-Number: 200008/73 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. Linking the 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 128Mb RAM, 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 much swap? Cheers Kevin kevin@sartorelli.gen.nz