Thread: Out of Memory

Out of Memory

From

"Abu Mushayeed"

Date:

06 November 2006, 19:46:43

I am trying to run the following query

SELECT
        cdc.cus_nbr,
        cdc.indiv_fkey,
        MAX(
                CASE
                        WHEN UPPER(pay.pay_typ) IN
('B','G','I','L','R','X','Y') THEN 'Y'
                        WHEN pay.pay_typ IN
('0','1','2','3','4','5','6','7','8','9') THEN 'P'
                        ELSE 'N'
                END
        ),
        MAX(
                CASE UPPER(pay.pay_typ)
                        WHEN 'E' THEN 'Y'
                        ELSE 'N'
                END
        )
FROM
        cdm.cdm_ddw_customer cdc,
        cdm.cdm_ddw_cust_paytype pay
WHERE
        pay.cus_nbr = cdc.cus_nbr
AND cdc.indiv_fkey IS NOT NULL
AND cdc.lst_dte >= (select start_date from cdm_epiphany.inc_date) --
'2003-11-15'::date --
AND cdc.lst_dte  < (select end_date from cdm_epiphany.inc_date)
--'2006-10-16'::date --
GROUP BY
        cdc.cus_nbr,
        cdc.indiv_fkey

Note: The table cdm_epiphany.inc_date has only one row.

When I do the explain plan, I get the following:

HashAggregate  (cost=672585.68..679836.00 rows=90629 width=21)
  InitPlan
    ->  Seq Scan on inc_date  (cost=0.00..1.01 rows=1 width=4)
    ->  Seq Scan on inc_date  (cost=0.00..1.01 rows=1 width=4)
  ->  Merge Join  (cost=17667.93..671646.97 rows=93669 width=21)
        Merge Cond: ("outer".cus_nbr = "inner".cus_nbr)
        ->  Index Scan using paytyp_cust_id on cdm_ddw_cust_paytype pay
(cost=0.00..593522.69 rows=23620542 width=13)
        ->  Sort  (cost=17667.93..17894.51 rows=90629 width=16)
              Sort Key: cdc.cus_nbr
              ->  Index Scan using cdm_ddwcust_lstdate_idx on
cdm_ddw_customer cdc  (cost=0.00..10205.68 rows=90629 width=16)
                    Index Cond: ((lst_dte >= $0) AND (lst_dte < $1))
                    Filter: (indiv_fkey IS NOT NULL)

Now, if I change the query to :

GroupAggregate  (cost=4952407.62..6300386.04 rows=14506983 width=21)
  ->  Sort  (cost=4952407.62..4989891.57 rows=14993583 width=21)
        Sort Key: cdc.cus_nbr, cdc.indiv_fkey
        ->  Merge Join  (cost=0.00..2889809.31 rows=14993583 width=21)
              Merge Cond: ("outer".cus_nbr = "inner".cus_nbr)
              ->  Index Scan using ddwcus_pk on cdm_ddw_customer cdc
(cost=0.00..2051240.77 rows=14506983 width=16)
                    Filter: ((indiv_fkey IS NOT NULL) AND (lst_dte >=
'2003-11-15'::date) AND (lst_dte < '2006-10-16'::date))
              ->  Index Scan using paytyp_cust_id on cdm_ddw_cust_paytype
pay  (cost=0.00..593522.69 rows=23620542 width=13)


Notice, the row returned from the two queries are way off. I have analyzed,
reanalyzed the table, changed the cdc.cus_nbr, cdc.indiv_fkey and
cdc.lst_dte columns statistics value to 300 and reanalyzed but I cannot get
to give me the same exact or closer row count.

The query outcome is "Out of Memory".

I would appreciate if someone can provide some guidance.

Thanks
Abu

_________________________________________________________________
Get FREE company branded e-mail accounts and business Web site from
Microsoft Office Live
http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/

Re: Out of Memory

From

Abu Mushayeed

Date:

06 November 2006, 21:31:42

I really could appreciate some help. I tried to run the following query and I get the following dump

Query:
----------------------------------------------------------------------------------------------------
SELECT
COUNT(*)
/*
        cdc.cus_nbr,
        cdc.indiv_fkey,
        MAX(
                CASE
                        WHEN UPPER(pay.pay_typ) IN ('B','G','I','L','R','X','Y') THEN 'Y'
                        WHEN pay.pay_typ IN ('0','1','2','3','4','5','6','7','8','9') THEN 'P'
                        ELSE 'N'
                END
        ),
        MAX(
                CASE UPPER(pay.pay_typ)
                        WHEN 'E' THEN 'Y'
                        ELSE 'N'
                END
        )
*/
FROM
        cdm.cdm_ddw_customer cdc,
        cdm.cdm_ddw_cust_paytype pay,
        cdm_epiphany.inc_date x
WHERE
        pay.cus_nbr = cdc.cus_nbr
AND cdc.indiv_fkey IS NOT NULL
AND cdc.lst_dte >= x.start_date -- '2003-11-15'::date --
AND cdc.lst_dte < x.end_date -- '2006-10-16'::date --
GROUP BY
        cdc.cus_nbr,
        cdc.indiv_fkey

====================================================
Query Plan:
-------------------
HashAggregate (cost=1166943.74..1192962.77 rows=2081523 width=16)
-> Merge Join (cost=467536.46..1151332.31 rows=2081523 width=16)
        Merge Cond: ("outer".cus_nbr = "inner".cus_nbr)
        -> Index Scan using paytyp_cust_id on cdm_ddw_cust_paytype pay (cost=0.00..593522.69 rows=23620542 width=8)
        -> Sort (cost=467536.46..472571.38 rows=2013969 width=16)
              Sort Key: cdc.cus_nbr
              -> Nested Loop (cost=0.00..256657.69 rows=2013969 width=16)
                    -> Seq Scan on inc_date x (cost=0.00..1.01 rows=1 width=8)
                    -> Index Scan using cdm_ddwcust_lstdate_idx on cdm_ddw_customer cdc (cost=0.00..226447.15 rows=2013969 width=20)
                          Index Cond: ((cdc.lst_dte >= "outer".start_date) AND (cdc.lst_dte < "outer".end_date))
                          Filter: (indiv_fkey IS NOT NULL)

====================================================
Dump:
---------------------------------------------------------------------------
TopMemoryContext: 61976 total in 6 blocks; 11176 free (9 chunks); 50800 used
TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks); 336 used
Record information cache: 8192 total in 1 blocks; 1864 free (0 chunks); 6328 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: 57344 total in 3 blocks; 19296 free (80 chunks); 38048 used
smgr relation table: 8192 total in 1 blocks; 2872 free (0 chunks); 5320 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; 7000 free (4 chunks); 17576 used
TupleSort: 142637924 total in 29 blocks; 125794424 free (863578 chunks); 16843500 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
ExprContext: 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
ExprContext: 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
AggContext: -2002788352 total in 292 blocks; 5016 free (152 chunks); -2002793368 used
TupleHashTable: 547610648 total in 77 blocks; 223032 free (284 chunks); 547387616 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; 80144 free (0 chunks); 435952 used
paytyp_cust_id: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
ddwcust_indiv_fkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
ddw_cust_zip_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
cdm_ddwcust_lstdate_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
ddwcus_pk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_database_oid_index: 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_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; 6880 free (0 chunks); 1312 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-11-06 14:20:12 PSTamushayeedmdc_oz11.16.152.174ERROR: out of memory
2006-11-06 14:20:12 PSTamushayeedmdc_oz11.16.152.174DETAIL: Failed on request of size 68.

Abu Mushayeed <amushayeed@hotmail.com> wrote:

I am trying to run the following query

SELECT
cdc.cus_nbr,
cdc.indiv_fkey,
MAX(
CASE
WHEN UPPER(pay.pay_typ) IN
('B','G','I','L','R','X','Y') THEN 'Y'
WHEN pay.pay_typ IN
('0','1','2','3','4','5','6','7','8','9') THEN 'P'
ELSE 'N'
END
),
MAX(
CASE UPPER(pay.pay_typ)
WHEN 'E' THEN 'Y'
ELSE 'N'
END
)
FROM
cdm.cdm_ddw_customer cdc,
cdm.cdm_ddw_cust_paytype pay
WHERE
pay.cus_nbr = cdc.cus_nbr
AND cdc.indiv_fkey IS NOT NULL
AND cdc.lst_dte >= (select start_date from cdm_epiphany.inc_date) --
'2003-11-15'::date --
AND cdc.lst_dte < (select end_date from cdm_epiphany.inc_date)
--'2006-10-16'::date --
GROUP BY
cdc.cus_nbr,
cdc.indiv_fkey

Note: The table cdm_epiphany.inc_date has only one row.

When I do the explain plan, I get the following:

HashAggregate (cost=672585.68..679836.00 rows=90629 width=21)
InitPlan
-> Seq Scan on inc_date (cost=0.00..1.01 rows=1 width=4)
-> Seq Scan on inc_date (cost=0.00..1.01 rows=1 width=4)
-> Merge Join (cost=17667.93..671646.97 rows=93669 width=21)
Merge Cond: ("outer".cus_nbr = "inner".cus_nbr)
-> Index Scan using paytyp_cust_id on cdm_ddw_cust_paytype pay
(cost=0.00..593522.69 rows=23620542 width=13)
-> Sort (cost=17667.93..17894.51 rows=90629 width=16)
Sort Key: cdc.cus_nbr
-> Index Scan using cdm_ddwcust_lstdate_idx on
cdm_ddw_customer cdc (cost=0.00..10205.68 rows=90629 width=16)
Index Cond: ((lst_dte >= $0) AND (lst_dte < $1))
Filter: (indiv_fkey IS NOT NULL)

Now, if I change the query to :

GroupAggregate (cost=4952407.62..6300386.04 rows=14506983 width=21)
-> Sort (cost=4952407.62..4989891.57 rows=14993583 width=21)
Sort Key: cdc.cus_nbr, cdc.indiv_fkey
-> Merge Join (cost=0.00..2889809.31 rows=14993583 width=21)
Merge Cond: ("outer".cus_nbr = "inner".cus_nbr)
-> Index Scan using ddwcus_pk on cdm_ddw_customer cdc
(cost=0.00..2051240.77 rows=14506983 width=16)
Filter: ((indiv_fkey IS NOT NULL) AND (lst_dte >=
'2003-11-15'::date) AND (lst_dte < '2006-10-16'::date))
-> Index Scan using paytyp_cust_id on cdm_ddw_cust_paytype
pay (cost=0.00..593522.69 rows=23620542 width=13)

Notice, the row returned from the two queries are way off. I have analyzed,
reanalyzed the table, changed the cdc.cus_nbr, cdc.indiv_fkey and
cdc.lst_dte columns statistics value to 300 and reanalyzed but I cannot get
to give me the same exact or closer row count.

The query outcome is "Out of Memory".

I would appreciate if someone can provide some guidance.

Thanks
Abu

_________________________________________________________________
Get FREE company branded e-mail accounts and business Web site from
Microsoft Office Live
http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Low, Low, Low Rates! Check out Yahoo! Messenger's cheap PC-to-Phone call rates.

Re: Out of Memory

From

Tom Lane

Date:

07 November 2006, 03:52:41

Abu Mushayeed <abumushayeed@yahoo.com> writes:
> I really could appreciate some help.

If nothing else, "set enable_hashagg = off" should help.  But have you
analyzed these tables lately?  Perhaps you have work_mem set too high?

            regards, tom lane