Please need help - Mailing list pgsql-novice

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