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.
