explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1TdO

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 1,230.167 ↑ 10.0 2 1

Limit (cost=50,951.92..138,450.33 rows=20 width=1,658) (actual time=1,229.960..1,230.167 rows=2 loops=1)

  • Total runtime: 1231.124 ms
2. 0.683 1,230.166 ↑ 151.5 2 1

Group (cost=50,951.92..1,376,552.76 rows=303 width=1,658) (actual time=1,229.959..1,230.166 rows=2 loops=1)

3. 0.062 1,140.907 ↑ 151.5 2 1

Sort (cost=50,951.92..50,952.68 rows=303 width=1,658) (actual time=1,140.906..1,140.907 rows=2 loops=1)

  • Sort Key: tv.report_id, tp.mr_no, isr.mr_no, pd.salutation, pd.patient_name, pd.middle_name, pd.last_name, tp.pat_id, pr.reference_docto_id, isr.referring_doctor, tv.report_date, tv.report_name, isr.patient_name, tv.addendum_signed_off, tv.handed_over, tv.handed_over_to, tv.hand_over_time, tv.num_prints, tv.report_addendum, him.impression_id, him.short_impression, ipr.center_id, pr.center_id, isr.incoming_source_type, tv.signoff_center, isr.center_id, (CASE WHEN (COALESCE(tpa.sponsor_type, itpa.sponsor_type, 'R'::bpchar) = 'R'::bpchar) THEN 'R'::text ELSE 'S'::text END), (CASE WHEN (pr.patient_id IS NOT NULL) THEN 'hospital'::text ELSE 'incoming'::text END)
  • Sort Method: quicksort Memory: 25kB
4. 0.012 1,140.845 ↑ 151.5 2 1

Hash Left Join (cost=350.78..50,939.43 rows=303 width=1,658) (actual time=1,140.443..1,140.845 rows=2 loops=1)

  • Hash Cond: (oh.outsource_dest_id = dom.outsource_dest_id)
5. 0.011 1,128.174 ↑ 67.5 2 1

Hash Left Join (cost=330.20..50,906.80 rows=135 width=1,662) (actual time=1,127.775..1,128.174 rows=2 loops=1)

  • Hash Cond: ((ipr.primary_sponsor_id)::text = (itpa.tpa_id)::text)
6. 0.001 1,128.026 ↑ 67.5 2 1

Nested Loop Left Join (cost=314.08..50,888.85 rows=135 width=1,669) (actual time=1,127.627..1,128.026 rows=2 loops=1)

7. 0.005 1,128.023 ↑ 67.5 2 1

Nested Loop Left Join (cost=313.66..49,908.40 rows=135 width=1,665) (actual time=1,127.625..1,128.023 rows=2 loops=1)

8. 0.006 1,128.016 ↑ 67.5 2 1

Nested Loop Left Join (cost=313.23..48,929.84 rows=135 width=1,660) (actual time=1,127.619..1,128.016 rows=2 loops=1)

  • Join Filter: ((isr.incoming_visit_id)::text = (tp.pat_id)::text)
  • Rows Removed by Join Filter: 40
9. 0.007 1,127.988 ↑ 67.5 2 1

Hash Left Join (cost=313.23..48,896.24 rows=135 width=962) (actual time=1,127.595..1,127.988 rows=2 loops=1)

  • Hash Cond: ((tp.pat_id)::text = (oh.visit_id)::text)
10. 0.003 1,098.560 ↑ 67.5 2 1

Hash Left Join (cost=87.26..48,650.45 rows=135 width=958) (actual time=1,098.168..1,098.560 rows=2 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (tpa.tpa_id)::text)
11. 0.006 1,098.412 ↑ 67.5 2 1

Nested Loop Left Join (cost=71.14..48,632.50 rows=135 width=965) (actual time=1,098.020..1,098.412 rows=2 loops=1)

12. 0.007 1,096.640 ↑ 67.5 2 1

Nested Loop Left Join (cost=70.71..47,621.00 rows=135 width=945) (actual time=1,096.592..1,096.640 rows=2 loops=1)

13. 0.001 1,096.599 ↑ 67.5 2 1

Nested Loop Left Join (cost=70.29..46,640.55 rows=135 width=908) (actual time=1,096.568..1,096.599 rows=2 loops=1)

  • Join Filter: (him.impression_id = thr.impression_id)
14. 0.005 1,096.596 ↑ 67.5 2 1

Nested Loop Left Join (cost=70.29..46,426.80 rows=135 width=690) (actual time=1,096.565..1,096.596 rows=2 loops=1)

  • Join Filter: (tp.prescribed_id = thr.prescribed_id)
15. 0.021 1,096.589 ↑ 67.5 2 1

Hash Join (cost=70.29..46,376.05 rows=135 width=690) (actual time=1,096.561..1,096.589 rows=2 loops=1)

  • Hash Cond: ((tp.test_id)::text = (d.test_id)::text)
16. 0.010 1,095.764 ↑ 1,127.0 3 1

Nested Loop (cost=0.42..46,292.15 rows=3,381 width=698) (actual time=1,095.738..1,095.764 rows=3 loops=1)

17. 1,085.377 1,085.377 ↑ 736.0 3 1

Seq Scan on test_visit_reports tv (cost=0.00..21,700.41 rows=2,208 width=663) (actual time=1,085.366..1,085.377 rows=3 loops=1)

  • Filter: ((report_state <> 'D'::bpchar) AND (handed_over = 'N'::bpchar) AND (signed_off = 'Y'::bpchar) AND (date(report_date) >= '2019-06-01'::date) AND (date(report_date) <= '2019-07-01'::date))
  • Rows Removed by Filter: 517157
18. 10.377 10.377 ↑ 6.0 1 3

Index Scan using tests_prescribed_report_idx on tests_prescribed tp (cost=0.42..11.08 rows=6 width=39) (actual time=3.459..3.459 rows=1 loops=3)

  • Index Cond: (report_id = tv.report_id)
  • Filter: ((conducted)::text <> 'RAS'::text)
19. 0.170 0.804 ↓ 15.6 1,218 1

Hash (cost=68.89..68.89 rows=78 width=8) (actual time=0.804..0.804 rows=1,218 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
20. 0.350 0.634 ↓ 15.6 1,218 1

Hash Join (cost=1.32..68.89 rows=78 width=8) (actual time=0.029..0.634 rows=1,218 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (dd.ddept_id)::text)
21. 0.266 0.266 ↓ 1.0 1,960 1

Seq Scan on diagnostics d (cost=0.00..59.48 rows=1,948 width=18) (actual time=0.003..0.266 rows=1,960 loops=1)

22. 0.004 0.018 ↓ 15.0 15 1

Hash (cost=1.31..1.31 rows=1 width=38) (actual time=0.018..0.018 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
23. 0.014 0.014 ↓ 15.0 15 1

Seq Scan on diagnostics_departments dd (cost=0.00..1.31 rows=1 width=38) (actual time=0.010..0.014 rows=15 loops=1)

  • Filter: ((category)::text = 'DEP_LAB'::text)
  • Rows Removed by Filter: 10
24. 0.002 0.002 ↓ 0.0 0 2

Materialize (cost=0.00..10.30 rows=20 width=8) (actual time=0.001..0.001 rows=0 loops=2)

25. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on test_histopathology_results thr (cost=0.00..10.20 rows=20 width=8) (actual time=0.000..0.000 rows=0 loops=1)

26. 0.002 0.002 ↓ 0.0 0 2

Materialize (cost=0.00..11.50 rows=100 width=222) (actual time=0.001..0.001 rows=0 loops=2)

27. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on histo_impression_master him (cost=0.00..11.00 rows=100 width=222) (actual time=0.000..0.000 rows=0 loops=1)

28. 0.034 0.034 ↑ 1.0 1 2

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..7.25 rows=1 width=37) (actual time=0.016..0.017 rows=1 loops=2)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
29. 1.766 1.766 ↑ 1.0 1 2

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..7.48 rows=1 width=40) (actual time=0.881..0.883 rows=1 loops=2)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
  • Filter: patient_confidentiality_check(COALESCE(patient_group, 0), mr_no)
30. 0.051 0.145 ↓ 1.0 366 1

Hash (cost=11.61..11.61 rows=361 width=12) (actual time=0.145..0.145 rows=366 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
31. 0.094 0.094 ↓ 1.0 366 1

Seq Scan on tpa_master tpa (cost=0.00..11.61 rows=361 width=12) (actual time=0.008..0.094 rows=366 loops=1)

32. 1.028 29.421 ↓ 1.0 7,111 1

Hash (cost=137.10..137.10 rows=7,110 width=13) (actual time=29.421..29.421 rows=7,111 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 332kB
33. 28.393 28.393 ↓ 1.0 7,111 1

Seq Scan on outsource_sample_details oh (cost=0.00..137.10 rows=7,110 width=13) (actual time=25.317..28.393 rows=7,111 loops=1)

34. 0.010 0.022 ↓ 1.2 20 2

Materialize (cost=0.00..1.24 rows=16 width=964) (actual time=0.005..0.011 rows=20 loops=2)

35. 0.012 0.012 ↓ 1.2 20 1

Seq Scan on incoming_sample_registration isr (cost=0.00..1.16 rows=16 width=964) (actual time=0.007..0.012 rows=20 loops=1)

36. 0.002 0.002 ↓ 0.0 0 2

Index Scan using tests_prescribed_pkey on tests_prescribed itp (cost=0.42..7.24 rows=1 width=13) (actual time=0.001..0.001 rows=0 loops=2)

  • Index Cond: (prescribed_id = tp.coll_prescribed_id)
37. 0.002 0.002 ↓ 0.0 0 2

Index Scan using patient_registration_pkey on patient_registration ipr (cost=0.42..7.25 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=2)

  • Index Cond: ((patient_id)::text = (itp.pat_id)::text)
38. 0.041 0.137 ↓ 1.0 366 1

Hash (cost=11.61..11.61 rows=361 width=12) (actual time=0.137..0.137 rows=366 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
39. 0.096 0.096 ↓ 1.0 366 1

Seq Scan on tpa_master itpa (cost=0.00..11.61 rows=361 width=12) (actual time=0.003..0.096 rows=366 loops=1)

40. 0.002 12.659 ↑ 47.0 10 1

Hash (cost=14.70..14.70 rows=470 width=122) (actual time=12.659..12.659 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
41. 12.657 12.657 ↑ 47.0 10 1

Seq Scan on diag_outsource_master dom (cost=0.00..14.70 rows=470 width=122) (actual time=12.656..12.657 rows=10 loops=1)

42.          

SubPlan (forGroup)

43. 0.000 88.576 ↑ 1.0 1 2

Limit (cost=4,374.51..4,374.60 rows=1 width=32) (actual time=44.288..44.288 rows=1 loops=2)

44. 0.004 88.576 ↑ 1.0 1 2

Subquery Scan on foo (cost=4,374.51..4,374.60 rows=1 width=32) (actual time=44.288..44.288 rows=1 loops=2)

45. 0.012 88.572 ↑ 1.0 1 2

HashAggregate (cost=4,374.51..4,374.59 rows=1 width=160) (actual time=44.286..44.286 rows=1 loops=2)

46. 0.010 88.560 ↑ 1.0 1 2

Nested Loop (cost=4,120.39..4,374.49 rows=1 width=160) (actual time=44.277..44.280 rows=1 loops=2)

47. 0.014 88.516 ↑ 1.0 1 2

Nested Loop Left Join (cost=4,119.97..4,365.98 rows=1 width=160) (actual time=44.255..44.258 rows=1 loops=2)

  • Join Filter: ((bcn.bill_no)::text = (b.bill_no)::text)
  • Rows Removed by Join Filter: 10
  • Filter: ((SubPlan 4) > 0::numeric)
48. 0.014 82.534 ↑ 2.0 1 2

Nested Loop (cost=1.72..219.23 rows=2 width=48) (actual time=41.264..41.267 rows=1 loops=2)

49. 0.010 53.324 ↑ 7.0 1 2

Nested Loop (cost=1.29..164.46 rows=7 width=12) (actual time=26.661..26.662 rows=1 loops=2)

50. 0.012 40.510 ↑ 7.0 1 2

Nested Loop (cost=0.86..109.75 rows=7 width=13) (actual time=20.254..20.255 rows=1 loops=2)

51. 0.008 0.008 ↑ 6.0 1 2

Index Scan using tests_prescribed_report_idx on tests_prescribed tp_1 (cost=0.42..12.38 rows=6 width=8) (actual time=0.004..0.004 rows=1 loops=2)

  • Index Cond: (report_id = tv.report_id)
  • Filter: ((coll_prescribed_id IS NULL) AND (mr_no IS NOT NULL))
52. 40.490 40.490 ↑ 1.0 1 2

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.43..16.22 rows=1 width=16) (actual time=20.245..20.245 rows=1 loops=2)

  • Index Cond: ((activity_id)::text = ((tp_1.prescribed_id)::character varying)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
53. 12.804 12.804 ↑ 1.0 1 2

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.43..7.81 rows=1 width=17) (actual time=6.402..6.402 rows=1 loops=2)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
54. 29.186 29.196 ↑ 1.0 1 2

Index Scan using bill_pkey on bill b (cost=0.43..7.81 rows=1 width=44) (actual time=14.597..14.598 rows=1 loops=2)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
  • Filter: ((visit_type = 'o'::bpchar) AND ((SubPlan 3) > 0::numeric))
55.          

SubPlan (forIndex Scan)

56. 0.010 0.010 ↑ 1.0 1 2

Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=2)

57. 0.002 5.960 ↑ 20.0 10 2

Materialize (cost=4,118.25..4,123.25 rows=200 width=112) (actual time=2.978..2.980 rows=10 loops=2)

58. 0.030 5.958 ↑ 20.0 10 1

HashAggregate (cost=4,118.25..4,120.25 rows=200 width=58) (actual time=5.954..5.958 rows=10 loops=1)

59. 0.031 5.928 ↑ 33.3 15 1

Nested Loop Left Join (cost=0.43..4,114.50 rows=500 width=58) (actual time=4.952..5.928 rows=15 loops=1)

60. 0.017 0.017 ↑ 33.3 15 1

Seq Scan on bill_credit_notes bcn (cost=0.00..15.00 rows=500 width=96) (actual time=0.014..0.017 rows=15 loops=1)

61. 5.880 5.880 ↑ 1.0 1 15

Index Scan using bill_pkey on bill cn (cost=0.43..8.19 rows=1 width=18) (actual time=0.392..0.392 rows=1 loops=15)

  • Index Cond: ((bcn.credit_note_bill_no)::text = (bill_no)::text)
62.          

SubPlan (forNested Loop Left Join)

63. 0.008 0.008 ↑ 1.0 1 2

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=2)

64. 0.022 0.022 ↑ 1.0 1 2

Index Only Scan using test_visit_reports_pkey on test_visit_reports tvr (cost=0.42..8.44 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=2)

  • Index Cond: (report_id = tv.report_id)
  • Heap Fetches: 2
65.          

SubPlan (forNested Loop)

66. 0.006 0.006 ↑ 1.0 1 2

Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=2)

67. 0.006 0.006 ↑ 1.0 1 2

Result (cost=0.00..0.04 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=2)