pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mark Dalphin <mdalphin@amgen.com>
To: pgsql-sql@postgresql.org
Subject: How to avoid "Out of memory" using aggregate functions?
Date: Thu, 02 Dec 1999 11:30:27 -0800
Message-ID: <3846C8D3.DB066E78@amgen.com> (raw)

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)






view thread (5+ messages)  latest in thread

Message-ID: <3846C8D3.DB066E78@amgen.com>
Permalink:  ../3846C8D3.DB066E78@amgen.com/
Also on:    postgresql.org/message-id/3846C8D3.DB066E78@amgen.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mdalphin@amgen.com
  Subject: Re: How to avoid "Out of memory" using aggregate functions?
  In-Reply-To: <3846C8D3.DB066E78@amgen.com>
  References: <3846C8D3.DB066E78@amgen.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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