hi, we are using postgresql to analyze our web log, we got a 6M table, and while doing the query: SELECT url,sum(ct) as ctperkw from ctrraw group by url order by ctperkw desc limit 1000; the table structure is: CREATE TABLE ctrRAW ( cdate date, ip inet, kw varchar(128), prd varchar(6), pos int, url varchar(512), ct int ); create INDEX ctr_ip_index on ctrraw(ip); our boxes ia a freebsd 4.1 with 2G emmory, but our postgresql user got a 512M data sigment ulimit. and the query run out of memory, the log file attached. the postgresql was check out from CVS around June 25. we are going to test in newly checkout CVS tip, but want to report here first. regards laser
TopMemoryContext: 24576 total in 3 blocks; 7360 free (5 chunks); 17216 used Operator class cache: 8192 total in 1 blocks; 4916 free (0 chunks); 3276 used Type information cache: 8192 total in 1 blocks; 1844 free (0 chunks); 6348 used TopTransactionContext: 8192 total in 1 blocks; 7840 free (0 chunks); 352 used MessageContext: 24576 total in 2 blocks; 14812 free (4 chunks); 9764 used smgr relation table: 8192 total in 1 blocks; 2844 free (0 chunks); 5348 used Portal hash: 8192 total in 1 blocks; 1844 free (0 chunks); 6348 used PortalMemory: 8192 total in 1 blocks; 8036 free (0 chunks); 156 used PortalHeapMemory: 1024 total in 1 blocks; 868 free (0 chunks); 156 used ExecutorState: 24576 total in 2 blocks; 14740 free (14 chunks); 9836 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used AggContext: 467656704 total in 66 blocks; 3708 free (59 chunks); 467652996 used TupleHashTable: 67133468 total in 18 blocks; 6616496 free (40 chunks); 60516972 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 ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used Relcache by OID: 8192 total in 1 blocks; 3868 free (0 chunks); 4324 used CacheMemoryContext: 516096 total in 6 blocks; 219572 free (0 chunks); 296524 used ctr_url_idx: 1024 total in 1 blocks; 376 free (0 chunks); 648 used ctr_ip_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_index_indrelid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_type_typname_nsp_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_type_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_statistic_relid_att_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_shadow_usesysid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_shadow_usename_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used pg_proc_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 176 free (0 chunks); 848 used pg_operator_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_opclass_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used pg_namespace_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_namespace_nspname_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_language_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_language_name_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_index_indexrelid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_group_sysid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_group_name_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_conversion_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_conversion_name_nsp_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_conversion_default_index: 1024 total in 1 blocks; 176 free (0 chunks); 848 used pg_class_relname_nsp_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_class_oid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used pg_cast_source_target_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_amproc_opc_proc_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used pg_amop_opr_opc_index: 1024 total in 1 blocks; 312 free (0 chunks); 712 used pg_amop_opc_strat_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used pg_aggregate_fnoid_index: 1024 total in 1 blocks; 376 free (0 chunks); 648 used MdSmgr: 8192 total in 1 blocks; 7112 free (0 chunks); 1080 used LockTable (locallock hash): 8192 total in 1 blocks; 3892 free (0 chunks); 4300 used Timezones: 323228 total in 2 blocks; 5952 free (0 chunks); 317276 used ErrorContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used ERROR: out of memory DETAIL: Failed on request of size 144.
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
