explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Qqo

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 100.778 ↓ 0.0 0 1

GroupAggregate (cost=176,034.91..176,036.00 rows=1 width=1,132) (actual time=100.778..100.778 rows=0 loops=1)

  • Group Key: foo.sample_date_time, foo.sample_sno, foo.sample_assertion_batch, foo.sample_type, foo.sample_date, foo.collection_center, foo.mr_no, foo.patient_id, foo.sample_qty, foo.ih_name, foo.sample_status, foo.assertion_time, foo.rejected_time, foo.conducted, foo.sample_type_id, foo.collection_center_id, foo.visit_type, foo.center_id, foo.sample_collection_id, foo.patient_name, foo.outsource_dest_id, foo.outsource_name, foo.orig_sample_no, foo.transfer_time, foo.transfer_other_details, foo.receipt_time, foo.receipt_other_details, foo.bill_status, foo.charge_head
2. 0.018 100.776 ↓ 0.0 0 1

Sort (cost=176,034.91..176,034.92 rows=1 width=1,365) (actual time=100.776..100.776 rows=0 loops=1)

  • Sort Key: foo.sample_date_time, foo.sample_sno, foo.sample_assertion_batch, foo.sample_type, foo.sample_date, foo.collection_center, foo.mr_no, foo.patient_id, foo.sample_qty, foo.ih_name, foo.assertion_time, foo.rejected_time, foo.conducted, foo.sample_type_id, foo.collection_center_id, foo.visit_type, foo.sample_collection_id, foo.patient_name, foo.outsource_dest_id, foo.outsource_name, foo.orig_sample_no, foo.transfer_time, foo.transfer_other_details, foo.receipt_time, foo.receipt_other_details, foo.bill_status, foo.charge_head
  • Sort Method: quicksort Memory: 25kB
3. 0.000 100.758 ↓ 0.0 0 1

Subquery Scan on foo (cost=176,034.80..176,034.90 rows=1 width=1,365) (actual time=100.758..100.758 rows=0 loops=1)

4. 0.000 100.758 ↓ 0.0 0 1

Unique (cost=176,034.80..176,034.89 rows=1 width=1,425) (actual time=100.758..100.758 rows=0 loops=1)

5. 0.017 100.758 ↓ 0.0 0 1

Sort (cost=176,034.80..176,034.81 rows=1 width=1,425) (actual time=100.758..100.758 rows=0 loops=1)

  • Sort Key: st.sample_type, sc.sample_sno, sc.sample_assertion_batch, ((sc.sample_date)::date), scc.collection_center, sc.sample_collection_id, sc1.transfer_time, sc1.transfer_other_details, sc.receipt_time, sc.receipt_other_details, d.ddept_id, diagnostics_departments.ddept_name, d.test_name, sc.sample_qty, sc.assertion_time, sc.rejected_time, tp.conducted, st.sample_type_id, (COALESCE(pr.mr_no, isr.mr_no)), (COALESCE(pr.patient_id, isr.incoming_visit_id)), pr.collection_center_id, (COALESCE(isr.patient_name, get_patient_full_name(sm.salutation, pd.patient_name, pd.middle_name, pd.last_name))), (CASE WHEN (isr.incoming_visit_id IS NOT NULL) THEN 't'::bpchar ELSE pr.visit_type END), sc.sample_date, isrd.orig_sample_no, (COALESCE(pr.center_id, isr.center_id)), ih.hospital_name, isr.patient_name, isr.patient_other_info, (CASE WHEN (sc.sample_status = 'R'::bpchar) THEN tpr.priority ELSE tp.priority END), dom.outsource_dest_id, (COALESCE(om.oh_name, hcm.center_name)), (CASE WHEN is_outhouse_test(d.test_id, pr.center_id) THEN 'O'::text ELSE 'I'::text END), b.status, bc.charge_head
  • Sort Method: quicksort Memory: 25kB
6. 0.001 100.741 ↓ 0.0 0 1

Nested Loop (cost=106,425.15..176,034.79 rows=1 width=1,425) (actual time=100.741..100.741 rows=0 loops=1)

7. 0.000 100.740 ↓ 0.0 0 1

Nested Loop (cost=106,425.01..176,034.12 rows=1 width=1,289) (actual time=100.740..100.740 rows=0 loops=1)

8. 0.000 100.740 ↓ 0.0 0 1

Nested Loop (cost=106,424.58..176,032.99 rows=1 width=1,301) (actual time=100.740..100.740 rows=0 loops=1)

9. 0.000 100.740 ↓ 0.0 0 1

Nested Loop (cost=106,424.15..176,031.18 rows=1 width=1,290) (actual time=100.740..100.740 rows=0 loops=1)

10. 0.000 100.740 ↓ 0.0 0 1

Nested Loop Left Join (cost=106,423.71..176,029.09 rows=1 width=1,289) (actual time=100.740..100.740 rows=0 loops=1)

11. 0.001 100.740 ↓ 0.0 0 1

Nested Loop Left Join (cost=106,423.28..176,028.00 rows=1 width=1,284) (actual time=100.740..100.740 rows=0 loops=1)

12. 0.000 100.739 ↓ 0.0 0 1

Nested Loop Left Join (cost=106,422.86..176,024.86 rows=1 width=1,284) (actual time=100.739..100.739 rows=0 loops=1)

  • Join Filter: (scc.collection_center_id = pr.collection_center_id)
13. 0.000 100.739 ↓ 0.0 0 1

Nested Loop Left Join (cost=106,422.86..176,023.83 rows=1 width=1,166) (actual time=100.739..100.739 rows=0 loops=1)

14. 0.001 100.739 ↓ 0.0 0 1

Nested Loop Left Join (cost=106,422.72..176,023.67 rows=1 width=955) (actual time=100.739..100.739 rows=0 loops=1)

  • Join Filter: ((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text)
15. 0.000 100.738 ↓ 0.0 0 1

Nested Loop Left Join (cost=106,422.43..176,022.78 rows=1 width=938) (actual time=100.738..100.738 rows=0 loops=1)

16. 0.001 100.738 ↓ 0.0 0 1

Nested Loop Left Join (cost=106,422.30..176,022.62 rows=1 width=829) (actual time=100.738..100.738 rows=0 loops=1)

17. 0.000 100.737 ↓ 0.0 0 1

Nested Loop (cost=106,421.87..176,021.49 rows=1 width=797) (actual time=100.737..100.737 rows=0 loops=1)

18. 0.001 100.737 ↓ 0.0 0 1

Hash Left Join (cost=106,421.73..176,021.32 rows=1 width=479) (actual time=100.737..100.737 rows=0 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
19. 0.000 100.736 ↓ 0.0 0 1

Nested Loop Left Join (cost=106,419.44..176,019.01 rows=1 width=583) (actual time=100.736..100.736 rows=0 loops=1)

  • Filter: (COALESCE(pr.center_id, isr.center_id) = 13)
20. 0.000 100.736 ↓ 0.0 0 1

Nested Loop Left Join (cost=106,419.16..175,912.18 rows=116 width=537) (actual time=100.736..100.736 rows=0 loops=1)

21. 0.001 100.736 ↓ 0.0 0 1

Hash Left Join (cost=106,418.73..175,361.54 rows=116 width=496) (actual time=100.736..100.736 rows=0 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
22. 0.000 100.735 ↓ 0.0 0 1

Nested Loop (cost=106,416.88..175,358.20 rows=116 width=278) (actual time=100.735..100.735 rows=0 loops=1)

23. 0.000 100.735 ↓ 0.0 0 1

Hash Left Join (cost=106,416.60..175,298.38 rows=116 width=247) (actual time=100.735..100.735 rows=0 loops=1)

  • Hash Cond: (CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END = tp.sample_collection_id)
  • Filter: ((CASE WHEN (sc.sample_status = 'R'::bpchar) THEN tpr.conducted ELSE tp.conducted END)::text = ANY ('{N,NRN}'::text[]))
24. 0.001 100.735 ↓ 0.0 0 1

Nested Loop Left Join (cost=29,297.47..86,934.24 rows=2,905 width=238) (actual time=100.735..100.735 rows=0 loops=1)

25. 0.000 100.734 ↓ 0.0 0 1

Hash Left Join (cost=29,297.04..68,115.37 rows=2,905 width=223) (actual time=100.734..100.734 rows=0 loops=1)

  • Hash Cond: (sc.sample_collection_id = sr.sample_collectio
26. 0.001 100.734 ↓ 0.0 0 1

Hash Left Join (cost=29,137.14..67,853.51 rows=2,905 width=219) (actual time=100.734..100.734 rows=0 loops=1)

  • Hash Cond: (sc.outsource_dest_id = dom.outsource_dest_id)
  • Filter: ((dom.outsource_dest_type IS NULL) OR (dom.outsource_dest_type <> 'C'::bpchar))
27. 0.032 100.733 ↓ 0.0 0 1

Hash Join (cost=29,135.19..67,835.14 rows=2,975 width=101) (actual time=100.733..100.733 rows=0 loops=1)

  • Hash Cond: ((tp1_1.sample_no)::text = (sc.sample_sno)::text)
28. 5.155 21.430 ↑ 122,289.0 1 1

Bitmap Heap Scan on tests_prescribed tp1_1 (cost=2,292.17..40,503.78 rows=122,289 width=13) (actual time=21.430..21.430 rows=1 loops=1)

  • Recheck Cond: ((conducted)::text = 'N'::text)
  • Heap Blocks: exact=1
29. 16.275 16.275 ↓ 1.0 122,543 1

Bitmap Index Scan on tests_prescribed_conducted_idx (cost=0.00..2,261.59 rows=122,289 width=0) (actual time=16.275..16.275 rows=122,543 loops=1)

  • Index Cond: ((conducted)::text = 'N'::text)
30. 0.001 79.271 ↓ 0.0 0 1

Hash (cost=26,621.56..26,621.56 rows=17,717 width=101) (actual time=79.270..79.271 rows=0 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 256kB
31. 79.270 79.270 ↓ 0.0 0 1

Seq Scan on sample_collection sc (cost=0.00..26,621.56 rows=17,717 width=101) (actual time=79.270..79.270 rows=0 loops=1)

  • Filter: ((sample_status = 'C'::bpchar) AND (sample_receive_status = 'R'::bpchar) AND (sample_status = ANY ('{C,A,R}'::bpchar[])) AND ((sample_date)::date >= '2018-12-29'::date))
  • Rows Removed by Filter: 599355
32. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.42..1.42 rows=42 width=130) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Seq Scan on diag_outsource_master dom (cost=0.00..1.42 rows=42 width=130) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Hash (cost=88.29..88.29 rows=5,729 width=8) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Seq Scan on sample_rejections sr (cost=0.00..88.29 rows=5,729 width=8) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Index Scan using tests_prescribed_pkey on tests_prescribed tpr (cost=0.43..6.47 rows=1 width=15) (never executed)

  • Index Cond: (prescribed_id = CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END)
37. 0.000 0.000 ↓ 0.0 0

Hash (cost=50,941.28..50,941.28 rows=1,425,828 width=19) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Seq Scan on tests_prescribed tp (cost=0.00..50,941.28 rows=1,425,828 width=19) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Index Scan using diagnostics_pkey on diagnostics d (cost=0.28..0.51 rows=1 width=47) (never executed)

  • Index Cond: ((COALESCE(tp.test_id, tpr.test_id))::text = (test_id)::text)
40. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.38..1.38 rows=38 width=336) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Seq Scan on outhouse_master om (cost=0.00..1.38 rows=38 width=336) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..4.74 rows=1 width=41) (never executed)

  • Index Cond: ((patient_id)::text = (sc.patient_id)::text)
43. 0.000 0.000 ↓ 0.0 0

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.29..0.91 rows=1 width=61) (never executed)

  • Index Cond: ((sc.patient_id)::text = (incoming_visit_id)::text)
44. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.13..2.13 rows=13 width=18) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Seq Scan on hospital_center_master hcm (cost=0.00..2.13 rows=13 width=18) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Index Scan using diagnostics_departments_pkey on diagnostics_departments (cost=0.14..0.16 rows=1 width=356) (never executed)

  • Index Cond: ((ddept_id)::text = (d.ddept_id)::text)
47. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..1.12 rows=1 width=47) (never executed)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
48. 0.000 0.000 ↓ 0.0 0

Index Scan using salutation_master_pkey on salutation_master sm (cost=0.14..0.16 rows=1 width=156) (never executed)

  • Index Cond: ((salutation_id)::text = (pd.salutation)::text)
49. 0.000 0.000 ↓ 0.0 0

Index Scan using incoming_sample_registration_details_pkey on incoming_sample_registration_details isrd (cost=0.29..0.88 rows=1 width=30) (never executed)

  • Index Cond: (tp.prescribed_id = prescribed_id)
50. 0.000 0.000 ↓ 0.0 0

Index Scan using incoming_hospitals_pkey on incoming_hospitals ih (cost=0.13..0.15 rows=1 width=266) (never executed)

  • Index Cond: ((hospital_id)::text = (isr.orig_lab_name)::text)
51. 0.000 0.000 ↓ 0.0 0

Seq Scan on sample_collection_centers scc (cost=0.00..1.01 rows=1 width=122) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Index Scan using tests_prescribed_pkey on tests_prescribed tp1 (cost=0.43..3.13 rows=1 width=8) (never executed)

  • Index Cond: (prescribed_id = isrd.source_test_prescribed)
53. 0.000 0.000 ↓ 0.0 0

Index Scan using sample_collection_id_pkey on sample_collection sc1 (cost=0.42..1.08 rows=1 width=13) (never executed)

  • Index Cond: (sample_collection_id = tp1.sample_collection_id)
54. 0.000 0.000 ↓ 0.0 0

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.44..2.08 rows=1 width=16) (never executed)

  • Index Cond: (COALESCE((tp.prescribed_id)::text, (tpr.prescribed_id)::text) = (activity_id)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
55. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.43..1.80 rows=1 width=29) (never executed)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
56. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_pkey on bill b (cost=0.43..1.12 rows=1 width=16) (never executed)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
57. 0.000 0.000 ↓ 0.0 0

Index Scan using sample_type_pkey on sample_type st (cost=0.14..0.16 rows=1 width=162) (never executed)

  • Index Cond: (sample_type_id = sc.sample_type_id)