Query Optimization - Mailing list pgsql-novice

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)
                         & nbsp; 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..4 2.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_i dx 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