Skip site navigation (1) Skip section navigation (2)

Problem with SQL query (eats swap)

From: pgsql-sql(at)ruby(dot)sartorelli(dot)gen(dot)nz (Mailing List Expander)
To: pgsql-sql(at)hub(dot)org
Subject: Problem with SQL query (eats swap)
Date: 2000-08-11 02:46:21
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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 in (select from mail m where < 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?



pgsql-sql by date

Next:From: Tom LaneDate: 2000-08-11 14:10:30
Subject: Re: Rules aren't doing what I expect
Previous:From: Mark VolpeDate: 2000-08-10 14:22:47
Subject: Re: Rules aren't doing what I expect

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group