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.

---------------------------------
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.