explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m5o4

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 12,427.059 ↑ 4.0 5 1

Limit (cost=894,806.02..894,810.49 rows=20 width=602) (actual time=12,426.986..12,427.059 rows=5 loops=1)

2. 0.253 12,427.058 ↑ 40.0 5 1

GroupAggregate (cost=894,806.02..894,850.70 rows=200 width=602) (actual time=12,426.985..12,427.058 rows=5 loops=1)

3. 0.124 12,426.805 ↑ 15.8 13 1

Sort (cost=894,806.02..894,806.13 rows=205 width=602) (actual time=12,426.803..12,426.805 rows=13 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.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
  • Sort Method: quicksort Memory: 28kB
4. 0.002 12,426.681 ↑ 15.8 13 1

Subquery Scan on foo (cost=894,782.51..894,804.45 rows=205 width=602) (actual time=12,426.674..12,426.681 rows=13 loops=1)

5. 0.052 12,426.679 ↑ 15.8 13 1

HashAggregate (cost=894,782.51..894,803.83 rows=205 width=366) (actual time=12,426.673..12,426.679 rows=13 loops=1)

6. 2.808 12,426.627 ↑ 15.8 13 1

Nested Loop (cost=422,558.43..894,778.72 rows=205 width=366) (actual time=12,421.264..12,426.627 rows=13 loops=1)

7. 0.018 12,423.754 ↑ 15.8 13 1

Nested Loop (cost=422,558.38..894,745.07 rows=205 width=351) (actual time=12,420.048..12,423.754 rows=13 loops=1)

8. 0.017 12,423.333 ↑ 15.8 13 1

Nested Loop (cost=422,558.27..894,715.46 rows=205 width=363) (actual time=12,419.985..12,423.333 rows=13 loops=1)

9. 0.017 12,423.082 ↑ 15.8 13 1

Nested Loop Left Join (cost=422,558.16..894,610.88 rows=205 width=353) (actual time=12,419.944..12,423.082 rows=13 loops=1)

10. 0.016 12,423.065 ↑ 15.8 13 1

Nested Loop Left Join (cost=422,558.07..894,587.39 rows=205 width=348) (actual time=12,419.940..12,423.065 rows=13 loops=1)

11. 0.027 12,423.049 ↑ 15.8 13 1

Hash Left Join (cost=422,557.98..894,445.01 rows=205 width=348) (actual time=12,419.936..12,423.049 rows=13 loops=1)

  • Hash Cond: (pr.collection_center_id = scc.collection_center_id)
12. 0.013 12,423.019 ↑ 15.8 13 1

Nested Loop (cost=422,556.98..894,443.24 rows=205 width=339) (actual time=12,419.915..12,423.019 rows=13 loops=1)

13. 0.015 12,422.850 ↑ 17.5 13 1

Hash Left Join (cost=422,556.89..894,398.10 rows=228 width=337) (actual time=12,419.876..12,422.850 rows=13 loops=1)

  • Hash Cond: ((isr.orig_lab_name)::text = (ih.hospital_id)::text)
14. 0.015 12,422.821 ↑ 17.5 13 1

Nested Loop Left Join (cost=422,555.77..894,396.12 rows=228 width=334) (actual time=12,419.853..12,422.821 rows=13 loops=1)

  • Join Filter: ((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text)
15. 0.025 12,422.767 ↑ 17.5 13 1

Hash Left Join (cost=422,555.69..894,374.37 rows=228 width=317) (actual time=12,419.835..12,422.767 rows=13 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
16. 0.010 12,422.731 ↑ 17.5 13 1

Nested Loop Left Join (cost=422,554.55..894,372.38 rows=228 width=321) (actual time=12,419.806..12,422.731 rows=13 loops=1)

17. 0.034 12,422.526 ↑ 17.5 13 1

Hash Join (cost=422,554.46..894,341.83 rows=228 width=288) (actual time=12,419.760..12,422.526 rows=13 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (diagnostics_departments.ddept_id)::text)
18. 0.346 12,422.483 ↑ 17.5 13 1

Nested Loop Left Join (cost=422,553.39..894,339.90 rows=228 width=275) (actual time=12,419.729..12,422.483 rows=13 loops=1)

  • Filter: (COALESCE(pr.center_id, isr.center_id) = 12)
  • Rows Removed by Filter: 531
19. 0.453 12,421.049 ↑ 84.0 544 1

Nested Loop Left Join (cost=422,553.30..886,475.63 rows=45,692 width=228) (actual time=12,317.930..12,421.049 rows=544 loops=1)

20. 0.124 12,415.156 ↑ 84.0 544 1

Hash Left Join (cost=422,553.22..781,569.41 rows=45,692 width=187) (actual time=12,317.816..12,415.156 rows=544 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
21. 0.154 12,414.999 ↑ 84.0 544 1

Hash Left Join (cost=422,549.07..781,461.09 rows=45,692 width=184) (actual time=12,317.777..12,414.999 rows=544 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
22. 0.408 12,414.823 ↑ 84.0 544 1

Hash Join (cost=422,547.68..781,304.73 rows=45,692 width=171) (actual time=12,317.742..12,414.823 rows=544 loops=1)

  • Hash Cond: ((COALESCE(tp.test_id, tpr.test_id))::text = (d.test_id)::text)
23. 1,696.014 12,411.787 ↑ 84.0 544 1

Merge Right Join (cost=422,025.98..780,600.25 rows=45,692 width=138) (actual time=12,315.098..12,411.787 rows=544 loops=1)

  • Merge Cond: (tp.sample_collection_id = (CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END))
  • Filter: ((CASE WHEN (sc.sample_status = 'R'::bpchar) THEN tpr.conducted ELSE tp.conducted END)::text = ANY ('{N,NRN}'::text[]))
  • Rows Removed by Filter: 147
24. 3,848.866 3,848.866 ↑ 1.2 6,537,589 1

Index Scan using idx_tests_prescribed_sample_collection_id on tests_prescribed tp (cost=0.09..325,806.47 rows=7,637,421 width=19) (actual time=0.012..3,848.866 rows=6,537,589 loops=1)

25. 0.308 6,866.907 ↑ 167.8 691 1

Sort (cost=422,025.89..422,083.86 rows=115,940 width=129) (actual time=6,866.795..6,866.907 rows=691 loops=1)

  • Sort Key: (CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END)
  • Sort Method: quicksort Memory: 78kB
26. 2,300.423 6,866.599 ↑ 310.0 374 1

Hash Left Join (cost=377,555.08..420,075.43 rows=115,940 width=129) (actual time=4,566.954..6,866.599 rows=374 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)
27. 2.214 1,048.538 ↑ 310.0 374 1

Hash Right Join (cost=86,983.85..87,263.64 rows=115,940 width=114) (actual time=1,048.413..1,048.538 rows=374 loops=1)

  • Hash Cond: (sr.sample_collection_id = sc.sample_collection_id)
28. 2.283 2.283 ↑ 1.0 25,890 1

Seq Scan on sample_rejections sr (cost=0.00..218.67 rows=25,890 width=8) (actual time=0.007..2.283 rows=25,890 loops=1)

29. 0.292 1,044.041 ↑ 310.0 374 1

Hash (cost=86,578.06..86,578.06 rows=115,940 width=110) (actual time=1,044.041..1,044.041 rows=374 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 39kB
30. 1.931 1,043.749 ↑ 310.0 374 1

Hash Left Join (cost=1.44..86,578.06 rows=115,940 width=110) (actual time=740.140..1,043.749 rows=374 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: 6603
31. 1,041.788 1,041.788 ↑ 18.9 6,977 1

Seq Scan on sample_collection sc (cost=0.00..86,399.35 rows=131,661 width=101) (actual time=629.362..1,041.788 rows=6,977 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 >= '2019-01-01'::date))
  • Rows Removed by Filter: 3127259
32. 0.015 0.030 ↑ 1.0 67 1

Hash (cost=1.20..1.20 rows=67 width=15) (actual time=0.030..0.030 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
33. 0.015 0.015 ↑ 1.0 67 1

Seq Scan on diag_outsource_master dom (cost=0.00..1.20 rows=67 width=15) (actual time=0.010..0.015 rows=67 loops=1)

34. 1,502.595 3,517.638 ↓ 1.0 7,637,703 1

Hash (cost=226,547.26..226,547.26 rows=7,637,421 width=15) (actual time=3,517.638..3,517.638 rows=7,637,703 loops=1)

  • Buckets: 262144 Batches: 8 Memory Usage: 46677kB
35. 2,015.043 2,015.043 ↓ 1.0 7,637,703 1

Seq Scan on tests_prescribed tpr (cost=0.00..226,547.26 rows=7,637,421 width=15) (actual time=0.022..2,015.043 rows=7,637,703 loops=1)

36. 0.415 2.628 ↑ 1.0 1,801 1

Hash (cost=515.40..515.40 rows=1,801 width=49) (actual time=2.628..2.628 rows=1,801 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 143kB
37. 2.213 2.213 ↑ 1.0 1,801 1

Seq Scan on diagnostics d (cost=0.00..515.40 rows=1,801 width=49) (actual time=0.028..2.213 rows=1,801 loops=1)

38. 0.010 0.022 ↑ 1.0 59 1

Hash (cost=1.18..1.18 rows=59 width=24) (actual time=0.022..0.022 rows=59 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
39. 0.012 0.012 ↑ 1.0 59 1

Seq Scan on outhouse_master om (cost=0.00..1.18 rows=59 width=24) (actual time=0.007..0.012 rows=59 loops=1)

40. 0.007 0.033 ↑ 1.0 23 1

Hash (cost=4.07..4.07 rows=23 width=16) (actual time=0.033..0.033 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
41. 0.026 0.026 ↑ 1.0 23 1

Seq Scan on hospital_center_master hcm (cost=0.00..4.07 rows=23 width=16) (actual time=0.018..0.026 rows=23 loops=1)

42. 5.440 5.440 ↑ 1.0 1 544

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.09..2.29 rows=1 width=41) (actual time=0.009..0.010 rows=1 loops=544)

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

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.08..0.17 rows=1 width=62) (actual time=0.002..0.002 rows=0 loops=544)

  • Index Cond: ((sc.patient_id)::text = (incoming_visit_id)::text)
44. 0.003 0.009 ↑ 1.0 12 1

Hash (cost=1.04..1.04 rows=12 width=23) (actual time=0.009..0.009 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
45. 0.006 0.006 ↑ 1.0 12 1

Seq Scan on diagnostics_departments (cost=0.00..1.04 rows=12 width=23) (actual time=0.005..0.006 rows=12 loops=1)

46. 0.195 0.195 ↑ 1.0 1 13

Index Scan using patient_details_pkey on patient_details pd (cost=0.09..0.13 rows=1 width=48) (actual time=0.012..0.015 rows=1 loops=13)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
47. 0.007 0.011 ↑ 1.0 21 1

Hash (cost=1.06..1.06 rows=21 width=14) (actual time=0.011..0.011 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
48. 0.004 0.004 ↑ 1.0 21 1

Seq Scan on salutation_master sm (cost=0.00..1.06 rows=21 width=14) (actual time=0.004..0.004 rows=21 loops=1)

49. 0.039 0.039 ↓ 0.0 0 13

Index Scan using incoming_sample_registration_details_pkey on incoming_sample_registration_details isrd (cost=0.08..0.09 rows=1 width=30) (actual time=0.003..0.003 rows=0 loops=13)

  • Index Cond: (tp.prescribed_id = prescribed_id)
50. 0.008 0.014 ↑ 1.0 18 1

Hash (cost=1.05..1.05 rows=18 width=17) (actual time=0.014..0.014 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
51. 0.006 0.006 ↑ 1.0 18 1

Seq Scan on incoming_hospitals ih (cost=0.00..1.05 rows=18 width=17) (actual time=0.003..0.006 rows=18 loops=1)

52. 0.156 0.156 ↑ 1.0 1 13

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.09..0.19 rows=1 width=17) (actual time=0.011..0.012 rows=1 loops=13)

  • Index Cond: (COALESCE((tp.prescribed_id)::text, (tpr.prescribed_id)::text) = (activity_id)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
53. 0.000 0.003 ↑ 1.0 1 1

Hash (cost=1.00..1.00 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
54. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on sample_collection_centers scc (cost=0.00..1.00 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=1)

55. 0.000 0.000 ↓ 0.0 0 13

Index Scan using tests_prescribed_pkey on tests_prescribed tp1 (cost=0.09..0.69 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=13)

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

Index Scan using sample_collection_id_pkey on sample_collection sc1 (cost=0.09..0.11 rows=1 width=13) (actual time=0.000..0.000 rows=0 loops=13)

  • Index Cond: (sample_collection_id = tp1.sample_collection_id)
57. 0.234 0.234 ↑ 1.0 1 13

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.11..0.51 rows=1 width=30) (actual time=0.012..0.018 rows=1 loops=13)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
58. 0.403 0.403 ↑ 1.0 1 13

Index Scan using bill_pkey on bill b (cost=0.11..0.14 rows=1 width=16) (actual time=0.013..0.031 rows=1 loops=13)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
59. 0.065 0.065 ↑ 1.0 1 13

Index Scan using sample_type_pkey on sample_type st (cost=0.05..0.06 rows=1 width=19) (actual time=0.003..0.005 rows=1 loops=13)

  • Index Cond: (sample_type_id = sc.sample_type_id)