Message-ID: <BAY108-F394DBD5B58F5B21A8147AED1F30@phx.gbl>
From: "Abu Mushayeed" <amushayeed@hotmail.com>
To: pgsql-admin@postgresql.org
Subject: Out of Memory
Date: Mon, 06 Nov 2006 12:46:35 -0800
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

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/