DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:subject:content-type:content-transfer-encoding;
	b=RedcYr+zV6GYlzIbL98jvkGg+GfYohJLfwuouBNqtghWuKFqtC918f2Ynvw2iQbJtNEksc41g+FrcKthkYh6essOMuJ6KAXzOcMUvMfdzh1/7ZWm9fRSPIJvmjeNS63E/FAd3YEfNAI6bYOv5FuxL/4/WVlfUd7toEt/+tTo2W0=
Message-ID: <4738B03C.8090901@gmail.com>
Date: Mon, 12 Nov 2007 20:57:48 +0100
From: Stephane Bailliez <sbailliez@gmail.com>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: ERROR: "invalid memory alloc request size" or "unexpected end of
	data" on large table
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

(posting on pgsql-perf as I'm questioning the pertinence of the 
settings, might not be the best place for the overall pb: apologies)

Postgresql 8.1.10
Linux Ubuntu: 2.6.17-12-server
4GB RAM, machine is only used for this I do have less than 30 tables, 4 
of them having between 10-40 million rows, size on disk is approximately 50G
Nothing spectacular on the install, it's mainly sandbox.

Relevant bits of the postgresql.conf
max_connections = 15
shared_buffers = 49152
work_mem = 16384
maintenance_work_mem = 32768
max_fsm_pages = 40000
effective_cache_size = 100000


I'm doing a rather 'simplistic' query, though heavy on hashing and 
aggregate:

For the records:
select count(*) from action where action_date between '2007-10-01' and 
'2007-10-31'
9647980


The query is:

select tspent, count(*) from (
select sum(time_spent)/60 as tspent from action
where action_date between '2007-10-01' and '2007-10-31'
group by action_date, user_id
) as a
group by tstpent
order by tspent asc;

I do receive a memory alloc error for a 1.5GB request size. So I may 
have oversized something significantly that is exploding (work_mem ?)
(I was running an explain analyze and had a pgsql_tmp dir reaching 2.9GB 
until it died with result similar error as with the query alone)

ERROR: invalid memory alloc request size 1664639562
SQL state: XX000

Sometimes I do get:

ERROR: unexpected end of data
SQL state: XX000


table is along the line of  (sorry cannot give you the full table):

CREATE TABLE action (
  id SERIAL,
  action_date  DATE NOT NULL,
  time_spent INT NOT NULL,
  user_id TEXT NOT NULL,  -- user id is a 38 character string
  ...
);

CREATE INDEX action_action_date_idx
  ON action USING btree(action_date);

Here is an explain analyze for just 1 day:

"HashAggregate  (cost=709112.04..709114.54 rows=200 width=8) (actual 
time=9900.994..9902.188 rows=631 loops=1)"
"  ->  HashAggregate  (cost=706890.66..708001.35 rows=74046 width=49) 
(actual time=9377.654..9687.964 rows=122644 loops=1)"
"        ->  Bitmap Heap Scan on action  (cost=6579.73..701337.25 
rows=740455 width=49) (actual time=2409.697..6756.027 rows=893351 loops=1)"
"              Recheck Cond: ((action_date >= '2007-10-01'::date) AND 
(action_date <= '2007-10-02'::date))"
"              ->  Bitmap Index Scan on action_action_date_idx  
(cost=0.00..6579.73 rows=740455 width=0) (actual time=2373.837..2373.837 
rows=893351 loops=1)"
"                    Index Cond: ((action_date >= '2007-10-01'::date) 
AND (action_date <= '2007-10-02'::date))"
"Total runtime: 9933.165 ms"


-- stephane