Skip site navigation (1) Skip section navigation (2)

ERROR: out of memory, running aggregate query

From: Casey Duncan <casey(at)pandora(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: ERROR: out of memory, running aggregate query
Date: 2006-05-04 17:23:12
Message-ID: 9C14CB9A-E1C9-4C9A-8F85-D2934F8A7C24@pandora.com (view raw or flat)
Thread:
Lists: pgsql-bugs
I have the following query which ran fine on 7.4 and fails  
consistently now that I've upgraded to 8.1.3. Here is the query and  
the plan (I can't explain analyze because of the memory error):

prod=> explain select count(*) from
prod->   (select st_id from sd
prod(>    group by st_id having count(*) > 1)
prod-> as multi_sd;
                                  QUERY PLAN
------------------------------------------------------------------------ 
----
Aggregate  (cost=558194.08..558194.09 rows=1 width=0)
    ->  HashAggregate  (cost=555076.97..556777.21 rows=113349 width=4)
          Filter: (count(*) > 1)
          ->  Seq Scan on sd  (cost=0.00..428498.65 rows=25315665  
width=4)
(4 rows)

Here is what I see in the back-end logs:

TopMemoryContext: 61976 total in 6 blocks; 10712 free (16 chunks);  
51264 used
TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks);  
336 used
Type information cache: 8192 total in 1 blocks; 1864 free (0 chunks);  
6328 used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks);  
3256 used
MessageContext: 24576 total in 2 blocks; 9520 free (1 chunks); 15056  
used
smgr relation table: 8192 total in 1 blocks; 808 free (0 chunks);  
7384 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 1024 total in 1 blocks; 880 free (0 chunks); 144 used
ExecutorState: 24576 total in 2 blocks; 18736 free (11 chunks); 5840  
used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AggContext: -1846550528 total in 304 blocks; 5232 free (158 chunks);  
-1846555760 used
TupleHashTable: 587456536 total in 80 blocks; 2776520 free (298  
chunks); 584680016 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AggContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used
CacheMemoryContext: 516096 total in 6 blocks; 106504 free (1 chunks);  
409592 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
seed_station_music_id_idx: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
seed_station_id: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
seed_music_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
seed_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
listener_to_station_station_id_idx: 1024 total in 1 blocks; 392 free  
(0 chunks); 632 used
listener_to_station_pkey: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
feedback_station_music_id_idx: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
feedback_station_id_idx: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
feedback_music_id_idx: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
feedback_date_created_is_positive_idx: 1024 total in 1 blocks; 328  
free (0 chunks); 696 used
feedback_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
listener_expiration_date_idx: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
listener_web_name_key: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
listener_username_key: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
listener_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_index_indrelid_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_constraint_conrelid_index: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_type_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free  
(0 chunks); 696 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free  
(0 chunks); 696 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 256 free (0  
chunks); 768 used
pg_proc_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 192 free (0  
chunks); 832 used
pg_operator_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_opclass_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 256 free (0  
chunks); 768 used
pg_namespace_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
pg_authid_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632  
used
pg_authid_rolname_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_database_datname_index: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0  
chunks); 832 used
pg_operator_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_opclass_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 256 free (0  
chunks); 768 used
pg_namespace_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
pg_authid_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632  
used
pg_authid_rolname_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_database_datname_index: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0  
chunks); 832 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_class_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632  
used
pg_cast_source_target_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 256 free (0  
chunks); 768 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 328 free (0 chunks);  
696 used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 256 free (0 chunks);  
768 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
MdSmgr: 8192 total in 1 blocks; 5128 free (0 chunks); 3064 used
LockTable (locallock hash): 8192 total in 1 blocks; 3912 free (0  
chunks); 4280 used
Timezones: 47592 total in 2 blocks; 5968 free (0 chunks); 41624 used
ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used
2006-05-04 06:13:17.614 PDT [d:prod u:casey s:4459d0a1.54c 3] ERROR:   
out of memory
2006-05-04 06:13:17.614 PDT [d:prod u:casey s:4459d0a1.54c 4]  
DETAIL:  Failed on request of size 80.

This line in particular looks suspicious to me:

AggContext: -1846550528 total in 304 blocks; 5232 free (158 chunks);  
-1846555760 used

Here are some of the memory-related config settings for this server:

shared_buffers = 20000          # min 16 or max_connections*2, 8KB each
work_mem = 8192                 # 8 Mb
maintenance_work_mem = 131072   # 128 Mb

The machine itself is a dual opteron with 8GB of RAM running debian  
linux. The postgres backend process running this query bloats up to  
roughly 3GB resident or so before blowing up.

Let me know if I can provide any other details or perform other tests.

Thanks much.

-Casey



pgsql-bugs by date

Next:From: Robert LorDate: 2006-05-04 19:17:56
Subject: Re: [BUGS] BUG #2401: spinlocks not available on amd64
Previous:From: Simon BurgeDate: 2006-05-04 06:49:54
Subject: BUG #2420: NetBSD doesn't need float8-small-is-zero regression test resultmap entry

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group