PgHero
No long running queries
Connections healthy 15
Vacuuming healthy
No columns near integer overflow
No invalid indexes or constraints
No duplicate indexes
2 suggested indexes
25 slow queries

Suggested Indexes

Add indexes to speed up queries.

Details
CREATE INDEX CONCURRENTLY ON bill_details (reference_1)

to speed up

Total Time Average Time Calls
13 min 0.2% 74 ms 10,584 cims1 · details 
SELECT     "bill_details".* FROM       "bill_details"  WHERE     ("bill_details"."reference_1" = $1) LIMIT $2
Details
CREATE INDEX CONCURRENTLY ON bills (service_request_id)

to speed up

Total Time Average Time Calls
43 min 0.6% 28 ms 90,629 cims1 · details 
SELECT     "bills".* FROM       "bills"  WHERE     ("bills"."service_request_id" = $1) LIMIT $2

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
117 min 2% 30 ms 237,045 cims1 · details 
SELECT     sr.id as Request_id ,sr.request_date, 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
117 min 2% 1,218 ms 5,768 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*/
93 min 1% 37 ms 152,214 erps2 · details 
UPDATE "extids" SET "current_value" = $1, "lock_version" = $2, "updated_at" = $3  WHERE (("extids"."id" = $4 AND "extids"."lock_version" = $5))
66 min 1% 206 ms 19,347 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)
48 min 0.7% 179 ms 16,031 cims1 · details 
SELECT     "icd10_codes".* FROM       "icd10_codes"  WHERE     (icd_name ilike $1) ORDER BY  icd_name LIMIT $2
47 min 0.7% 22 ms 124,954 erps1 · details 
SELECT     "item_reorder_levels".* FROM       "item_reorder_levels"  WHERE     (item_id=$1 and inventory_head_id=$2) LIMIT $3
43 min 0.6% 28 ms 90,629 cims1 · details 
SELECT     "bills".* FROM       "bills"  WHERE     ("bills"."service_request_id" = $1) LIMIT $2
Details
CREATE INDEX CONCURRENTLY ON bills (service_request_id)
41 min 0.6% 66 ms 37,418 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
38 min 0.6% 44 ms 51,107 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
36 min 0.5% 347 ms 6,190 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*/
34 min 0.5% 138 ms 14,921 erps1 · details 
SELECT     "patients".* FROM       "patients"  WHERE     (patients.extid not ilike $1) AND (lower(patients.name) iLIKE $2) AND (lower(patients.last_name) iLIKE $3) ORDER BY  patients.id
32 min 0.5% 150 ms 12,633 erps1 · details 
SELECT     "patients".* FROM       "patients"  WHERE     (patients.extid not ilike $1) AND (lower(patients.name) iLIKE $2) AND (lower(patients.last_name) iLIKE $3) AND (patients.id in (select patient_id from visits where location_id = $4)) ORDER BY  patients.id LIMIT $5 OFFSET $6
27 min 0.4% 138 ms 11,541 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
21 min 0.3% 52 ms 23,898 cims2 · details 
SELECT     sr.id as Request_id ,sr.request_date, 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
20 min 0.3% 41 ms 30,091 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
20 min 0.3% 111 ms 10,717 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
20 min 0.3% 22 ms 53,911 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
17 min 0.3% 73 ms 14,395 erps1 · details 
SELECT     "bills".* FROM       "bills"  WHERE     (date(bill_date) >= $1 and date(bill_date) <= $2) AND (narration != $3) AND (is_discount_approved = $4) AND (bills.location_id = $5)
17 min 0.3% 29 ms 34,154 erps1 · details 
SELECT     "generic_names".* FROM       "generic_names"  WHERE     (status=$1 and healthcare_institution_id=$2) ORDER BY  lower(name)
16 min 0.2% 132 ms 7,288 cims1 · details 
SELECT     "pains".* FROM       "pains"  WHERE     (patient_id=$1)
16 min 0.2% 131 ms 7,288 cims1 · details 
SELECT     "flaccs".* FROM       "flaccs"  WHERE     (patient_id=$1)
14 min 0.2% 228 ms 3,586 erps2 · 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)
13 min 0.2% 74 ms 10,584 cims1 · details 
SELECT     "bill_details".* FROM       "bill_details"  WHERE     ("bill_details"."reference_1" = $1) LIMIT $2
Details
CREATE INDEX CONCURRENTLY ON bill_details (reference_1)
13 min 0.2% 82 ms 9,657 erps2 · 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
13 min 0.2% 21 ms 36,541 erps2 · details 
SELECT     "item_reorder_levels".* FROM       "item_reorder_levels"  WHERE     (item_id=$1 and inventory_head_id=$2) LIMIT $3