Posgre view is performing very slow

[huumzz]

CREATE OR REPLACE VIEW vwstmACC AS 
 SELECT DISTINCT ON (stmnt.chargeservicelineid) stmnt.created, stmnt.createdby, stmnt.lastupdated, stmnt.updatedby, stmnt.accountid, stmnt.practiceid, stmnt.chargegroupid, stmnt.chargeservicelineid, stmnt.patientid, stmnt.paymentpostingid, stmnt.chargeadjustmentid, stmnt.locationid, stmnt.specialityid, stmnt.placeofserviceid, stmnt.billtopatientpayerid, stmnt.paymentid, stmnt.patientdisplayid, stmnt.dateofservice, stmnt.posteddate, stmnt.status, stmnt.voideddate, stmnt.originalclaimreferencenumber, stmnt.adjustmentreason, stmnt.adjdescription, stmnt.adjposteddate, stmnt.name, stmnt.patientaspayer, stmnt.patfirstname, stmnt.patmiddlename, stmnt.patlastname, stmnt.chartnum, stmnt.sex, stmnt.healthcardnumber, stmnt.dob, stmnt.units, stmnt.fee, stmnt.chargeamount, stmnt.allowedamount, stmnt.deductibleamount, stmnt.coinsuranceamount, stmnt.copaymentamount, stmnt.totalpayments, stmnt.totaladjustments, stmnt.balanceamount, stmnt.voidedchargelinkid, stmnt.roworder, stmnt.methodofpayment, stmnt.payment_posteddate, stmnt.claimnumber, stmnt.claimsubstatus, stmnt.onholdreason, stmnt.claimvoideddate, stmnt.servicecodename, stmnt.servicecodenamedesc, stmnt.locationname, stmnt.locationdescription, stmnt.payer, stmnt.payercode, stmnt.payerlastname, stmnt.payerfirstname
   FROM ( SELECT DISTINCT ON (csl.chargeservicelineid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid) cg.created, cg.createdby, cg.lastupdated, cg.updatedby, cg.accountid, cg.practiceid, cg.chargegroupid, csl.chargeservicelineid, cg.patientid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid, cg.locationid, cg.specialityid, cg.placeofserviceid, cg.billtopatientpayerid, pAYpOSTT.paymentid, pat_info.patientdisplayid, csl.dateofservicestart AS dateofservice, cg.posteddate, cg.status, cg.voideddate, cg.originalclaimreferencenumber, tva.adjustmentreason, tva.description AS adjdescription, tva.posteddate AS adjposteddate, practice.name, 
                CASE
                    WHEN cg.patientaspayer = true THEN 'Patient'::text
                    ELSE '3RD PARTY'::text
                END AS patientaspayer, pat_info.firstname AS patfirstname, pat_info.mi AS patmiddlename, pat_info.lastname AS patlastname, pat_info.chartnum, pat_info.sex, pat_info.healthcardnumber, pat_info.dob, csl.units, csl.fee, csl.chargeamount, csl.allowedamount, csl.deductibleamount, csl.coinsuranceamount, csl.copaymentamount, ( SELECT sum(p1.postedamount) AS sum
                   FROM pAYpOSTT p1
                  WHERE cg.accountid = p1.accountid AND cg.practiceid = p1.practiceid AND csl.chargeservicelineid = p1.chargeservicelineid AND p1.status::text = 'POSTED'::text) AS totalpayments, ( SELECT sum(a1.amount) AS sum
                   FROM vwadjustments a1
                  WHERE cg.accountid = a1.accountid AND cg.practiceid = a1.practiceid AND csl.chargeservicelineid = a1.chargeservicelineid) AS totaladjustments, csl.balanceamount, csl.voidedchargelinkid, csl.roworder, payments.methodofpayment, pAYpOSTT.posteddate AS payment_posteddate, claim.claimnumber, claim.substatus AS claimsubstatus, claim.onholdreason, claim.voideddate AS claimvoideddate, enterprise_servicecodes.codename AS servicecodename, enterprise_servicecodes.description AS servicecodenamedesc, pRACloc.locationname, pRACloc.description AS locationdescription, vwpatientpayers.payer, vwpatientpayers.payercode, vwpatientpayers.lastname AS payerlastname, vwpatientpayers.firstname AS payerfirstname
           FROM charge_group cg
      JOIN charge_service_line csl ON cg.accountid = csl.accountid AND cg.practiceid = csl.practiceid AND cg.chargegroupid = csl.chargegroupid AND cg.status::text = csl.status::text AND csl.substatus::text <> '2'::text AND cg.status::text = 'POSTED'::text
   LEFT JOIN enterprise_servicecodes ON enterprise_servicecodes.entservicecodeid = csl.entservicecodeid
   LEFT JOIN pAYpOSTT ON cg.accountid = pAYpOSTT.accountid AND cg.practiceid = pAYpOSTT.practiceid AND csl.chargeservicelineid = pAYpOSTT.chargeservicelineid AND pAYpOSTT.status::text = 'POSTED'::text
   LEFT JOIN payments ON cg.accountid = payments.accountid AND cg.practiceid = payments.practiceid AND payments.paymentid = pAYpOSTT.paymentid AND payments.status::text = 'POSTED'::text
   LEFT JOIN claim ON cg.accountid = claim.accountid AND cg.practiceid = claim.practiceid AND cg.chargegroupid = claim.chargegroupid
   JOIN pRACloc ON cg.accountid = pRACloc.accountid AND cg.practiceid = pRACloc.practiceid AND cg.locationid = pRACloc.locationid
   LEFT JOIN vwpatientpayers ON cg.accountid = vwpatientpayers.accountid AND cg.patientid = vwpatientpayers.patientid AND cg.billtopatientpayerid = vwpatientpayers.patientpayerid
   JOIN pat_info ON cg.accountid = pat_info.accountid AND cg.patientid = pat_info.patientid
   LEFT JOIN vwadjustments tva ON cg.accountid = tva.accountid AND cg.practiceid = tva.practiceid AND csl.chargeservicelineid = tva.chargeservicelineid
   JOIN practice ON cg.accountid = practice.accountid AND cg.practiceid = practice.practiceid
  WHERE (date(csl.posteddate) <> date(csl.voideddate) OR csl.voideddate IS NULL OR csl.substatus::text = '1'::text) AND cg.status::text = 'POSTED'::text
  ORDER BY csl.chargeservicelineid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid, cg.chargegroupid, cg.dateofservice, csl.roworder) stmnt
  ORDER BY stmnt.chargeservicelineid, stmnt.paymentpostingid, stmnt.chargeadjustmentid, stmnt.created DESC;
 
ALTER TABLE vwstmACC
  OWNER TO postgr;

[Rabbit]

Your code is formatted terribly. It makes it very hard to diagnose so I had to reformat it just to be able to understand it.

There's lots of reasons your code is going to run slow. Too many to try to go over at this very moment. Instead, read our article on optimizing SQL: http://bytes.com/topic/sql-server/in...ze-sql-queries

It was written for SQL Server but many of the same concepts apply to all SQL engines. The syntax will be slightly different but equivalents exist in all the major database management systems.

After applying the concepts in that article, please post the updated SQL for further help. And for Pete's sake, please format the SQL so it is easier to read.

SELECT DISTINCT ON (stmnt.chargeservicelineid) 
    stmnt.created, 
    stmnt.createdby, 
    stmnt.lastupdated, 
    stmnt.updatedby, 
    stmnt.accountid, 
    stmnt.practiceid, 
    stmnt.chargegroupid, 
    stmnt.chargeservicelineid, 
    stmnt.patientid, 
    stmnt.paymentpostingid, 
    stmnt.chargeadjustmentid, 
    stmnt.locationid, 
    stmnt.specialityid, 
    stmnt.placeofserviceid, 
    stmnt.billtopatientpayerid, 
    stmnt.paymentid, 
    stmnt.patientdisplayid, 
    stmnt.dateofservice, 
    stmnt.posteddate, 
    stmnt.status, 
    stmnt.voideddate, 
    stmnt.originalclaimreferencenumber, 
    stmnt.adjustmentreason, 
    stmnt.adjdescription, 
    stmnt.adjposteddate, 
    stmnt.name, 
    stmnt.patientaspayer, 
    stmnt.patfirstname, 
    stmnt.patmiddlename, 
    stmnt.patlastname, 
    stmnt.chartnum, 
    stmnt.sex, 
    stmnt.healthcardnumber, 
    stmnt.dob, 
    stmnt.units, 
    stmnt.fee, 
    stmnt.chargeamount, 
    stmnt.allowedamount, 
    stmnt.deductibleamount, 
    stmnt.coinsuranceamount, 
    stmnt.copaymentamount, 
    stmnt.totalpayments, 
    stmnt.totaladjustments, 
    stmnt.balanceamount, 
    stmnt.voidedchargelinkid, 
    stmnt.roworder, 
    stmnt.methodofpayment, 
    stmnt.payment_posteddate, 
    stmnt.claimnumber, 
    stmnt.claimsubstatus, 
    stmnt.onholdreason, 
    stmnt.claimvoideddate, 
    stmnt.servicecodename, 
    stmnt.servicecodenamedesc, 
    stmnt.locationname, 
    stmnt.locationdescription, 
    stmnt.payer, 
    stmnt.payercode, 
    stmnt.payerlastname, 
    stmnt.payerfirstname
 
FROM 
    (
        SELECT DISTINCT ON (csl.chargeservicelineid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid) 
            cg.created, 
            cg.createdby, 
            cg.lastupdated, 
            cg.updatedby, 
            cg.accountid, 
            cg.practiceid, 
            cg.chargegroupid, 
            csl.chargeservicelineid, 
            cg.patientid, 
            pAYpOSTT.paymentpostingid, 
            tva.chargeadjustmentid, 
            cg.locationid, 
            cg.specialityid, 
            cg.placeofserviceid, 
            cg.billtopatientpayerid, 
            pAYpOSTT.paymentid, 
            pat_info.patientdisplayid, 
            csl.dateofservicestart AS dateofservice, 
            cg.posteddate, 
            cg.status, 
            cg.voideddate, 
            cg.originalclaimreferencenumber, 
            tva.adjustmentreason, 
            tva.description AS adjdescription, 
            tva.posteddate AS adjposteddate, 
            practice.name,
 
            CASE
                WHEN cg.patientaspayer = true THEN 'Patient'::text
                ELSE '3RD PARTY'::text
            END AS patientaspayer, 
 
            pat_info.firstname AS patfirstname, 
            pat_info.mi AS patmiddlename, 
            pat_info.lastname AS patlastname, 
            pat_info.chartnum, 
            pat_info.sex, 
            pat_info.healthcardnumber, 
            pat_info.dob, 
            csl.units, 
            csl.fee, 
            csl.chargeamount, 
            csl.allowedamount, 
            csl.deductibleamount, 
            csl.coinsuranceamount, 
            csl.copaymentamount, 
 
            (
                SELECT sum(p1.postedamount) AS sum
                FROM pAYpOSTT p1
                WHERE 
                    cg.accountid = p1.accountid AND 
                    cg.practiceid = p1.practiceid AND 
                    csl.chargeservicelineid = p1.chargeservicelineid AND 
                    p1.status::text = 'POSTED'::text
            ) AS totalpayments, 
 
            (
                SELECT sum(a1.amount) AS sum
                FROM vwadjustments a1
                WHERE 
                    cg.accountid = a1.accountid AND 
                    cg.practiceid = a1.practiceid AND 
                    csl.chargeservicelineid = a1.chargeservicelineid
            ) AS totaladjustments, 
 
            csl.balanceamount, 
            csl.voidedchargelinkid, 
            csl.roworder, 
            payments.methodofpayment, 
            pAYpOSTT.posteddate AS payment_posteddate, 
            claim.claimnumber, 
            claim.substatus AS claimsubstatus, 
            claim.onholdreason, 
            claim.voideddate AS claimvoideddate, 
            enterprise_servicecodes.codename AS servicecodename, 
            enterprise_servicecodes.description AS servicecodenamedesc, 
            pRACloc.locationname, 
            pRACloc.description AS locationdescription, 
            vwpatientpayers.payer, 
            vwpatientpayers.payercode, 
            vwpatientpayers.lastname AS payerlastname, 
            vwpatientpayers.firstname AS payerfirstname
 
        FROM 
            charge_group cg
 
            JOIN charge_service_line csl ON 
                cg.accountid = csl.accountid AND 
                cg.practiceid = csl.practiceid AND 
                cg.chargegroupid = csl.chargegroupid AND 
                cg.status::text = csl.status::text AND 
                csl.substatus::text <> '2'::text AND 
                cg.status::text = 'POSTED'::text
 
            LEFT JOIN enterprise_servicecodes ON 
                enterprise_servicecodes.entservicecodeid = csl.entservicecodeid
 
            LEFT JOIN pAYpOSTT ON 
                cg.accountid = pAYpOSTT.accountid AND 
                cg.practiceid = pAYpOSTT.practiceid AND 
                csl.chargeservicelineid = pAYpOSTT.chargeservicelineid AND 
                pAYpOSTT.status::text = 'POSTED'::text
 
            LEFT JOIN payments ON 
                cg.accountid = payments.accountid AND 
                cg.practiceid = payments.practiceid AND 
                payments.paymentid = pAYpOSTT.paymentid AND 
                payments.status::text = 'POSTED'::text
 
            LEFT JOIN claim ON 
                cg.accountid = claim.accountid AND 
                cg.practiceid = claim.practiceid AND 
                cg.chargegroupid = claim.chargegroupid
 
            JOIN pRACloc ON 
                cg.accountid = pRACloc.accountid AND 
                cg.practiceid = pRACloc.practiceid AND 
                cg.locationid = pRACloc.locationid
 
            LEFT JOIN vwpatientpayers ON 
                cg.accountid = vwpatientpayers.accountid AND 
                cg.patientid = vwpatientpayers.patientid AND 
                cg.billtopatientpayerid = vwpatientpayers.patientpayerid
 
            JOIN pat_info ON 
                cg.accountid = pat_info.accountid AND 
                cg.patientid = pat_info.patientid
 
            LEFT JOIN vwadjustments tva ON 
                cg.accountid = tva.accountid AND 
                cg.practiceid = tva.practiceid AND 
                csl.chargeservicelineid = tva.chargeservicelineid
 
            JOIN practice ON
                cg.accountid = practice.accountid AND
                cg.practiceid = practice.practiceid
 
        WHERE 
            (
                date(csl.posteddate) <> date(csl.voideddate) OR 
                csl.voideddate IS NULL OR 
                csl.substatus::text = '1'::text
            ) AND 
            cg.status::text = 'POSTED'::text
 
        ORDER BY 
            csl.chargeservicelineid, 
            pAYpOSTT.paymentpostingid, 
            tva.chargeadjustmentid, 
            cg.chargegroupid, 
            cg.dateofservice, 
            csl.roworder
    ) stmnt
 
ORDER BY 
    stmnt.chargeservicelineid, 
    stmnt.paymentpostingid, 
    stmnt.chargeadjustmentid, 
    stmnt.created DESC;