Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id BFC2D9FB1F5 for ; Mon, 6 Nov 2006 18:31:41 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 75577-10 for ; Mon, 6 Nov 2006 22:31:36 +0000 (UTC) X-Greylist: delayed 00:06:39.949168 by SQLgrey- Received: from web57104.mail.re3.yahoo.com (web57104.mail.re3.yahoo.com [216.252.111.117]) by postgresql.org (Postfix) with SMTP id CF6AD9FA497 for ; Mon, 6 Nov 2006 18:31:35 -0400 (AST) Received: (qmail 38710 invoked by uid 60001); 6 Nov 2006 22:24:54 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=oGESmWyV6s0vPV7DDVk43e3VBIpOvcKpGSvZFgtu/ZfnYGJLy6gmPo/cZE1F5WTh8hJZWQ708Kzj81wqb9RA0ae/m+8S14eGJ0nI7WgAGGFPwtUpLG/R9rFmjTNxTB3X7FP6PMZ9Ln3emI6QxRkY4I5VEheP2d65O6U5YCGw8uo= ; Message-ID: <20061106222454.38708.qmail@web57104.mail.re3.yahoo.com> Received: from [64.237.4.140] by web57104.mail.re3.yahoo.com via HTTP; Mon, 06 Nov 2006 14:24:54 PST Date: Mon, 6 Nov 2006 14:24:54 -0800 (PST) From: Abu Mushayeed Subject: Re: Out of Memory To: Abu Mushayeed , pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1086990899-1162851894=:37771" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.757 tagged_above=0 required=5 tests=BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200611/36 X-Sequence-Number: 23563 --0-1086990899-1162851894=:37771 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 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. --0-1086990899-1162851894=:37771 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --0-1086990899-1162851894=:37771--