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:

Previous
From: Dimitri Fontaine
Date:
Subject: Re: libreadline and Debian 5 - not missing just badly named
Next
From: Alban Hertroys
Date:
Subject: Re: Query Slow in Postgres 8.4.3 than Postgres 8.1.5