By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 860 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Posgre view is performing very slow

P: 1
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE VIEW vwstmACC AS 
  2.  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
  3.    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, 
  4.                 CASE
  5.                     WHEN cg.patientaspayer = true THEN 'Patient'::text
  6.                     ELSE '3RD PARTY'::text
  7.                 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
  8.                    FROM pAYpOSTT p1
  9.                   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
  10.                    FROM vwadjustments a1
  11.                   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
  12.            FROM charge_group cg
  13.       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
  14.    LEFT JOIN enterprise_servicecodes ON enterprise_servicecodes.entservicecodeid = csl.entservicecodeid
  15.    LEFT JOIN pAYpOSTT ON cg.accountid = pAYpOSTT.accountid AND cg.practiceid = pAYpOSTT.practiceid AND csl.chargeservicelineid = pAYpOSTT.chargeservicelineid AND pAYpOSTT.status::text = 'POSTED'::text
  16.    LEFT JOIN payments ON cg.accountid = payments.accountid AND cg.practiceid = payments.practiceid AND payments.paymentid = pAYpOSTT.paymentid AND payments.status::text = 'POSTED'::text
  17.    LEFT JOIN claim ON cg.accountid = claim.accountid AND cg.practiceid = claim.practiceid AND cg.chargegroupid = claim.chargegroupid
  18.    JOIN pRACloc ON cg.accountid = pRACloc.accountid AND cg.practiceid = pRACloc.practiceid AND cg.locationid = pRACloc.locationid
  19.    LEFT JOIN vwpatientpayers ON cg.accountid = vwpatientpayers.accountid AND cg.patientid = vwpatientpayers.patientid AND cg.billtopatientpayerid = vwpatientpayers.patientpayerid
  20.    JOIN pat_info ON cg.accountid = pat_info.accountid AND cg.patientid = pat_info.patientid
  21.    LEFT JOIN vwadjustments tva ON cg.accountid = tva.accountid AND cg.practiceid = tva.practiceid AND csl.chargeservicelineid = tva.chargeservicelineid
  22.    JOIN practice ON cg.accountid = practice.accountid AND cg.practiceid = practice.practiceid
  23.   WHERE (date(csl.posteddate) <> date(csl.voideddate) OR csl.voideddate IS NULL OR csl.substatus::text = '1'::text) AND cg.status::text = 'POSTED'::text
  24.   ORDER BY csl.chargeservicelineid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid, cg.chargegroupid, cg.dateofservice, csl.roworder) stmnt
  25.   ORDER BY stmnt.chargeservicelineid, stmnt.paymentpostingid, stmnt.chargeadjustmentid, stmnt.created DESC;
  26.  
  27. ALTER TABLE vwstmACC
  28.   OWNER TO postgr;
Aug 12 '15 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,347
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT ON (stmnt.chargeservicelineid) 
  2.     stmnt.created, 
  3.     stmnt.createdby, 
  4.     stmnt.lastupdated, 
  5.     stmnt.updatedby, 
  6.     stmnt.accountid, 
  7.     stmnt.practiceid, 
  8.     stmnt.chargegroupid, 
  9.     stmnt.chargeservicelineid, 
  10.     stmnt.patientid, 
  11.     stmnt.paymentpostingid, 
  12.     stmnt.chargeadjustmentid, 
  13.     stmnt.locationid, 
  14.     stmnt.specialityid, 
  15.     stmnt.placeofserviceid, 
  16.     stmnt.billtopatientpayerid, 
  17.     stmnt.paymentid, 
  18.     stmnt.patientdisplayid, 
  19.     stmnt.dateofservice, 
  20.     stmnt.posteddate, 
  21.     stmnt.status, 
  22.     stmnt.voideddate, 
  23.     stmnt.originalclaimreferencenumber, 
  24.     stmnt.adjustmentreason, 
  25.     stmnt.adjdescription, 
  26.     stmnt.adjposteddate, 
  27.     stmnt.name, 
  28.     stmnt.patientaspayer, 
  29.     stmnt.patfirstname, 
  30.     stmnt.patmiddlename, 
  31.     stmnt.patlastname, 
  32.     stmnt.chartnum, 
  33.     stmnt.sex, 
  34.     stmnt.healthcardnumber, 
  35.     stmnt.dob, 
  36.     stmnt.units, 
  37.     stmnt.fee, 
  38.     stmnt.chargeamount, 
  39.     stmnt.allowedamount, 
  40.     stmnt.deductibleamount, 
  41.     stmnt.coinsuranceamount, 
  42.     stmnt.copaymentamount, 
  43.     stmnt.totalpayments, 
  44.     stmnt.totaladjustments, 
  45.     stmnt.balanceamount, 
  46.     stmnt.voidedchargelinkid, 
  47.     stmnt.roworder, 
  48.     stmnt.methodofpayment, 
  49.     stmnt.payment_posteddate, 
  50.     stmnt.claimnumber, 
  51.     stmnt.claimsubstatus, 
  52.     stmnt.onholdreason, 
  53.     stmnt.claimvoideddate, 
  54.     stmnt.servicecodename, 
  55.     stmnt.servicecodenamedesc, 
  56.     stmnt.locationname, 
  57.     stmnt.locationdescription, 
  58.     stmnt.payer, 
  59.     stmnt.payercode, 
  60.     stmnt.payerlastname, 
  61.     stmnt.payerfirstname
  62.  
  63. FROM 
  64.     (
  65.         SELECT DISTINCT ON (csl.chargeservicelineid, pAYpOSTT.paymentpostingid, tva.chargeadjustmentid) 
  66.             cg.created, 
  67.             cg.createdby, 
  68.             cg.lastupdated, 
  69.             cg.updatedby, 
  70.             cg.accountid, 
  71.             cg.practiceid, 
  72.             cg.chargegroupid, 
  73.             csl.chargeservicelineid, 
  74.             cg.patientid, 
  75.             pAYpOSTT.paymentpostingid, 
  76.             tva.chargeadjustmentid, 
  77.             cg.locationid, 
  78.             cg.specialityid, 
  79.             cg.placeofserviceid, 
  80.             cg.billtopatientpayerid, 
  81.             pAYpOSTT.paymentid, 
  82.             pat_info.patientdisplayid, 
  83.             csl.dateofservicestart AS dateofservice, 
  84.             cg.posteddate, 
  85.             cg.status, 
  86.             cg.voideddate, 
  87.             cg.originalclaimreferencenumber, 
  88.             tva.adjustmentreason, 
  89.             tva.description AS adjdescription, 
  90.             tva.posteddate AS adjposteddate, 
  91.             practice.name,
  92.  
  93.             CASE
  94.                 WHEN cg.patientaspayer = true THEN 'Patient'::text
  95.                 ELSE '3RD PARTY'::text
  96.             END AS patientaspayer, 
  97.  
  98.             pat_info.firstname AS patfirstname, 
  99.             pat_info.mi AS patmiddlename, 
  100.             pat_info.lastname AS patlastname, 
  101.             pat_info.chartnum, 
  102.             pat_info.sex, 
  103.             pat_info.healthcardnumber, 
  104.             pat_info.dob, 
  105.             csl.units, 
  106.             csl.fee, 
  107.             csl.chargeamount, 
  108.             csl.allowedamount, 
  109.             csl.deductibleamount, 
  110.             csl.coinsuranceamount, 
  111.             csl.copaymentamount, 
  112.  
  113.             (
  114.                 SELECT sum(p1.postedamount) AS sum
  115.                 FROM pAYpOSTT p1
  116.                 WHERE 
  117.                     cg.accountid = p1.accountid AND 
  118.                     cg.practiceid = p1.practiceid AND 
  119.                     csl.chargeservicelineid = p1.chargeservicelineid AND 
  120.                     p1.status::text = 'POSTED'::text
  121.             ) AS totalpayments, 
  122.  
  123.             (
  124.                 SELECT sum(a1.amount) AS sum
  125.                 FROM vwadjustments a1
  126.                 WHERE 
  127.                     cg.accountid = a1.accountid AND 
  128.                     cg.practiceid = a1.practiceid AND 
  129.                     csl.chargeservicelineid = a1.chargeservicelineid
  130.             ) AS totaladjustments, 
  131.  
  132.             csl.balanceamount, 
  133.             csl.voidedchargelinkid, 
  134.             csl.roworder, 
  135.             payments.methodofpayment, 
  136.             pAYpOSTT.posteddate AS payment_posteddate, 
  137.             claim.claimnumber, 
  138.             claim.substatus AS claimsubstatus, 
  139.             claim.onholdreason, 
  140.             claim.voideddate AS claimvoideddate, 
  141.             enterprise_servicecodes.codename AS servicecodename, 
  142.             enterprise_servicecodes.description AS servicecodenamedesc, 
  143.             pRACloc.locationname, 
  144.             pRACloc.description AS locationdescription, 
  145.             vwpatientpayers.payer, 
  146.             vwpatientpayers.payercode, 
  147.             vwpatientpayers.lastname AS payerlastname, 
  148.             vwpatientpayers.firstname AS payerfirstname
  149.  
  150.         FROM 
  151.             charge_group cg
  152.  
  153.             JOIN charge_service_line csl ON 
  154.                 cg.accountid = csl.accountid AND 
  155.                 cg.practiceid = csl.practiceid AND 
  156.                 cg.chargegroupid = csl.chargegroupid AND 
  157.                 cg.status::text = csl.status::text AND 
  158.                 csl.substatus::text <> '2'::text AND 
  159.                 cg.status::text = 'POSTED'::text
  160.  
  161.             LEFT JOIN enterprise_servicecodes ON 
  162.                 enterprise_servicecodes.entservicecodeid = csl.entservicecodeid
  163.  
  164.             LEFT JOIN pAYpOSTT ON 
  165.                 cg.accountid = pAYpOSTT.accountid AND 
  166.                 cg.practiceid = pAYpOSTT.practiceid AND 
  167.                 csl.chargeservicelineid = pAYpOSTT.chargeservicelineid AND 
  168.                 pAYpOSTT.status::text = 'POSTED'::text
  169.  
  170.             LEFT JOIN payments ON 
  171.                 cg.accountid = payments.accountid AND 
  172.                 cg.practiceid = payments.practiceid AND 
  173.                 payments.paymentid = pAYpOSTT.paymentid AND 
  174.                 payments.status::text = 'POSTED'::text
  175.  
  176.             LEFT JOIN claim ON 
  177.                 cg.accountid = claim.accountid AND 
  178.                 cg.practiceid = claim.practiceid AND 
  179.                 cg.chargegroupid = claim.chargegroupid
  180.  
  181.             JOIN pRACloc ON 
  182.                 cg.accountid = pRACloc.accountid AND 
  183.                 cg.practiceid = pRACloc.practiceid AND 
  184.                 cg.locationid = pRACloc.locationid
  185.  
  186.             LEFT JOIN vwpatientpayers ON 
  187.                 cg.accountid = vwpatientpayers.accountid AND 
  188.                 cg.patientid = vwpatientpayers.patientid AND 
  189.                 cg.billtopatientpayerid = vwpatientpayers.patientpayerid
  190.  
  191.             JOIN pat_info ON 
  192.                 cg.accountid = pat_info.accountid AND 
  193.                 cg.patientid = pat_info.patientid
  194.  
  195.             LEFT JOIN vwadjustments tva ON 
  196.                 cg.accountid = tva.accountid AND 
  197.                 cg.practiceid = tva.practiceid AND 
  198.                 csl.chargeservicelineid = tva.chargeservicelineid
  199.  
  200.             JOIN practice ON
  201.                 cg.accountid = practice.accountid AND
  202.                 cg.practiceid = practice.practiceid
  203.  
  204.         WHERE 
  205.             (
  206.                 date(csl.posteddate) <> date(csl.voideddate) OR 
  207.                 csl.voideddate IS NULL OR 
  208.                 csl.substatus::text = '1'::text
  209.             ) AND 
  210.             cg.status::text = 'POSTED'::text
  211.  
  212.         ORDER BY 
  213.             csl.chargeservicelineid, 
  214.             pAYpOSTT.paymentpostingid, 
  215.             tva.chargeadjustmentid, 
  216.             cg.chargegroupid, 
  217.             cg.dateofservice, 
  218.             csl.roworder
  219.     ) stmnt
  220.  
  221. ORDER BY 
  222.     stmnt.chargeservicelineid, 
  223.     stmnt.paymentpostingid, 
  224.     stmnt.chargeadjustmentid, 
  225.     stmnt.created DESC;
Aug 12 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.