explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ht3X

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

Limit (cost=895,198.10..895,219.80 rows=20 width=1,132) (actual time=441.710..441.710 rows=0 loops=1)

2. 0.002 441.708 ↓ 0.0 0 1

GroupAggregate (cost=895,198.10..895,285.99 rows=81 width=1,132) (actual time=441.708..441.708 rows=0 loops=1)

  • Group Key: foo.sample_date, foo.sample_sno, foo.sample_assertion_batch, foo.sample_type, 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.sample_date_time, 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
3. 0.047 441.706 ↓ 0.0 0 1

Sort (cost=895,198.10..895,198.31 rows=81 width=1,367) (actual time=441.706..441.706 rows=0 loops=1)

  • Sort Key: foo.sample_date, foo.sample_sno, foo.sample_assertion_batch, foo.sample_type, 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_date_time, 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
4. 0.001 441.659 ↓ 0.0 0 1

Subquery Scan on foo (cost=895,187.44..895,195.54 rows=81 width=1,367) (actual time=441.659..441.659 rows=0 loops=1)

5. 0.000 441.658 ↓ 0.0 0 1

Unique (cost=895,187.44..895,194.73 rows=81 width=1,428) (actual time=441.658..441.658 rows=0 loops=1)

6. 0.053 441.658 ↓ 0.0 0 1

Sort (cost=895,187.44..895,187.64 rows=81 width=1,428) (actual time=441.658..441.658 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
7. 0.000 441.605 ↓ 0.0 0 1

Hash Left Join (cost=657,488.15..895,184.87 rows=81 width=1,428) (actual time=441.605..441.605 rows=0 loops=1)

  • Hash Cond: (pr.collection_center_id = scc.collection_center_id)
8. 0.017 441.605 ↓ 0.0 0 1

Hash Join (cost=657,466.90..895,119.83 rows=32 width=1,332) (actual time=441.605..441.605 rows=0 loops=1)

  • Hash Cond: (sc.sample_type_id = st.sample_type_id)
9. 0.001 441.553 ↓ 0.0 0 1

Nested Loop (cost=657,464.80..895,117.64 rows=32 width=1,174) (actual time=441.553..441.553 rows=0 loops=1)

10. 0.000 441.552 ↓ 0.0 0 1

Nested Loop (cost=657,464.37..895,021.01 rows=32 width=1,186) (actual time=441.552..441.552 rows=0 loops=1)

11. 0.000 441.552 ↓ 0.0 0 1

Nested Loop Left Join (cost=657,463.80..894,907.96 rows=32 width=1,176) (actual time=441.552..441.552 rows=0 loops=1)

12. 0.001 441.552 ↓ 0.0 0 1

Nested Loop Left Join (cost=657,463.37..894,812.14 rows=32 width=1,171) (actual time=441.552..441.552 rows=0 loops=1)

13. 0.000 441.551 ↓ 0.0 0 1

Nested Loop (cost=657,462.94..894,702.53 rows=32 width=1,171) (actual time=441.551..441.551 rows=0 loops=1)

14. 0.001 441.551 ↓ 0.0 0 1

Hash Left Join (cost=657,462.50..894,512.40 rows=33 width=1,169) (actual time=441.551..441.551 rows=0 loops=1)

  • Hash Cond: ((isr.orig_lab_name)::text = (ih.hospital_id)::text)
15. 0.000 441.550 ↓ 0.0 0 1

Nested Loop Left Join (cost=657,461.16..894,510.95 rows=33 width=958) (actual time=441.550..441.550 rows=0 loops=1)

  • Join Filter: ((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text)
16. 0.001 441.550 ↓ 0.0 0 1

Hash Left Join (cost=657,460.74..894,412.82 rows=33 width=941) (actual time=441.550..441.550 rows=0 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
17. 0.000 441.549 ↓ 0.0 0 1

Nested Loop Left Join (cost=657,459.26..894,411.24 rows=33 width=832) (actual time=441.549..441.549 rows=0 loops=1)

18. 0.013 441.549 ↓ 0.0 0 1

Hash Join (cost=657,458.84..894,312.20 rows=33 width=799) (actual time=441.549..441.549 rows=0 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (diagnostics_departments.ddept_id)::text)
19. 0.001 441.523 ↓ 0.0 0 1

Hash Left Join (cost=657,457.50..894,310.75 rows=33 width=481) (actual time=441.523..441.523 rows=0 loops=1)

  • Hash Cond: ((sc.patient_id)::text = (isr.incoming_visit_id)::text)
  • Filter: (COALESCE(pr.center_id, isr.center_id) = 2)
20. 0.001 441.522 ↓ 0.0 0 1

Nested Loop Left Join (cost=651,626.78..888,462.93 rows=6,519 width=434) (actual time=441.522..441.522 rows=0 loops=1)

21. 0.000 441.521 ↓ 0.0 0 1

Hash Left Join (cost=651,626.35..856,688.19 rows=6,519 width=393) (actual time=441.521..441.521 rows=0 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
22. 0.001 441.521 ↓ 0.0 0 1

Hash Left Join (cost=651,623.84..856,620.74 rows=6,519 width=185) (actual time=441.521..441.521 rows=0 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
23. 0.014 441.520 ↓ 0.0 0 1

Hash Join (cost=651,621.60..856,600.23 rows=6,519 width=172) (actual time=441.520..441.520 rows=0 loops=1)

  • Hash Cond: ((COALESCE(tp.test_id, tpr.test_id))::text = (d.test_id)::text)
24. 0.001 440.647 ↓ 0.0 0 1

Hash Left Join (cost=651,538.79..856,500.26 rows=6,519 width=139) (actual time=440.647..440.647 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[]))
25. 0.000 440.646 ↓ 0.0 0 1

Hash Left Join (cost=371,415.19..400,031.22 rows=89,701 width=130) (actual time=440.646..440.646 rows=0 loops=1)

  • Hash Cond: (CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END = tpr.prescribed_id)
26. 0.638 440.646 ↓ 0.0 0 1

Hash Right Join (cost=96,267.60..96,601.95 rows=89,701 width=115) (actual time=440.646..440.646 rows=0 loops=1)

  • Hash Cond: (sr.sample_collection_id = sc.sample_collection_id)
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on sample_rejections sr (cost=0.00..263.25 rows=17,025 width=8) (never executed)

28. 0.000 440.008 ↓ 0.0 0 1

Hash (cost=95,146.34..95,146.34 rows=89,701 width=111) (actual time=440.008..440.008 rows=0 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 1024kB
29. 0.037 440.008 ↓ 0.0 0 1

Hash Left Join (cost=2.40..95,146.34 rows=89,701 width=111) (actual time=440.008..440.008 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))
  • Rows Removed by Filter: 1
30. 439.939 439.939 ↑ 101,117.0 1 1

Seq Scan on sample_collection sc (cost=0.00..94,545.29 rows=101,117 width=101) (actual time=400.945..439.939 rows=1 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-31'::date))
  • Rows Removed by Filter: 2103506
31. 0.018 0.032 ↑ 1.0 62 1

Hash (cost=1.62..1.62 rows=62 width=16) (actual time=0.032..0.032 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
32. 0.014 0.014 ↑ 1.0 62 1

Seq Scan on diag_outsource_master dom (cost=0.00..1.62 rows=62 width=16) (actual time=0.009..0.014 rows=62 loops=1)

33. 0.000 0.000 ↓ 0.0 0

Hash (cost=186,572.93..186,572.93 rows=5,095,493 width=15) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Seq Scan on tests_prescribed tpr (cost=0.00..186,572.93 rows=5,095,493 width=15) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Hash (cost=186,572.93..186,572.93 rows=5,095,493 width=19) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Seq Scan on tests_prescribed tp (cost=0.00..186,572.93 rows=5,095,493 width=19) (never executed)

37. 0.488 0.859 ↑ 1.0 1,725 1

Hash (cost=61.25..61.25 rows=1,725 width=49) (actual time=0.859..0.859 rows=1,725 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 153kB
38. 0.371 0.371 ↑ 1.0 1,725 1

Seq Scan on diagnostics d (cost=0.00..61.25 rows=1,725 width=49) (actual time=0.008..0.371 rows=1,725 loops=1)

39. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.55..1.55 rows=55 width=24) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Seq Scan on outhouse_master om (cost=0.00..1.55 rows=55 width=24) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.23..2.23 rows=23 width=222) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Seq Scan on hospital_center_master hcm (cost=0.00..2.23 rows=23 width=222) (never executed)

43. 0.000 0.000 ↓ 0.0 0

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

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

Hash (cost=4,077.54..4,077.54 rows=140,254 width=62) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Seq Scan on incoming_sample_registration isr (cost=0.00..4,077.54 rows=140,254 width=62) (never executed)

46. 0.006 0.013 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=356) (actual time=0.013..0.013 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.007 0.007 ↑ 1.0 15 1

Seq Scan on diagnostics_departments (cost=0.00..1.15 rows=15 width=356) (actual time=0.005..0.007 rows=15 loops=1)

48. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..3.00 rows=1 width=48) (never executed)

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

Hash (cost=1.21..1.21 rows=21 width=156) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Seq Scan on salutation_master sm (cost=0.00..1.21 rows=21 width=156) (never executed)

51. 0.000 0.000 ↓ 0.0 0

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

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

Hash (cost=1.15..1.15 rows=15 width=266) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Seq Scan on incoming_hospitals ih (cost=0.00..1.15 rows=15 width=266) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.45..5.75 rows=1 width=17) (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 tests_prescribed_pkey on tests_prescribed tp1 (cost=0.43..3.43 rows=1 width=8) (never executed)

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

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

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

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..3.53 rows=1 width=30) (never executed)

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

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

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
59. 0.016 0.035 ↑ 1.0 49 1

Hash (cost=1.49..1.49 rows=49 width=162) (actual time=0.035..0.035 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
60. 0.019 0.019 ↑ 1.0 49 1

Seq Scan on sample_type st (cost=0.00..1.49 rows=49 width=162) (actual time=0.013..0.019 rows=49 loops=1)

61. 0.000 0.000 ↓ 0.0 0

Hash (cost=15.00..15.00 rows=500 width=122) (never executed)

62. 0.000 0.000 ↓ 0.0 0

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