Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e7BFseG26542 for ; Fri, 11 Aug 2000 11:54:40 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id LAA02541; Fri, 11 Aug 2000 11:53:56 -0400 (EDT) To: pgsql-sql@ruby.sartorelli.gen.nz (Mailing List Expander) cc: pgsql-sql@hub.org Subject: Re: Problem with SQL query (eats swap) In-reply-to: References: Comments: In-reply-to pgsql-sql@ruby.sartorelli.gen.nz (Mailing List Expander) message dated "Fri, 11 Aug 2000 14:46:21 +1200" Date: Fri, 11 Aug 2000 11:53:55 -0400 Message-ID: <2538.966009235@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200008/75 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