pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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