I've reported variants of this in the past, but this case is entirely
repeatable.
Executing this query:
select st_id, min(seed_id) as "initial_seed_id", count(*) as
"seed_count"
from seed group by st_id;
The query plan and table stats are:
QUERY PLAN
-----------------------------------------------------------------------
HashAggregate (cost=1362694.83..1365164.68 rows=164656 width=16)
-> Seq Scan on seed (cost=0.00..964065.62 rows=53150562 width=16)
relname | relpages | reltuples
---------+----------+-------------
seed | 428880 | 5.26984e+07
Environment:
Debian linux, kernel v. 2.6.13.1-20050914
Dual opterons w/8G RAM
Postgresql v 8.1.3
Pg Config:
shared_buffers = 50000
work_mem = 262144
maintenance_work_mem = 262144
max_fsm_pages = 100000
When the query blows up I get this spew in the server log:
TopMemoryContext: 40960 total in 5 blocks; 12192 free (11 chunks);
28768 used
TopTransactionContext: 8192 total in 1 blocks; 5784 free (0 chunks);
2408 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; 15080 free (4 chunks); 9496
used
smgr relation table: 8192 total in 1 blocks; 1840 free (0 chunks);
6352 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; 896 free (0 chunks); 128 used
ExecutorState: 8192 total in 1 blocks; 1928 free (17 chunks); 6264 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AggContext: -2001739776 total in 285 blocks; 4784 free (141 chunks);
-2001744560 used
TupleHashTable: 497279000 total in 70 blocks; 1772200 free (259
chunks); 495506800 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used
CacheMemoryContext: 516096 total in 6 blocks; 46448 free (6 chunks);
469648 used
pg_shdepend_depender_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_shdepend_reference_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_depend_depender_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_depend_reference_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
seed_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
seed_station_music_id_idx: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
seed_music_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
seed_station_id: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
feedback_pkey: 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_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
listener_segment_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
pg_constraint_conrelid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
listener_default_web_name_key: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
listener_default_username_key: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
listener_default_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_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_statistic_relid_att_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_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; 4768 free (1 chunks); 3424 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-09-17 21:31:42.753 PDT [d:radio_prod_full u:radio s:
450e0813.6b5d 4] ERROR: out of memory
2006-09-17 21:31:42.753 PDT [d:radio_prod_full u:radio s:
450e0813.6b5d 5] DETAIL: Failed on request of size 88.
2006-09-17 21:31:42.753 PDT [d:radio_prod_full u:radio s:
450e0813.6b5d 6] STATEMENT: select st_id, min(seed_id) as
"initial_seed_id",
count(*) as "seed_count"
from seed group by st_id;
If I execute "set enable_hashagg=off;" before running this query it
completes successfully.
Thanks!
-Casey
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
