Query Slow in Postgres 8.4.3 than Postgres 8.1.5

The following query is very slow in Postgres 8.4.3 as compared to Postgres 8.1.5. Please reply. Thanx in advance..... 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

---

View this message in context: Query Slow in Postgres 8.4.3 than Postgres 8.1.5
Sent from the PostgreSQL - general mailing list archive at Nabble.com.