Thread: 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 ;
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)
-------------------------------------------------------------------------------------------------------------
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
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search. Learn more.
sarlav kumar wrote: > 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 followed) The expensive operation is the UNIQUE. Are you sure, in terms of business logic, that this is necessary? Is it actually possible to have duplicate transactions at the exact same time, and if so, would you really want to eliminate them? As an aside, I prefer to have numeric constants like the 'what' field in a small lookup table of two columns (what_code, what_description); it's easier to extend and to document.