PgHero
No long running queries
Connections healthy 16
Vacuuming healthy
No columns near integer overflow
No invalid indexes or constraints
No duplicate indexes
No suggested indexes
37 slow queries

Slow Queries

Slow queries take 20 ms or more on average and have been called at least 100 times.

Explain queries to see where to add indexes.

Total Time Average Time Calls
567 min 4% 26 ms 1,308,773 cims2 · details 
SELECT     "lab_test_requests".* FROM       "lab_test_requests"  join lab_test_request_details as ltrd on lab_test_requests.id = ltrd.lab_test_request_id join lab_test_results as ltre on ltre.lab_test_request_detail_id = ltrd.id join service_requests as sr on sr.id = lab_test_requests.service_request_id join service_request_details as srd on srd.service_request_id= sr.id WHERE     (lab_test_requests.patient_id=$1 and srd.service_id in ($2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21) 
    and ltre.result_value ilike $22 )
377 min 3% 21 ms 1,091,361 erps1 · details 
UPDATE "extids" SET "current_value" = $1, "lock_version" = $2, "updated_at" = $3  WHERE (("extids"."id" = $4 AND "extids"."lock_version" = $5))
308 min 2% 46 ms 398,754 cims1 · details 
SELECT     sr.id as Request_id ,sr.request_date, sr.requested_by_id, sg.name as Service_group,s.name ,srd.billing_status,ltrd.id as ltrdid,inv.id as invid,ltrd.status as Lab_Status,inv.status As Imaging_Status,srd.id as ser_req_lab FROM       "service_requests"  as sr join service_request_details as srd on sr.id = srd.service_request_id
                               join services as s on srd.service_id = s.id
                              join service_groups as sg on s.service_group_id =sg.id
                               left join lab_test_request_details as ltrd on srd.id = ltrd.service_request_detail_id
                               left outer join investigation_request_details as inv on srd.id = inv.service_request_detail_id WHERE     (sg.clinical_system_id in ($1) and sr.Patient_id =$2) ORDER BY  sr.id desc
282 min 2% 303 ms 55,915 erps1 · details 
SELECT     SUM("bill_details"."quantity") AS sum_id FROM       "bill_details"  WHERE     (reference_1 = $1 and narration ilike $2)
282 min 2% 36 ms 471,894 cims1 · details 
SELECT     "bills".* FROM       "bills"  WHERE     ("bills"."service_request_id" = $1) LIMIT $2
155 min 1% 40 ms 232,994 erps2 · details 
UPDATE "extids" SET "current_value" = $1, "lock_version" = $2, "updated_at" = $3  WHERE (("extids"."id" = $4 AND "extids"."lock_version" = $5))
134 min 1.0% 1,263 ms 6,376 pghero · details 
SELECT n.nspname AS table_schema, c.relname AS table, attname AS column, format_type(a.atttypid, a.atttypmod) AS column_type, pg_get_expr(d.adbin, d.adrelid) AS default_value FROM pg_catalog.pg_attribute a INNER JOIN pg_catalog.pg_class c ON c.oid = a.attrelid INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum) WHERE NOT a.attisdropped AND a.attnum > $1 AND pg_get_expr(d.adbin, d.adrelid) LIKE $2 AND n.nspname NOT LIKE $3 /*pghero*/
115 min 0.8% 20 ms 340,901 erps1 · details 
SELECT     COUNT(*) AS count_id FROM       "appointments"  INNER JOIN "appointment_details" ON "appointments".id = "appointment_details".appointment_id WHERE     (("appointment_details".schedulable_entity_id = $1)) AND (((app_start_date_time >= $2 and app_start_date_time < $3) or (app_end_date_time >= $4 and app_end_date_time < $5))) AND (status = $6)
105 min 0.8% 38 ms 166,081 cims1 · details 
SELECT     COUNT(lts.id) AS count_id FROM       "lab_test_requests"  as ltr inner join lab_test_request_details as ltrd on ltr.id = ltrd.lab_test_request_id inner join lab_test_results as lts on lts.lab_test_request_detail_id = ltrd.id WHERE     (ltr.patient_id =$1  and lts.service_unit_id=$2 and ltrd.created_at <$3 and ltrd.id !=$4 and ltrd.status=$5)
93 min 0.7% 224 ms 24,790 erps1 · details 
SELECT     distinct(bd.*) FROM       "bill_details"  as bd inner join bills as b on b.id=bd.bill_id inner join itemwise_corporate_coverages as iwcc on iwcc.corporate_id = b.corporate_id WHERE     (iwcc.corporate_id=$1 and bd.bill_id=$2 and bd.patient_share > $3)
92 min 0.7% 41 ms 132,918 cims1 · details 
SELECT     sr.id as Request_id ,sr.request_date, sr.requested_by_id, sg.name as Service_group,s.name,srd.id as request_detail_id,srd.billing_status,ltrd.id as ltrdid,inv.id as invid,ltrd.status as Lab_Status,inv.status As Imaging_Status,srd.id as inv_serv_req_det FROM       "service_requests"  as sr join service_request_details as srd on sr.id = srd.service_request_id
                               join services as s on srd.service_id = s.id
                              join service_groups as sg on s.service_group_id =sg.id
                               left join lab_test_request_details as ltrd on srd.id = ltrd.service_request_detail_id
                               left outer join investigation_request_details as inv on srd.id = inv.service_request_detail_id WHERE     (sg.clinical_system_id in ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) and sr.Patient_id =$11) ORDER BY  sr.id desc
92 min 0.7% 347 ms 15,871 erps2 · details 
SELECT     SUM("bill_details"."quantity") AS sum_id FROM       "bill_details"  WHERE     (reference_1 = $1 and narration ilike $2)
81 min 0.6% 20 ms 239,306 erps1 · details 
SELECT     "item_reorder_levels".* FROM       "item_reorder_levels"  WHERE     (item_id=$1 and inventory_head_id=$2) LIMIT $3
78 min 0.6% 20 ms 232,972 cims1 · details 
SELECT COUNT(*) AS count_id FROM (SELECT $1 FROM "service_requests"  WHERE     ("service_requests".patient_id = $2)) AS subquery
72 min 0.5% 47 ms 92,055 cims2 · details 
SELECT     sr.id as Request_id ,sr.request_date, sr.requested_by_id, sg.name as Service_group,s.name ,srd.billing_status,ltrd.id as ltrdid,inv.id as invid,ltrd.status as Lab_Status,inv.status As Imaging_Status,srd.id as ser_req_lab FROM       "service_requests"  as sr join service_request_details as srd on sr.id = srd.service_request_id
                               join services as s on srd.service_id = s.id
                              join service_groups as sg on s.service_group_id =sg.id
                               left join lab_test_request_details as ltrd on srd.id = ltrd.service_request_detail_id
                               left outer join investigation_request_details as inv on srd.id = inv.service_request_detail_id WHERE     (sg.clinical_system_id in ($1) and sr.Patient_id =$2) ORDER BY  sr.id desc
70 min 0.5% 159 ms 26,346 cims1 · details 
SELECT     "icd10_codes".* FROM       "icd10_codes"  WHERE     (icd_name ilike $1) ORDER BY  icd_name LIMIT $2
67 min 0.5% 21 ms 189,424 cims1 · details 
SELECT     "lab_test_results".* FROM       "lab_test_results"  WHERE     ("lab_test_results".lab_test_request_detail_id = $1) AND ("lab_test_results"."status" = $2) ORDER BY  id
66 min 0.5% 20 ms 193,265 erps1 · details 
INSERT INTO "family_banks" ("amount", "bill_extid", "bill_id", "bill_ref_number", "business_short_code", "callback_message", "callback_received_at", "checkout_request_id", "created_at", "created_by", "invoice_number", "is_updated", "kyc_name", "location_id", "merchant_request_identifier", "message", "msisdn", "org_account_balance", "patient_id", "payment_date", "phone_number", "provider", "result_code", "result_desc", "trans_amount", "trans_id", "trans_time", "transaction_id", "transaction_status", "transaction_type", "updated_at", "visit_id") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32) RETURNING "id"
62 min 0.4% 69 ms 54,076 erps1 · details 
SELECT * FROM ( SELECT     DISTINCT ON ("patients".id) "patients".id, patients.id AS alias_0 FROM       "patients" LEFT OUTER JOIN "address_details" ON "address_details"."addressable_id" = "patients"."id" AND "address_details"."addressable_type" = $1 WHERE     (patients.extid not ilike $2) AND (address_details.mobile_no =$3) AND (patients.id in (select patient_id from visits where location_id = $4))) AS id_list ORDER BY id_list.alias_0  LIMIT $5 OFFSET $6
61 min 0.4% 36 ms 103,048 cims2 · details 
SELECT     "bills".* FROM       "bills"  WHERE     ("bills"."service_request_id" = $1) LIMIT $2
58 min 0.4% 50 ms 69,529 cims1 · details 
SELECT     a.*,e.id as encounter_id FROM       "appointments"  as a inner join visits as v on a.visit_id=v.id inner join encounters as e on v.id = e.visit_id
                                                                                inner join service_request_details as srd on srd.id=a.service_request_detail_id                                                                                                         inner join service_requests as sq on sq.id=srd.service_request_id WHERE     (a.check1 is null and e.status = $1 and e.name !=$2 and v.patient_type =$3 and date(a.app_start_date_time) >= $4 and  date(a.app_start_date_time) <= $5 and a.app_status = $6  and srd.billing_status != $7 and srd.billing_status = $8 and sq.clinician_id in ($9) and sq.location_id = $10 and a.is_telemedicine is null) ORDER BY  a.patient_criticality_id desc
44 min 0.3% 363 ms 7,249 pghero · details 
SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), $1, $2) AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), $3, $4) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname IS NOT NULL ORDER BY 1, 2 /*pghero*/
38 min 0.3% 259 ms 8,697 cims1 · details 
SELECT     COUNT(*) AS count_id FROM       "drug_prescription_details"  as dpd inner join prescription_masters as pm on pm.id=dpd.prescription_master_id inner join visits as v on v.id=pm.visit_id WHERE     (pm.patient_id = $1  AND (pm.is_ivf IS NULL OR pm.is_ivf != $2) AND dpd.status NOT IN ($3, $4) AND dpd.end_date >= $5 AND pm.visit_id = $6 AND v.patient_type = $7 AND dpd.is_discharge_med is null or dpd.is_discharge_med !=$8)
36 min 0.3% 74 ms 29,372 cims1 · details 
SELECT     "bill_details".* FROM       "bill_details"  WHERE     ("bill_details"."reference_1" = $1) LIMIT $2
33 min 0.2% 49 ms 39,871 cims1 · details 
SELECT     ocpd.narration as narration,ocpd.created_by as created_by,ocpd.created_at as created_at,ocpd.opd_card_prescription_id,ocpd.include_note_in_dashboard,ocp.patient_id,ocpd.id,ocpd.narration_type as narration_type FROM       "opd_card_prescription_details"  as ocpd inner join opd_card_prescriptions as ocp on ocp.id =  ocpd.opd_card_prescription_id WHERE     (ocp.patient_id=$1 and ocpd.narration_type in ($2,$3) and ocpd.status=$4) ORDER BY  ocpd.opd_card_prescription_id desc,ocpd.created_at desc
31 min 0.2% 148 ms 12,467 cims1 · details 
SELECT     "pains".* FROM       "pains"  WHERE     (patient_id=$1)
30 min 0.2% 143 ms 12,791 erps1 · details 
SELECT COUNT(*) AS count_id FROM (SELECT $1 FROM "patients"  WHERE     (patients.extid not ilike $2) AND (lower(patients.last_name) iLIKE $3) AND (patients.id in (select patient_id from visits where location_id = $4))) AS subquery
30 min 0.2% 27 ms 67,847 cims2 · details 
SELECT COUNT(*) AS count_id FROM (SELECT $1 FROM "service_requests"  WHERE     ("service_requests".patient_id = $2)) AS subquery
30 min 0.2% 142 ms 12,467 cims1 · details 
SELECT     "flaccs".* FROM       "flaccs"  WHERE     (patient_id=$1)
29 min 0.2% 21 ms 83,322 cims1 · details 
SELECT     "lab_test_results".* FROM       "lab_test_results"  WHERE     ("lab_test_results"."lab_test_request_detail_id" = $1) LIMIT $2
27 min 0.2% 25 ms 65,424 erps1 · details 
SELECT b.id as id,b.final_amount as final_amount, sum(bd.net_amount+bd.corporate_covered_amount) as net_amount ,b.narration as narration
                              FROM bills  as b 
                              inner join bill_details as bd on b.id = bd.bill_id
                              inner join visits as v on v.id = b.visit_id
                              inner join patients as p on p.id = v.patient_id
                              inner join corporates as c on v.corporate_id = c.id
                              inner join department_service_groups as dsg on dsg.id = bd.department_service_group_id                              
                              WHERE (bd.status = $1 and bd.is_cancelled != $2 and ((v.patient_type=$3) or (v.patient_type=$4 and b.narration=$5)) and date(bill_date) >= $6 and date(bill_date) <= $7 and dsg.id = $8 and bd.location_id =$9)
                              GROUP BY b.id,b.narration,b.final_amount
                              ORDER BY b.id
27 min 0.2% 46 ms 35,000 erps1 · details 
SELECT     "family_bank_stk_requests".* FROM       "family_bank_stk_requests"  WHERE     ("family_bank_stk_requests"."visit_id" = $1)
27 min 0.2% 30 ms 52,862 erps1 · details 
SELECT     "generic_names".* FROM       "generic_names"  WHERE     (status=$1 and healthcare_institution_id=$2) ORDER BY  lower(name)
26 min 0.2% 96 ms 16,080 erps1 · details 
SELECT COUNT(*) AS count_id FROM (SELECT $1 FROM "transaction_details"  WHERE     ("transaction_details".ip_supplementary_bill_id = $2) AND ("transaction_details"."narration" = $3)) AS subquery
25 min 0.2% 21 ms 71,507 cims1 · details 
SELECT     a.*,e.id as encounter_id FROM       "appointments"  as a inner join visits as v on a.visit_id=v.id inner join encounters as e on v.id = e.visit_id
                                                                                inner join service_request_details as srd on srd.id=a.service_request_detail_id                                                                                                         inner join service_requests as sq on sq.id=srd.service_request_id WHERE     (a.check1 is null and e.status = $1  and e.name !=$2 and v.patient_type =$3 and date(a.app_start_date_time) = $4 and a.app_status = $5  and srd.billing_status != $6 and sq.clinician_id in ($7) and sq.location_id = $8 and a.is_telemedicine = $9 and a.txnstatus=$10) ORDER BY  a.app_start_date_time asc
25 min 0.2% 40 ms 38,024 cims2 · details 
SELECT     COUNT(lts.id) AS count_id FROM       "lab_test_requests"  as ltr inner join lab_test_request_details as ltrd on ltr.id = ltrd.lab_test_request_id inner join lab_test_results as lts on lts.lab_test_request_detail_id = ltrd.id WHERE     (ltr.patient_id =$1  and lts.service_unit_id=$2 and ltrd.created_at <$3 and ltrd.id !=$4 and ltrd.status=$5)
24 min 0.2% 122 ms 11,596 erps1 · details 
SELECT COUNT(*) AS count_id FROM (SELECT $1 FROM "patients"  WHERE     (patients.extid not ilike $2) AND (lower(patients.name) iLIKE $3) AND (patients.id in (select patient_id from visits where location_id = $4))) AS subquery