Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 4E5CC9FBD84 for ; Thu, 1 Mar 2007 10:13:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 71941-10 for ; Thu, 1 Mar 2007 10:13:42 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from web57107.mail.re3.yahoo.com (web57107.mail.re3.yahoo.com [216.252.111.120]) by postgresql.org (Postfix) with SMTP id 814349FBC4D for ; Thu, 1 Mar 2007 10:13:47 -0400 (AST) Received: (qmail 13371 invoked by uid 60001); 1 Mar 2007 14:13:45 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=2Bb2A602Cizr9a39BeMWDah89QNQs86dYaW6DhHmca5j6KatDfU0zER8goEn8TP5EO33EQ+PavVuN1WWhWWnlJRonjYP/M6McmztlawSGfpos+jntjV17SBnJVrl7Jf1xbzida7rqvqxOev4rDp5q3CuLYTtAW2aCdBbIDYRrEY=; X-YMail-OSG: 44utYHUVM1nl04Kr0GsYuz_LKVs0SomnEfyTjaSTgU.XMud3zd_OZwYY5Nqk94Acwg-- Received: from [64.175.40.2] by web57107.mail.re3.yahoo.com via HTTP; Thu, 01 Mar 2007 06:13:45 PST Date: Thu, 1 Mar 2007 06:13:45 -0800 (PST) From: Abu Mushayeed Subject: Performance Query To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1797542960-1172758425=:13110" Content-Transfer-Encoding: 8bit Message-ID: <906038.13110.qm@web57107.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.853 tagged_above=0 required=5 tests=BAYES_50, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_40_50, HTML_MESSAGE X-Spam-Level: *** X-Archive-Number: 200703/8 X-Sequence-Number: 23413 --0-1797542960-1172758425=:13110 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 1. The function: SELECT a.birth_date FROM ( SELECT indiv_fkey, birth_dt as birth_date, intern_last_update::date as last_update, 'fed' as source FROM cdm.cdm_fedcustomer WHERE birth_dt IS NOT NULL AND indiv_fkey = $1 UNION SELECT indiv_fkey, birthdate as birth_date, last_update::date as last_update, 'reg' as source FROM cdm.cdm_reg_customer WHERE birthdate IS NOT NULL AND indiv_fkey = $1 ORDER BY source asc, last_update desc limit 1 ) a 2. The query: INSERT INTO indiv_mast.staging_birthdate SELECT * FROM ( SELECT im.indiv_key, indiv_mast.getbest_bday(im.indiv_key::integer) AS birth_date FROM indiv_mast.indiv_mast Im WHERE im.indiv_key >= 2000000 AND im.indiv_key < 4000000 ) b WHERE b.birth_date IS NOT NULL ; 3. The query plan: Bitmap Heap Scan on indiv_mast im (cost=28700.91..2098919.14 rows=1937250 width=8) Recheck Cond: ((indiv_key >= 2000000) AND (indiv_key < 4000000)) Filter: (indiv_mast.getbest_bday((indiv_key)::integer) IS NOT NULL) -> Bitmap Index Scan on indiv_mast_pkey_idx (cost=0.00..28700.91 rows=1946985 width=0) Index Cond: ((indiv_key >= 2000000) AND (indiv_key < 4000000)) 4. Number of records in the tables: indiv_mast.indiv_mast : 15Million cdm.cdm_fedcustomer: 18Million cdm.cdm_reg_customer: 9 Million The query (2) runs for hours. It started at 2:00Am last night and it is still running (6:00Am). Some of the postgresql.conf file parameters are below: shared_buffers = 20000 #60000 work_mem = 65536 #131072 #65536 maintenance_work_mem = 524288 #131072 max_fsm_pages = 8000000 max_fsm_relations = 32768 wal_buffers = 128 checkpoint_segments = 256 # in logfile segments, min 1, 16MB each checkpoint_timeout = 3600 checkpoint_warning = 300 effective_cache_size = 20000 random_page_cost = 2 # (same) I really do not know how to find out what the query is waiting on, unlike oracle db provides some of the information through its dynamic performance views. Please help in understanding how I can find out what the system is waiting for or why is it taking the query so long. I will really appreciate some help. Thanks Abu --------------------------------- Sucker-punch spam with award-winning protection. Try the free Yahoo! Mail Beta. --0-1797542960-1172758425=:13110 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
1. The function:
 
SELECT a.birth_date FROM (
 SELECT  indiv_fkey, birth_dt as birth_date, intern_last_update::date as last_update, 'fed' as source
    
  FROM cdm.cdm_fedcustomer
  WHERE birth_dt IS NOT NULL
  AND indiv_fkey = $1
  UNION
  SELECT  indiv_fkey, birthdate as birth_date, last_update::date as last_update, 'reg' as source
  FROM cdm.cdm_reg_customer
  WHERE birthdate IS NOT NULL
  AND indiv_fkey = $1
ORDER BY source asc, last_update desc limit 1
)   a
 
2. The query:
 
INSERT INTO  indiv_mast.staging_birthdate
        SELECT * FROM (
        SELECT im.indiv_key,   indiv_mast.getbest_bday(im.indiv_key::integer) AS birth_date
        FROM indiv_mast.indiv_mast Im
        WHERE im.indiv_key >= 2000000 AND im.indiv_key < 4000000
        ) b
        WHERE b.birth_date IS NOT NULL
        ;
 
3. The query plan:
 
Bitmap Heap Scan on indiv_mast im  (cost=28700.91..2098919.14 rows=1937250 width=8)
  Recheck Cond: ((indiv_key >= 2000000) AND (indiv_key < 4000000))
  Filter: (indiv_mast.getbest_bday((indiv_key)::integer) IS NOT NULL)
  ->  Bitmap Index Scan on indiv_mast_pkey_idx  (cost=0.00..28700.91 rows=1946985 width=0)
        Index Cond: ((indiv_key >= 2000000) AND (indiv_key < 4000000))
4. Number of records in the tables:
 
indiv_mast.indiv_mast : 15Million
cdm.cdm_fedcustomer: 18Million
cdm.cdm_reg_customer: 9 Million
 
The query (2) runs for hours. It started at 2:00Am last night and it is still running (6:00Am).
 
Some of the postgresql.conf file parameters are below:
 
shared_buffers = 20000 #60000
work_mem = 65536 #131072 #65536
maintenance_work_mem = 524288 #131072
max_fsm_pages = 8000000
max_fsm_relations = 32768
wal_buffers = 128
checkpoint_segments = 256               # in logfile segments, min 1, 16MB each
checkpoint_timeout = 3600
checkpoint_warning = 300
effective_cache_size = 20000
random_page_cost = 2        # (same)
 
I really do not know how to find out what the query is waiting on, unlike oracle db provides some of the information through its dynamic performance views.
 
Please help in understanding how I can find out what the system is waiting for or why is it taking the query so long.
 
I will really appreciate some help.
 
Thanks
Abu

Sucker-punch spam with award-winning protection.
Try the free Yahoo! Mail Beta. --0-1797542960-1172758425=:13110--