explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fDUR

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 158.078 ↑ 10.0 2 1

Limit (cost=50,626.76..137,813.37 rows=20 width=1,578) (actual time=157.990..158.078 rows=2 loops=1)

2. 0.109 158.076 ↑ 151.5 2 1

Group (cost=50,626.76..1,371,503.81 rows=303 width=1,578) (actual time=157.989..158.076 rows=2 loops=1)

3. 0.022 157.745 ↑ 151.5 2 1

Sort (cost=50,626.76..50,627.52 rows=303 width=1,578) (actual time=157.745..157.745 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.008 157.723 ↑ 151.5 2 1

Hash Left Join (cost=353.19..50,614.28 rows=303 width=1,578) (actual time=157.481..157.723 rows=2 loops=1)

  • Hash Cond: (oh.outsource_dest_id = dom.outsource_dest_id)
5. 0.008 157.711 ↑ 67.5 2 1

Hash Left Join (cost=332.62..50,581.64 rows=135 width=1,582) (actual time=157.469..157.711 rows=2 loops=1)

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

Nested Loop Left Join (cost=316.38..50,563.58 rows=135 width=1,589) (actual time=157.340..157.581 rows=2 loops=1)

7. 0.003 157.580 ↑ 67.5 2 1

Nested Loop Left Join (cost=315.96..49,602.30 rows=135 width=1,585) (actual time=157.339..157.580 rows=2 loops=1)

8. 0.008 157.575 ↑ 67.5 2 1

Nested Loop Left Join (cost=315.53..48,639.71 rows=135 width=1,580) (actual time=157.336..157.575 rows=2 loops=1)

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

Hash Left Join (cost=315.53..48,597.96 rows=135 width=882) (actual time=157.322..157.557 rows=2 loops=1)

  • Hash Cond: ((tp.pat_id)::text = (oh.visit_id)::text)
10. 0.002 155.848 ↑ 67.5 2 1

Hash Left Join (cost=89.53..48,352.14 rows=135 width=878) (actual time=155.614..155.848 rows=2 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (tpa.tpa_id)::text)
11. 0.004 155.722 ↑ 67.5 2 1

Nested Loop Left Join (cost=73.30..48,334.08 rows=135 width=885) (actual time=155.488..155.722 rows=2 loops=1)

12. 0.006 155.216 ↑ 67.5 2 1

Nested Loop Left Join (cost=72.88..47,340.98 rows=135 width=865) (actual time=155.189..155.216 rows=2 loops=1)

13. 0.001 155.190 ↑ 67.5 2 1

Nested Loop Left Join (cost=72.45..46,379.69 rows=135 width=828) (actual time=155.172..155.190 rows=2 loops=1)

  • Join Filter: (him.impression_id = thr.impression_id)
14. 0.001 155.187 ↑ 67.5 2 1

Nested Loop Left Join (cost=72.45..46,165.94 rows=135 width=610) (actual time=155.170..155.187 rows=2 loops=1)

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

Hash Join (cost=72.45..46,115.19 rows=135 width=610) (actual time=155.167..155.184 rows=2 loops=1)

  • Hash Cond: ((tp.test_id)::text = (d.test_id)::text)
16. 0.005 154.511 ↑ 1,125.3 3 1

Nested Loop (cost=0.42..46,029.16 rows=3,376 width=618) (actual time=154.497..154.511 rows=3 loops=1)

17. 154.488 154.488 ↑ 734.3 3 1

Seq Scan on test_visit_reports tv (cost=0.00..21,700.90 rows=2,203 width=583) (actual time=154.483..154.488 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. 0.018 0.018 ↑ 6.0 1 3

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

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

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

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

Hash Join (cost=1.32..71.06 rows=78 width=8) (actual time=0.023..0.508 rows=1,218 loops=1)

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

Seq Scan on diagnostics d (cost=0.00..61.60 rows=1,960 width=18) (actual time=0.002..0.147 rows=1,960 loops=1)

22. 0.007 0.010 ↓ 15.0 15 1

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

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

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

  • Filter: ((category)::text = 'DEP_LAB'::text)
  • Rows Removed by Filter: 10
24. 0.001 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.001 0.001 ↓ 0.0 0 1

Seq Scan on test_histopathology_results thr (cost=0.00..10.20 rows=20 width=8) (actual time=0.001..0.001 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.020 0.020 ↑ 1.0 1 2

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

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

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..7.35 rows=1 width=40) (actual time=0.250..0.251 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.037 0.124 ↑ 1.0 366 1

Hash (cost=11.66..11.66 rows=366 width=12) (actual time=0.124..0.124 rows=366 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
31. 0.087 0.087 ↑ 1.0 366 1

Seq Scan on tpa_master tpa (cost=0.00..11.66 rows=366 width=12) (actual time=0.002..0.087 rows=366 loops=1)

32. 0.905 1.706 ↑ 1.0 7,111 1

Hash (cost=137.11..137.11 rows=7,111 width=13) (actual time=1.706..1.706 rows=7,111 loops=1)

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

Seq Scan on outsource_sample_details oh (cost=0.00..137.11 rows=7,111 width=13) (actual time=0.002..0.801 rows=7,111 loops=1)

34. 0.005 0.010 ↑ 1.0 20 2

Materialize (cost=0.00..1.30 rows=20 width=964) (actual time=0.002..0.005 rows=20 loops=2)

35. 0.005 0.005 ↑ 1.0 20 1

Seq Scan on incoming_sample_registration isr (cost=0.00..1.20 rows=20 width=964) (actual time=0.002..0.005 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.12 rows=1 width=13) (actual time=0.001..0.001 rows=0 loops=2)

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

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

  • Index Cond: ((patient_id)::text = (itp.pat_id)::text)
38. 0.045 0.122 ↑ 1.0 366 1

Hash (cost=11.66..11.66 rows=366 width=12) (actual time=0.122..0.122 rows=366 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
39. 0.077 0.077 ↑ 1.0 366 1

Seq Scan on tpa_master itpa (cost=0.00..11.66 rows=366 width=12) (actual time=0.001..0.077 rows=366 loops=1)

40. 0.002 0.004 ↑ 47.0 10 1

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

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

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

42.          

SubPlan (forGroup)

43. 0.000 0.222 ↑ 1.0 1 2

Limit (cost=4,358.92..4,359.01 rows=1 width=32) (actual time=0.111..0.111 rows=1 loops=2)

44. 0.002 0.222 ↑ 1.0 1 2

Subquery Scan on foo (cost=4,358.92..4,359.01 rows=1 width=32) (actual time=0.111..0.111 rows=1 loops=2)

45. 0.006 0.220 ↑ 1.0 1 2

HashAggregate (cost=4,358.92..4,359.00 rows=1 width=160) (actual time=0.110..0.110 rows=1 loops=2)

46. 0.006 0.214 ↑ 1.0 1 2

Nested Loop (cost=4,120.39..4,358.90 rows=1 width=160) (actual time=0.105..0.107 rows=1 loops=2)

47. 0.010 0.192 ↑ 1.0 1 2

Nested Loop Left Join (cost=4,119.97..4,350.39 rows=1 width=160) (actual time=0.095..0.096 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.002 0.086 ↑ 2.0 1 2

Nested Loop (cost=1.72..203.64 rows=2 width=48) (actual time=0.042..0.043 rows=1 loops=2)

49. 0.002 0.050 ↑ 6.0 1 2

Nested Loop (cost=1.29..156.70 rows=6 width=12) (actual time=0.025..0.025 rows=1 loops=2)

50. 0.004 0.028 ↑ 6.0 1 2

Nested Loop (cost=0.86..109.81 rows=6 width=13) (actual time=0.013..0.014 rows=1 loops=2)

51. 0.006 0.006 ↑ 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.002..0.003 rows=1 loops=2)

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

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

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

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

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

Index Scan using bill_pkey on bill b (cost=0.43..7.81 rows=1 width=44) (actual time=0.017..0.017 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.006 0.006 ↑ 1.0 1 2

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

57. 0.003 0.092 ↑ 20.0 10 2

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

58. 0.013 0.089 ↑ 20.0 10 1

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

59. 0.015 0.076 ↑ 33.3 15 1

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

60. 0.001 0.001 ↑ 33.3 15 1

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

61. 0.060 0.060 ↑ 1.0 1 15

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

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

SubPlan (forNested Loop Left Join)

63. 0.004 0.004 ↑ 1.0 1 2

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

64. 0.008 0.008 ↑ 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.004..0.004 rows=1 loops=2)

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

SubPlan (forNested Loop)

66. 0.004 0.004 ↑ 1.0 1 2

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

67. 0.004 0.004 ↑ 1.0 1 2

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