pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problem with SQL query (eats swap)
2+ messages / 2 participants
[nested] [flat]

* Problem with SQL query (eats swap)
@ 2000-08-11 02:46  Mailing List Expander <pgsql-sql@ruby.sartorelli.gen.nz>
  0 siblings, 1 reply; 2+ messages in thread

From: pgsql-sql@ruby.sartorelli.gen.nz @ 2000-08-11 02:46 UTC (permalink / raw)
  To: pgsql-sql@hub.org

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Problem with SQL query (eats swap)
@ 2000-08-11 15:53  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Mailing List Expander <pgsql-sql@ruby.sartorelli.gen.nz>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2000-08-11 15:53 UTC (permalink / raw)
  To: pgsql-sql@ruby.sartorelli.gen.nz; +Cc: pgsql-sql@hub.org

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-08-11 15:53 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-11 02:46 Problem with SQL query (eats swap) Mailing List Expander <pgsql-sql@ruby.sartorelli.gen.nz>
2000-08-11 15:53 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox