Query Optimization

Hi all,

Can someone please help me optimize this query? Is there a better way to write this query? I am generating a report of transactions ordered by time and with details of the sender and receiver etc.

SELECT distinct a.time::date ||'<br>'||substring(a.time::time::text,1,8) as Time,
CASE WHEN a.what = 0 THEN 'Money Transfer' WHEN a.what = 15 THEN 'Purchase' WHEN a.what = 26 THEN 'Merchant Streamline' WHEN a.what = 13 THEN 'Reversal' END as Transaction_Type ,
c1.account_no as SenderAccount, c2.account_no as RecieverAccount,
b.country as SenderCountry, d.country as RecieverCountry,
b.firstname as SenderFirstName, b.lastname as SenderLastName,
d.firstname as ReceiverFirstName, d.lastname as ReceiverLastName,
a.status as status,
(select sum(td.amount * 0.01) from transaction_data td where td.data_id = a2.id and td.dir = 1 and td.uid = a.target_uid) as ReversedAmount,
(select sum(td.amount * 0.01) from transaction_data td where td.data_id = a2.id and td.dir = 0 and td.uid = a.uid ) as DepositedAmount, a.flags, (a.amount * 0.01) as Amount,
(a.fee * 0.01) as Fee
FROM data a, customerdata b, customerdata d, customer c1, customer c2, participant p, data a2
WHERE p.id = a.partner_id AND (a.uid = c1.id) AND (a.target_uid = c2.id) and c1.id=b.uid and c2.id=d.uid
and a.confirmation is not null AND (a2.ref_id = a.id) and
((a2.what = 13) or (a2.what = 17) ) ORDER BY time desc ;


QUERY PLAN

-------------------------------------------------------------------------------------------------------------
Unique (cost=2978.27..2981.54 rows=8 width=150) (actual time=502.29..506.75 rows=382 loops=1)
-> Sort (cost=2978.27..2978.46 rows=77 width=150) (actual time=502.29..502.61 rows=461 loops=1)
Sort Key: ((((a."time")::date)::text || '<br>'::text) || "substring"(((a."time")::time without time zone)::text, 1, 8)), CASE WHEN (a
.what = 0) THEN 'Money Transfer'::text WHEN (a.what = 15) THEN 'Purchase'::text WHEN (a.what = 26) THEN 'Merchant Streamline'::text WHEN (a.wh
at = 13) THEN 'Reversal'::text ELSE NULL::text END, c1.account_no, c2.account_no, b.country, d.country, b.firstname, b.lastname, d.firstname,
d.lastname, a.status, (subplan), (subplan), a.flags, ((a.amount)::numeric * 0.01), ((a.fee)::numeric * 0.01)
-> Hash Join (cost=2687.00..2975.86 rows=77 width=150) (actual time=423.91..493.48 rows=461 loops=1)
Hash Cond: ("outer".partner_id = "inner".id)
-> Nested Loop (cost=2494.67..2781.99 rows=77 width=146) (actual time=413.19..441.61 rows=472 loops=1)
-> Merge Join (cost=2494.67..2526.04 rows=77 width=116) (actual time=413.09..429.86 rows=472 loops=1)
Merge Cond: ("outer".id = "inner".ref_id)
-> Sort (cost=1443.39..1458.57 rows=6069 width=108) (actual time=370.14..377.72 rows=5604 loops=1)
Sort Key: a.id
-> Hash Join (cost=203.50..1062.01 rows=6069 width=108) (actual time=20.35..335.44 rows=5604 loops=1)
Hash Cond: ("outer".uid = "inner".id)
-> Merge Join (cost=0.00..676.43 rows=6069 width=91) (actual time=0.42..255.33 rows=5611 loops=1)
Merge Cond: ("outer".target_uid = "inner".uid)
-> Merge Join (cost=0.00..1224.05 rows=6069 width=61) (actual time=0.34..156.74 rows=5611 loops
=1)
Merge Cond: ("outer".target_uid = "inner".id)
-> Index Scan using data_target_uid on data a (cost=0.00..2263.05 rows=6069 width=44) (ac
tual time=0.23..63.87 rows=5630 loops=1)
Filter: (confirmation IS NOT NULL)
-> Index Scan using customer_pkey on customer c2 (cost=0.00..631.03 rows=6120 width=17) (
actual time=0.05..50.97 rows=10862 loops=1)
-> Index Scan using customerdata_uid_idx on customerdata d (cost=0.00..312.36 rows=6085 width=3
0) (actual time=0.06..48.95 rows=10822 loops=1)
-> Hash (cost=188.20..188.20 rows=6120 width=17) (actual time=19.81..19.81 rows=0 loops=1)
-> Seq Scan on customer c1 (cost=0.00..188.20 rows=6120 width=17) (actual time=0.03..12.30 rows
=6157 loops=1)
-> Sort (cost=1051.28..1052.52 rows=497 width=8) (actual time=42.05..42.51 rows=542 loops=1)
Sort Key: a2.ref_id
-> Seq Scan on data a2 (cost=0.00..1029.00 rows=497 width=8) (actual time=0.21..41.14 rows=545 loops=1)
Filter: ((what = 13) OR (what = 17))
-> Index Scan using customerdata_uid_idx on customerdata b (cost=0.00..3.31 rows=1 width=30) (actual time=0.01..0.01 ro
ws=1 loops=472)
Index Cond: (b.uid = "outer".uid)
-> Hash (cost=192.26..192.26 rows=26 width=4) (actual time=10.50..10.50 rows=0 loops=1)
-> Seq Scan on participant p (cost=0.00..192.26 rows=26 width=4) (actual time=10.42..10.46 rows=26 loops=1)
SubPlan
-> Aggregate (cost=6.08..6.08 rows=1 width=4) (actual time=0.03..0.03 rows=1 loops=461)
-> Index Scan using td_data_id_idx on transaction_data td (cost=0.00..6.08 rows=1 width=4) (actual time=0.02..0.02 ro
ws=1 loops=461)
Index Cond: (data_id = $0)
Filter: ((dir = 1) AND (uid = $1))
-> Aggregate (cost=6.08..6.08 rows=1 width=4) (actual time=0.02..0.02 rows=1 loops=461)
-> Index Scan using td_data_id_idx on transaction_data td (cost=0.00..6.08 rows=1 width=4) (actual time=0.01..0.01 ro
ws=1 loops=461)
Index Cond: (data_id = $0)
Filter: ((dir = 0) AND (uid = $2))
Total runtime: 508.27 msec
(40 rows)
Time: 528.13 ms

Please help me out.
Thanks in advance!
Saranya

---------------------------------
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search. Learn more.