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 )

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

UPDATE "extids" SET "current_value" = $1, "lock_version" = $2, "updated_at" = $3  WHERE (("extids"."id" = $4 AND "extids"."lock_version" = $5))

Covered by index on (id)
Rows: 1948
Row progression: 1948, 1

Row estimates
- id (=): 1
- lock_version (=): 3

Existing indexes
- id PRIMARY

SELECT     "bills".* FROM       "bills"  WHERE     ("bills"."service_request_id" = $1) LIMIT $2

Covered by index on (service_request_id)
Rows: 350438
Row progression: 350438, 1

Row estimates
- service_request_id (=): 1

Existing indexes
- id PRIMARY
- created_at
- extid UNIQUE
- form_token UNIQUE
- ip_supplementary_bill_id
- luxury_type_id
- narration
- service_request_id
- visit_id

UPDATE "extids" SET "current_value" = $1, "lock_version" = $2, "updated_at" = $3  WHERE (("extids"."id" = $4 AND "extids"."lock_version" = $5))

Covered by index on (id)
Rows: 1948
Row progression: 1948, 1

Row estimates
- id (=): 1
- lock_version (=): 3

Existing indexes
- id PRIMARY

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*/

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

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)

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)

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

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)

SELECT     "item_reorder_levels".* FROM       "item_reorder_levels"  WHERE     (item_id=$1 and inventory_head_id=$2) LIMIT $3

Covered by index on (item_id, inventory_head_id)
Rows: 656453
Row progression: 656453, 167, 1

Row estimates
- item_id (=): 167
- inventory_head_id (=): 3931

Existing indexes
- id PRIMARY
- item_id, inventory_head_id

SELECT     "icd10_codes".* FROM       "icd10_codes"  WHERE     (icd_name ilike $1) ORDER BY  icd_name LIMIT $2

Covered by index on (icd_name gist_trgm_ops)
Rows: 68100
Row progression: 68100, 6810

Row estimates
- icd_name (~~*): 6810
- icd_name (sort): 1

Existing indexes
- id PRIMARY
- icd_name gist_trgm_ops GIST

SELECT COUNT(*) AS count_id FROM (SELECT $1 FROM "service_requests"  WHERE     ("service_requests".patient_id = $2)) AS subquery

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

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

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"

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*/

SELECT     "bills".* FROM       "bills"  WHERE     ("bills"."service_request_id" = $1) LIMIT $2

Covered by index on (service_request_id)
Rows: 350438
Row progression: 350438, 1

Row estimates
- service_request_id (=): 1

Existing indexes
- id PRIMARY
- created_at
- extid UNIQUE
- form_token UNIQUE
- ip_supplementary_bill_id
- luxury_type_id
- narration
- service_request_id
- visit_id

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

SELECT     itd.* FROM       "inv_tran_details"  as itd inner join inventory_transactions as it on it.id = itd.inventory_transaction_id inner join item_with_details as iwd on itd.item_with_detail_id = iwd.id WHERE     (it.reference2 = $1 and iwd.item_id = $2) ORDER BY  it.id desc LIMIT $3

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

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

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

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)

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

SELECT     "bill_details".* FROM       "bill_details"  WHERE     ("bill_details"."reference_1" = $1) LIMIT $2

Covered by index on (reference_1)
Rows: 589696
Row progression: 589696, 1

Row estimates
- reference_1 (=): 1

Existing indexes
- id PRIMARY
- bill_id
- item_with_detail_id
- location_id
- narration
- patient_id
- reference_1
- reference_3
- service_id
- service_request_detail_id
- service_request_detail_id
- station_id

SELECT     "pains".* FROM       "pains"  WHERE     (patient_id=$1)

Covered by index on (patient_id)
Rows: 292652
Row progression: 292652, 20

Row estimates
- patient_id (=): 20

Existing indexes
- id PRIMARY
- patient_id

SELECT COUNT(*) AS count_id FROM (SELECT $1 FROM "service_requests"  WHERE     ("service_requests".patient_id = $2)) AS subquery

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

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)

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

SELECT     "flaccs".* FROM       "flaccs"  WHERE     (patient_id=$1)

Covered by index on (patient_id)
Rows: 257902
Row progression: 257902, 17

Row estimates
- patient_id (=): 17

Existing indexes
- id PRIMARY
- patient_id

SELECT     "generic_names".* FROM       "generic_names"  WHERE     (status=$1 and healthcare_institution_id=$2) ORDER BY  lower(name)

Covered by index on (status)
Rows: 4082
Row progression: 4082, 1021, 1021

Row estimates
- status (=): 1021
- healthcare_institution_id (=): 4082

Existing indexes
- id PRIMARY
- healthcare_institution_id
- is_external
- is_internal
- is_rt
- name
- name gist_trgm_ops GIST
- purchase_type
- status
- store_category_id, name

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

SELECT * FROM mpesa_payments WHERE TRIM(merchant_request_identifier) = $1

SELECT     ltrd.id as detail_id,ltrd.lab_test_request_id as lab_test_request_id,ltrd.begin_specimen_received_time as spec_procedure_time, s.id as service_id,
     s.name as service_name,s.is_vantana, sp.name as specimen_name,lt.patient_id,ltrd.urgent_check,ltp.status as ltp_status, ltp.extid,ltp.priority_comments, ltrd.status as ltrd_status,s.report_type,
     ltp.indications,ltp.specimen_prefix as specimen_prefix,ltp.specimen_number as specimen_number,ltrd.station_id,ltrd.service_request_detail_id,(p.name||$1||p.last_name) as pat_name,p.extid as pat_extid,lt.visit_id as visit_id, v.patient_type,v.clinician_id as cli_id,
     sr.is_cpoe_request,stn.name as station_name,srd.order_remarks,p.title_id,p.id as pat_id,p.age as pat_age,p.gender_id as pat_gender, ltp.id as specimen_id,ltrd.specimen_received_time,ltrd.status FROM       "lab_test_request_details"  as ltrd
     inner join lab_test_requests as lt on lt.id =ltrd.lab_test_request_id
     inner join cross_location_orders as clo on clo.lab_test_request_detail_id=ltrd.id
     inner join services as s on s.id = ltrd.service_id
     inner join specimens as sp on sp.id = ltrd.specimen_id
     inner join lab_test_specimens as ltp on ltp.lab_test_request_detail_id = ltrd.id
     inner join patients as p on lt.patient_id = p.id
     inner join visits as v on v.id=lt.visit_id
     inner join service_request_details as srd on srd.id = ltrd.service_request_detail_id
     inner join service_requests as sr on srd.service_request_id=sr.id
     inner join stations as stn on stn.id=ltrd.station_id WHERE     (ltrd.specimen_recieved = $2 and (ltrd.status = $3  or ltrd.status = $4 or ltrd.status = $5) and ltrd.sending_approval is null and ltp.status = $6 and (date(ltrd.begin_specimen_received_time) >= $7 and date(ltrd.begin_specimen_received_time) <= $8) and s.id in(select id from services where service_group_id in(select service_group_id from service_groups_stations where (station_id = $9 ))) and ((s.report_type= $10) or (s.report_type = $11) or (s.report_type = $12) or (s.report_type = $13)) and lt.location_id = $14 and clo.to_location_id = $15 and clo.status = $16) ORDER BY  ltp.extid,lt.emergency desc,srd.is_urgent desc

SELECT n.nspname AS schema, c.relname AS relation, CASE c.relkind WHEN $1 THEN $2 WHEN $3 then $4 ELSE $5 END AS type, pg_table_size(c.oid) AS size_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN ($6, $7) AND n.nspname !~ $8 AND c.relkind IN ($9, $10, $11) ORDER BY pg_table_size(c.oid) DESC, 2 ASC /*pghero*/