(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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
