Re: Query Slow in Postgres 8.4.3 than Postgres 8.1.5

On 3 Jun 2010, at 24:42, federalbird wrote:

>
> The following query is very slow in Postgres 8.4.3 as compared to Postgres
> 8.1.5. Please reply. Thanx in advance.....

Did you check the output of EXPLAIN ANALYSE to see if the plans are different between the two? Are your database
settingsidentical? 

To improve your chances on an answer, does the problem still occur if you strip out all the irrelevant crud (like all
thoseCASE's) from that query? I bet it does and it would make the query a whole lot more readable. 

I left the plain text version of your message below, as the HTML version was just one big blob of text and people may
notrealise the plain text version was less badly formatted: 

> select f.finance_company_name, b.brokerage_name, bc.quote_no as
> ContractNumber, cl.first_name as ClientFirstName, cl.last_name as
> ClientLastName, mcsh.status_type_cd as ContractStatus,
>     (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained
> * monthly_amt ELSE 0 END)) as due_amount,
>     (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained
> * monthly_amt ELSE 0 END)) as received_amount,
>     (gl.ds - gl.cs - gl.d + gl.c) as ledger,
>     (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as active_ledger,
>     (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as active_due_amount,
>     (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as active_received_amount,
>     (case when mcsh.status_type_cd = 'ACTIVE' THEN 1 else 0 end) as
> active_count,
>     (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as cancelled_ledger,
>     (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as cancelled_due_amount,
>     (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as cancelled_received_amount,
>     (case when mcsh.status_type_cd = 'CANCELLED' THEN 1 else 0 end) as
> cancelled_count,
>     (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as default_ledger,
>     (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as default_due_amount,
>     (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as default_received_amount,
>     (case when mcsh.status_type_cd = 'DEFAULTED' THEN 1 else 0 end) as
> default_count,
>     (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as payout_ledger,
>     (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as payout_due_amount,
>     (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as payout_received_amount,
>     (case when mcsh.status_type_cd = 'PAIDOUT' THEN 1 else 0 end) as
> payout_count,
>     (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as expired_ledger,
>     (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as expired_due_amount,
>     (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as expired_received_amount,
>     (case when mcsh.status_type_cd = 'EXPIRED' THEN 1 else 0 end) as
> expired_count,
>     (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as writeoff_ledger,
>     (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as writeoff_due_amount,
>     (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as writeoff_received_amount,
>     (case when mcsh.status_type_cd = 'WRITEOFF' THEN 1 else 0 end) as
> writeoff_count,
>     (case when mcsh.status_type_cd = 'RESCIND' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as rescind_ledger,
>     (case when mcsh.status_type_cd = 'RESCIND' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as rescind_due_amount,
>     (case when mcsh.status_type_cd = 'RESCIND' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as rescind_received_amount,
>     (case when mcsh.status_type_cd = 'RESCIND' THEN 1 else 0 end) as
> rescind_count
> from nq_finance_company f inner join nq_group g on (f.finance_company_id =
> g.group_id and g.group_id =3299)
> inner join nq_group ug on (g.left_index < ug.left_index and g.right_index >
> ug.right_index)
> inner join nq_brokerage b on (b.brokerage_id = ug.parent_id)
> inner join nq_base_contract bc on (bc.group_id = ug.group_id and
> bc.quote_type_cd = 'CONTRACT')
> inner join nq_client cl on (cl.client_id = bc.client_id)
> left outer join nq_retained_pmts rp on (bc.quote_id = rp.quote_id)
> inner join (select csh.quote_id, csh.status_type_cd from
> nq_contract_status_history csh where (csh.quote_id, csh.status_history_id)
> in (select quote_id, max(status_history_id) from nq_contract_status_history
> where DATE_TRUNC( 'DAY',entry_date) <= to_date('
> 06/02/2010', 'mm/dd/yyyy')  group by quote_id)) mcsh
> on (mcsh.quote_id = bc.quote_id and mcsh.status_type_cd in ('ACTIVE',
> 'CANCELLED', 'DEFAULTED', 'EXPIRED', 'PAIDOUT', 'RESCIND'))
> inner join
>     (select t.transaction_relation_id,
>       sum (case when (e.debit_id != 1100 and e.credit_id >= 2000 and
> e.credit_id < 3000) then amount else 0 end) as cs,
>       sum (case when (e.credit_id != 1100 and e.debit_id >= 2000 and
> e.debit_id < 3000 ) then amount else 0 end) as ds,
>       sum (case when (e.debit_id = 1100 and e.credit_id >= 2000 and
> e.credit_id < 3000 ) then amount else 0 end) as d,
>       sum (case when (e.credit_id = 1100 and e.debit_id >= 2000 and
> e.debit_id < 3000) then amount else 0 end) as c
> from nq_gl_account_entry e inner join nq_transaction t on (e.transaction_id
> = t.transaction_id)
>     where DATE_TRUNC( 'DAY',transaction_date) <= to_date('
> 06/02/2010', 'mm/dd/yyyy')   group by t.transaction_relation_id) gl on
> (gl.transaction_relation_id = bc.transaction_relation_id)
> order by f.finance_company_name, b.brokerage_name, bc.quote_no

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c07a96310151389715979!