Re: Query Slow in Postgres 8.4.3 than Postgres 8.1.5 - Mailing list pgsql-general
| From | Thom Brown |
|---|---|
| Subject | Re: Query Slow in Postgres 8.4.3 than Postgres 8.1.5 |
| Date | |
| Msg-id | AANLkTinqwPxJq6d4TFBggfLhv7XYuBB__cE266909Wh2@mail.gmail.com Whole thread Raw |
| In response to | Query Slow in Postgres 8.4.3 than Postgres 8.1.5 (federalbird <federalbird@hotmail.com>) |
| List | pgsql-general |
Thought I'd reformat your query for readability:
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
pgsql-general by date: