On 03/26/2012 12:20 PM, Greg Stark wrote:
On Mon, Mar 26, 2012 at 4:03 PM, Andrew Dunstan<and...@dunslane.net> wrote:
TupleHashTable: 619175960 total in 95 blocks; 821528 free
(331 chunks); 618354432 used
I think the plan you showed isn't the plan that's running out of
memory. I think it's running out of memory because it's using a Hash
Aggregate and underestimating the number of distinct elements. I would
set enable_hash_aggregate=false for this query (not for the whole
server, hash aggregates are quite useful in general).
This is a long-standing problem with hash aggregates. The problem is
that we don't have a clever way to fall back to a non-hash-aggregate
if the original estimated memory usage turns out to be way off. Just
spilling to disk the way hash joins do would be horrendously
expensive, we think. If we had an approach to doing so it would be
very useful because simply running out of memory is kind of rude.
Ugh, Ok, thanks for the explanation.
So it looks like I'm running into two separate memory problems. Here's a
trace from the other one
cheers
andrew
TopMemoryContext: 49816 total in 6 blocks; 6840 free (9 chunks);
42976 used
Operator class cache: 8192 total in 1 blocks; 3848 free (0
chunks); 4344 used
Filesystem: 8192 total in 1 blocks; 7872 free (0 chunks); 320 used
TopTransactionContext: 213901336 total in 10 blocks; 1796344 free
(25 chunks); 212104992 used
Combo CIDs: 755490840 total in 100 blocks; 5161072 free (381
chunks); 750329768 used
MessageContext: 73728 total in 4 blocks; 44920 free (15 chunks);
28808 used
smgr relation table: 8192 total in 1 blocks; 2816 free (0
chunks); 5376 used
TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0
chunks); 16 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; 920 free (0 chunks);
104 used
ExecutorState: 8192 total in 1 blocks; 5880 free (1 chunks);
2312 used
ExprContext: 24576 total in 2 blocks; 13664 free (8
chunks); 10912 used
Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks);
4816 used
CacheMemoryContext: 405552 total in 19 blocks; 14296 free (5
chunks); 391256 used
pg_largeobject_loid_pn_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 192 free
(0 chunks); 832 used
pg_foreign_data_wrapper_name_index: 1024 total in 1 blocks; 344
free (0 chunks); 680 used
pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_foreign_server_oid_index: 1024 total in 1 blocks; 344 free
(0 chunks); 680 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
pg_cast_source_target_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
pg_language_name_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_foreign_data_wrapper_oid_index: 1024 total in 1 blocks; 344
free (0 chunks); 680 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 280
free (0 chunks); 744 used
pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_conversion_default_index: 1024 total in 1 blocks; 128 free
(0 chunks); 896 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 128
free (0 chunks); 896 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240
free (0 chunks); 784 used
pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_user_mapping_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free
(0 chunks); 832 used
pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_operator_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_amop_opr_fam_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 280
free (0 chunks); 744 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_class_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 152
free (0 chunks); 872 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240
free (0 chunks); 784 used
pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_language_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_namespace_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_database_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_foreign_server_name_index: 1024 total in 1 blocks; 344 free
(0 chunks); 680 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 280
free (0 chunks); 744 used
pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_user_mapping_user_server_index: 1024 total in 1 blocks; 280
free (0 chunks); 744 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
MdSmgr: 8192 total in 1 blocks; 6160 free (0 chunks); 2032 used
LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks);
4280 used
Timezones: 79320 total in 2 blocks; 5968 free (0 chunks); 73352 used
ErrorContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used
2012-03-24 10:59:39 EDT ERROR: out of memory
2012-03-24 10:59:39 EDT DETAIL: Failed on request of size 419430400.
2012-03-24 10:59:39 EDT STATEMENT: SELECT pg_catalog.lowrite(0, ....
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
