Received: from castle-smtp (firewall-user@ns2.amgen.com [138.133.17.7]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA87808 for ; Thu, 2 Dec 1999 14:30:23 -0500 (EST) (envelope-from mdalphin@amgen.com) Received: by castle-smtp; id LAA16053; Thu, 2 Dec 1999 11:30:54 -0800 (PST) Received: from mailbag.amgen.com(138.133.10.78) by castle-smtp.amgen.com via smap (V4.2) id xma015483; Thu, 2 Dec 99 11:29:20 -0800 Received: from maat.amgen.com (maat [138.133.145.25]) by mailbag.amgen.com (8.8.5/8.8.5) with ESMTP id LAA20280 for ; Thu, 2 Dec 1999 11:28:45 -0800 (PST) Received: from amgen.com (mahunui [138.133.147.23]) by maat.amgen.com (8.8.8+Sun/8.8.8) with ESMTP id LAA00882 for ; Thu, 2 Dec 1999 11:28:44 -0800 (PST) Sender: mdalphin@amgen.com Message-ID: <3846C8D3.DB066E78@amgen.com> Date: Thu, 02 Dec 1999 11:30:27 -0800 From: Mark Dalphin Organization: Amgen, Inc. X-Mailer: Mozilla 4.6C-SGI [en] (X11; U; IRIX64 6.5 IP30) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: How to avoid "Out of memory" using aggregate functions? Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi, I am working with a simple table similar to the following: CREATE TABLE Seq ( SeqID int8 PRIMARY KEY, FileID int2 NOT NULL, FileOffset int8 NOT NULL, SeqLength int4, FOREIGN KEY (FileID) REFERENCES File (FileID) ); CREATE INDEX Seq_SeqLength ON Seq (SeqLength); The table contains facts about a DNA sequence (eg its length) and how to access that sequence from an ordinary Unix file (FileID and FileOffset) where it is concatonated with all the other DNA sequences. Currently the TABLE Seq contains 4,586,379 (times 2) rows and the overall size of the Unix file is 3,660,117,293 bytes (ie I expect sum(SeqLength) to be in the region of 3.6 Gbytes). I want to use the PostgreSQL aggregate functions to learn more about the sequences. The following query works well and quickly (I need to cast as int8 to avoid overflow of the sum() and perhaps for avg()): SELECT count(seqlength::int8), min(seqlength::int8), max(seqlength::int8), avg(seqlength::int8), sum(seqlength::int8) FROM Seq WHERE FileID=4 AND SeqLength>1000; An EXPLAIN of the above query returns the following: NOTICE: QUERY PLAN: Aggregate (cost=202000.62 rows=918779 width=4) -> Index Scan using seq_seqlength on seq (cost=202000.62 rows=918779 width=4) EXPLAIN If I remove the SeqLength constraint, the query takes forever to execute and then crashes with an out-of-memory (on my SGI, I have 256 Mbyte RAM and equal sized swap space; I run out of swap space during the query which makes a very unpleasent noise). EXPLAIN SELECT count(seqlength::int8), min(seqlength::int8), max(seqlength::int8), avg(seqlength::int8), sum(seqlength::int8) FROM Seq WHERE FileID=4; NOTICE: QUERY PLAN: Aggregate (cost=397916.69 rows=2756337 width=4) -> Seq Scan on seq (cost=397916.69 rows=2756337 width=4) EXPLAIN I do not understand how to interpret this Query Plan, except that I am doing a sequential scan of the DB (hence slow), but, since all the elements requested are aggregates, why do I run out of memory? I am not trying to load the whole table into memory at once, am I? Suggestions for how to make this query run would be welcome; my database is rapidly growing larger! Thanks, Mark -- Mark Dalphin email: mdalphin@amgen.com Mail Stop: 29-2-A phone: +1-805-447-4951 (work) One Amgen Center Drive +1-805-375-0680 (home) Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)