Обсуждение: Out of memory error

Hello,

I am running the following query:

SELECT
    indiv_fkey,
    MAX(emp_ind),
    MAX(prizm_cd_indiv),
    MAX(CASE
            WHEN div IS NULL THEN NULL
            WHEN store_loyal_loc_cd IS NULL THEN NULL
            ELSE div || '-' || store_loyal_loc_cd
        END)    ,
    MAX(supp_all_adv_ind)    ,
    MAX(supp_direct_mail_ind)
FROM
    cdm.cdm_fedcustomer
WHERE
    kept_acct_flg = 'Y' AND indiv_fkey IS NOT NULL
GROUP BY
    indiv_fkey;

AFTER A WHILE THE SYSTEM COMES BACK AND SAYS IN THE LOG FILE:

TopMemoryContext: 45592 total in 4 blocks; 4600 free (14 chunks); 40992 used
TopTransactionContext: 8192 total in 1 blocks; 8136 free (0 chunks); 56 used
DeferredTriggerXact: 0 total in 0 blocks; 0 free (0 chunks); 0 used
MessageContext: 57344 total in 3 blocks; 23312 free (1 chunks); 34032 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
ExecutorState: 24576 total in 2 blocks; 7416 free (3 chunks); 17160 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AggContext: -1501569024 total in 351 blocks; 69904 free (507 chunks);
-1501638928 used
DynaHashTable: 302047256 total in 46 blocks; 275720 free (66 chunks);
301771536 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
CacheMemoryContext: 516096 total in 6 blocks; 114992 free (1 chunks); 401104
used
indiv_key_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
fed_cust_indiv_sum_pk: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
cdm_fedcustomer_idx4: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
fedcust_lname_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
fedcust_indivfkey_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 320 free (0 chunks);
704 used
pg_database_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_database_datname_index: 1024 total in 1 blocks; 640 free (0 chunks); 384
used
pg_index_indrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384
used
pg_amop_opc_strategy_index: 1024 total in 1 blocks; 320 free (0 chunks); 704
used
pg_shadow_usename_index: 1024 total in 1 blocks; 640 free (0 chunks); 384
used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_conversion_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384
used
pg_language_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384
used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 320 free (0 chunks);
704 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 320 free (0
chunks); 704 used
pg_shadow_usesysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384
used
pg_cast_source_target_index: 1024 total in 1 blocks; 320 free (0 chunks);
704 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks);
704 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 320 free (0
chunks); 704 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 640 free (0 chunks); 384
used
pg_conversion_default_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704
used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384
used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 320 free (0 chunks);
704 used
pg_language_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704
used
pg_group_sysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_namespace_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384
used
pg_proc_proname_args_nsp_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
pg_opclass_am_name_nsp_index: 2048 total in 1 blocks; 768 free (0 chunks);
1280 used
pg_group_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_proc_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_operator_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_amproc_opc_procnum_index: 1024 total in 1 blocks; 320 free (0 chunks);
704 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384
used
pg_operator_oprname_l_r_n_index: 2048 total in 1 blocks; 704 free (0
chunks); 1344 used
pg_opclass_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 320 free (0 chunks);
704 used
pg_type_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 320 free (0
chunks); 704 used
pg_class_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
MdSmgr: 8192 total in 1 blocks; 6000 free (0 chunks); 2192 used
DynaHash: 8192 total in 1 blocks; 6560 free (0 chunks); 1632 used
DynaHashTable: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used
DynaHashTable: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used
DynaHashTable: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used
DynaHashTable: 8192 total in 1 blocks; 1984 free (0 chunks); 6208 used
DynaHashTable: 8192 total in 1 blocks; 3520 free (0 chunks); 4672 used
DynaHashTable: 24576 total in 2 blocks; 13240 free (4 chunks); 11336 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
2006-06-23 09:27:52 [27143] ERROR:  out of memory
DETAIL:  Failed on request of size 420.
STATEMENT:  --INSERT INTO cdm_epiphany.fed_cust_indiv_summary_staging
        SELECT
                indiv_fkey,
                MAX(emp_ind),
                MAX(prizm_cd_indiv),
                MAX(CASE
                                WHEN div IS NULL THEN NULL
                                WHEN store_loyal_loc_cd IS NULL THEN NULL
                                ELSE div || '-' || store_loyal_loc_cd
                        END)    ,
                MAX(supp_all_adv_ind)   ,
                MAX(supp_direct_mail_ind)
        FROM
                cdm.cdm_fedcustomer
        WHERE
                kept_acct_flg = 'Y' AND indiv_fkey IS NOT NULL
        GROUP BY
                indiv_fkey;

===================================================================================================================
BUT WHEN I RAN THE FOLLOWING QUERY IT CAME BACK WITH THE COUNT. I DID AN
EXPLAIN AND IT IS DOING A FULL TABLE SCAN WITH AN AGGREAGATE, BUT IN THE
ABOVE QUERY IT IS DOING A FULL TABLE SCAN AND A HASH AGGREGATE TO GET TO THE
RESULT.

SELECT
    count(*)
/*
    indiv_fkey,
    MAX(emp_ind),
    MAX(prizm_cd_indiv),
    MAX(CASE
            WHEN div IS NULL THEN NULL
            WHEN store_loyal_loc_cd IS NULL THEN NULL
            ELSE div || '-' || store_loyal_loc_cd
        END)    ,
    MAX(supp_all_adv_ind)    ,
    MAX(supp_direct_mail_ind)
*/
FROM
    cdm.cdm_fedcustomer
WHERE
    kept_acct_flg = 'Y' AND indiv_fkey IS NOT NULL
--GROUP BY
--    indiv_fkey;



I AM NEW TO POSTGRES AND I WOULD LIKE TO KNOW HOW TO FIX THIS ISSUE.

Thanks
Abu

"Abu Mushayeed" <amushayeed@hotmail.com> writes:
> AFTER A WHILE THE SYSTEM COMES BACK AND SAYS IN THE LOG FILE:

Please turn off your caps lock key :-(

> AggContext: -1501569024 total in 351 blocks; 69904 free (507 chunks);
> -1501638928 used
> DynaHashTable: 302047256 total in 46 blocks; 275720 free (66 chunks);
> 301771536 used

Hm, it's evidently misjudging how much memory will be needed by a
HashAggregate plan, probably because it's underestimating the number
of GROUP BY groups.  Could we see the EXPLAIN output for the query?
What PG version is this exactly?  Have you ANALYZEd the table lately?

            regards, tom lane