From: Andrew Lazarus <andrew@pillette.com>
To: sarlav kumar <sarlavk@yahoo.com>
Cc: pgsqlperform <pgsql-performance@postgresql.org>
Cc: pgsql-novice@postgresql.org
Subject: Re: [PERFORM] Query Optimization
Date: Wed, 15 Dec 2004 10:05:49 -0800
Message-ID: <41C07CFD.4020703@pillette.com> (raw)
In-Reply-To: <20041214213407.84233.qmail@web51307.mail.yahoo.com>
References: <20041214213407.84233.qmail@web51307.mail.yahoo.com>
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.
Attachments:andrew.vcf (text/x-vcard, 296B) download
begin:vcard
fn:Andrew Lazarus
n:Lazarus;Andrew
org:Pillette Investment Management;Research and Development
adr;dom:;;3028 Fillmore;San Francisco;CA;94123
email;internet:andrew@pillette.com
title:Director
tel;work:800-366-0688
tel;fax:415-440-4093
url:http://www.pillette.com
version:2.1
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@pillette.com, sarlavk@yahoo.com, pgsql-novice@postgresql.org
Subject: Re: [PERFORM] Query Optimization
In-Reply-To: <41C07CFD.4020703@pillette.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox