Thread: Select taking excessively long; Request help streamlining.

If this message has already appeared on the list, I apologize.  My system tried to temporarily freeze up when I attempted to send this message a few minutes ago, and I do not know if I hit send before it halted or not.

 

I am working with a php program that is designed to enter the database, execute a rather convoluted select (statement seeks out all records in a specific table that match the input criteria, then flows through the table links [x.foo = y.bar] to gather all data related to the records it is looking for), then display the results as a web page.

 

I admit that the primary table the select statement looks at has a large number of records (~ 6 million) in it, but I still don't think it should take as long to accomplish this task as it does.  I suspect that the real problem lies in the way I built the select statement, that it is somehow clunky and unwieldy.

 

A copy of the statement and explain results on it appear below.  Would someone please assist me in figuring out how to more appropriately streamline this statement?

 

 

attest=# EXPLAIN select substring(ttrans.tran_dt, 1, 10) as tran_dt, ttrans.dist_id as dist_id, ttrans.cntrct_id as cntrct_id, cntrt.cntrtyp_cd as cntrt_type, cntrt.actual_amt as cntrt_amt, acntrec.mth_reck as mth_reck, persn.frst_nm as fnm, persn.lst_nm as lnm from ttrans, cntrt, acntrec, persn, custm, addru where ttrans.tran_dt >= '2007-03-01' and ttrans.tran_dt < '2007-03-31' and ttrans.cntrct_id = cntrt.cntrct_id and cntrt.cntrct_seq = addru.cntrct_seq and addru.aunit_seq = acntrec.aunit_seq and (cntrt.cntrtyp_cd = 255 or cntrt.cntrtyp_cd = 260) and cntrt.clnt_seq = custm.clnt_seq and custm.person_seq = persn.person_seq and acntrec.cd_inst = 49 and acntrec.months = 49 and cntrt.dow_flg1 = 'NO' order by ttrans.dist_id asc, cntrt.cntrtyp_cd asc, cntrt.cntrct_id asc, cntrt.cntrct_id asc;
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=183688.49..183688.50 rows=1 width=125)
   Sort Key: ttrans.dist_id, cntrt.cntrtyp_cd, cntrt.cntrct_id
   ->  Nested Loop  (cost=0.00..183688.48 rows=1 width=125)
         ->  Nested Loop  (cost=0.00..183683.87 rows=1 width=106)
               Join Filter: (("inner".cntrct_id)::bpchar = "outer".cntrct_id)
               ->  Nested Loop  (cost=0.00..21820.21 rows=1 width=48)
                     ->  Nested Loop  (cost=0.00..21815.45 rows=1 width=48)
                           ->  Nested Loop  (cost=0.00..21793.06 rows=4 width=43)
                                 ->  Seq Scan on cntrt  (cost=0.00..21771.81 rows=4 width=43)
                                       Filter: ((((cntrtyp_cd)::text = '255'::text) OR ((cntrtyp_cd)::text = '260'::text)) AND (dow_flg1 = 'NO'::bpchar))
                                 ->  Index Scan using fk_cntrct on addru  (cost=0.00..5.30 rows=1 width=8)
                                       Index Cond: ("outer".cntrct_seq = addru.cntrct_seq)
                           ->  Index Scan using fk_aunit on acntrec  (cost=0.00..5.59 rows=1 width=13)
                                 Index Cond: ("outer".aunit_seq = acntrec.aunit_seq)
                                 Filter: ((cd_inst = 49) AND ((months)::text = '49'::text))
                     ->  Index Scan using "pkeyCUSTM" on custm  (cost=0.00..4.75 rows=1 width=8)
                           Index Cond: ("outer".clnt_seq = custm.clnt_seq)
               ->  Seq Scan on ttrans  (cost=0.00..161492.77 rows=29671 width=58)
                     Filter: ((tran_dt >= '2007-03-01 00:00:00-06'::timestamp with time zone) AND (tran_dt < '2007-03-31 00:00:00-05'::timestamp with time zone))
         ->  Index Scan using "pkeyPERSN" on persn  (cost=0.00..4.59 rows=1 width=27)
               Index Cond: ("outer".person_seq = persn.person_seq)
(21 rows)

 

Thank you for your consideration.