From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Smith <sodgodofall@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance of aggregates over set-returning functions
Date: Tue, 08 Jan 2008 22:07:23 -0500
Message-ID: <25214.1199848043@sss.pgh.pa.us> (raw)
In-Reply-To: <b88f0d670801081821r70fb10b4j4c176eed96918434@mail.gmail.com>
References: <b88f0d670801081821r70fb10b4j4c176eed96918434@mail.gmail.com>
"John Smith" <sodgodofall@gmail.com> writes:
> Consider the following query:
> postgres=# select count(*) from generate_series(1,100000000000000000);
> A vmstat while this query was running seems to suggest that the
> generate_series() was being materialized to disk first and then the
> count computed
Yeah, that's what I'd expect given the way nodeFunctionscan.c works.
> A small variation in the query (i.e. placing the generate_series in
> the select list without a from clause) exhibited a different
> behaviour:
> postgres=# select count(*) from (select
> generate_series(1,100000000000000000)) as A;
> This time, Postgres seemed to be trying to build the entire
> generate_series() result set in memory and eventually started to swap
> out until the swap space limit after which it crashed.
Hmm, at worst you should get an "out of memory" error --- that's what
I get. I think you're suffering from the dreaded "OOM kill" disease.
> Interestingly though, when the range in the generate_series() was
> small enough to fit in 4 bytes of memory (e.g.
> generate_series(1,1000000000) ), the above query completed consuming
> only negligible amount of memory. So, it looked like the aggregate
> computation was being pipelined with the tuples returned from
> generate_series().
It's pipelined either way. But int8 is a pass-by-reference data type,
and it sounds like we have a memory leak for this case.
> Third question: more generally, is there a way I can ask Postgres to
> report an Out-of-memory the moment it tries to consume greater than a
> certain percentage of memory (instead of letting it hit the swap and
> eventually die or thrash) ?
First, you want to turn off memory overcommit in your kernel;
second, you might find that running the postmaster under conservative
ulimit settings would make the behavior nicer.
regards, tom lane
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sodgodofall@gmail.com
Subject: Re: Performance of aggregates over set-returning functions
In-Reply-To: <25214.1199848043@sss.pgh.pa.us>
* 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