explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pJsP

Settings
# exclusive inclusive rows x rows loops node
1. 235.543 161,089.684 ↓ 361.0 86,647 1

Sort (cost=297,223.15..297,223.75 rows=240 width=487) (actual time=161,080.962..161,089.684 rows=86,647 loops=1)

  • Sort Key: ddep.ddept_name
  • Sort Method: quicksort Memory: 45282kB
2. 2,207.166 160,854.141 ↓ 361.0 86,647 1

Nested Loop Left Join (cost=4.40..297,206.46 rows=240 width=487) (actual time=29.831..160,854.141 rows=86,647 loops=1)

  • Join Filter: ((tp.conducted)::text = (dsm.value)::text)
  • Rows Removed by Join Filter: 983252
3. 59.627 137,591.754 ↓ 361.0 86,647 1

Nested Loop Left Join (cost=4.40..293,883.37 rows=240 width=269) (actual time=27.919..137,591.754 rows=86,647 loops=1)

4. 110.622 137,098.892 ↓ 361.0 86,647 1

Nested Loop Left Join (cost=4.12..293,802.91 rows=240 width=249) (actual time=27.884..137,098.892 rows=86,647 loops=1)

5. 109.629 136,468.388 ↓ 361.0 86,647 1

Nested Loop Left Join (cost=3.84..293,726.01 rows=240 width=234) (actual time=27.846..136,468.388 rows=86,647 loops=1)

  • Join Filter: ((sm.salutation_id)::text = (pd.salutation)::text)
  • Rows Removed by Join Filter: 122453
6. 82.316 136,358.759 ↓ 361.0 86,647 1

Nested Loop Left Join (cost=3.84..293,555.28 rows=240 width=238) (actual time=27.830..136,358.759 rows=86,647 loops=1)

7. 115.385 134,630.150 ↓ 361.0 86,647 1

Nested Loop (cost=3.41..293,436.77 rows=240 width=204) (actual time=27.737..134,630.150 rows=86,647 loops=1)

  • Join Filter: ((pr.center_id = hcm.center_id) OR (isr.center_id = hcm.center_id))
  • Rows Removed by Join Filter: 384668
8. 0.067 0.067 ↑ 1.0 1 1

Seq Scan on hospital_center_master hcm (cost=0.00..5.50 rows=1 width=22) (actual time=0.033..0.067 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMCAbuDhabi'::text)
  • Rows Removed by Filter: 45
9. 260.175 134,514.698 ↓ 84.5 471,315 1

Nested Loop Left Join (cost=3.41..293,236.12 rows=5,576 width=194) (actual time=4.043..134,514.698 rows=471,315 loops=1)

10. 864.079 131,897.948 ↓ 84.5 471,315 1

Nested Loop Left Join (cost=2.99..288,791.32 rows=5,576 width=170) (actual time=4.023..131,897.948 rows=471,315 loops=1)

11. 544.036 124,435.459 ↓ 84.5 471,315 1

Nested Loop Left Join (cost=2.43..267,890.50 rows=5,576 width=149) (actual time=3.952..124,435.459 rows=471,315 loops=1)

12. 415.968 121,534.848 ↓ 84.5 471,315 1

Nested Loop Left Join (cost=1.99..252,645.28 rows=5,576 width=137) (actual time=3.914..121,534.848 rows=471,315 loops=1)

13. 448.207 118,762.305 ↓ 84.5 471,315 1

Nested Loop Left Join (cost=1.71..250,845.99 rows=5,576 width=144) (actual time=3.857..118,762.305 rows=471,315 loops=1)

14. 532.461 100,404.128 ↓ 84.5 471,315 1

Nested Loop Left Join (cost=1.27..231,939.83 rows=5,576 width=129) (actual time=3.203..100,404.128 rows=471,315 loops=1)

15. 255.928 92,801.942 ↓ 84.5 471,315 1

Nested Loop (cost=0.84..213,896.55 rows=5,576 width=121) (actual time=2.330..92,801.942 rows=471,315 loops=1)

16. 3.252 6.840 ↓ 7.7 1,246 1

Nested Loop (cost=0.28..310.92 rows=162 width=60) (actual time=1.594..6.840 rows=1,246 loops=1)

  • Join Filter: ((diag.ddept_id)::text = (ddep.ddept_id)::text)
  • Rows Removed by Join Filter: 5789
17. 3.588 3.588 ↑ 1.0 1,941 1

Index Scan using diagnostics_pkey on diagnostics diag (cost=0.28..241.56 rows=1,941 width=49) (actual time=0.013..3.588 rows=1,941 loops=1)

18. 0.000 0.000 ↓ 4.0 4 1,941

Materialize (cost=0.00..1.42 rows=1 width=31) (actual time=0.000..0.000 rows=4 loops=1,941)

19. 0.011 0.011 ↓ 6.0 6 1

Seq Scan on diagnostics_departments ddep (cost=0.00..1.42 rows=1 width=31) (actual time=0.009..0.011 rows=6 loops=1)

  • Filter: (CASE WHEN ((category)::text = 'DEP_LAB'::text) THEN 'Lab'::text ELSE 'Radiology'::text END = 'Lab'::text)
  • Rows Removed by Filter: 6
20. 92,539.174 92,539.174 ↓ 4.4 378 1,246

Index Scan using tests_prescribed_test_id_idx on tests_prescribed tp (cost=0.56..1,315.88 rows=85 width=77) (actual time=0.331..74.269 rows=378 loops=1,246)

  • Index Cond: ((test_id)::text = (diag.test_id)::text)
  • Filter: ((date(pres_date) >= '2020-01-03'::date) AND (date(pres_date) <= '2020-02-02'::date))
  • Rows Removed by Filter: 9286
21. 7,069.725 7,069.725 ↑ 1.0 1 471,315

Index Scan using tests_conducted_index on tests_conducted tc (cost=0.43..3.21 rows=1 width=12) (actual time=0.015..0.015 rows=1 loops=471,315)

  • Index Cond: (tp.prescribed_id = prescribed_id)
22. 17,909.970 17,909.970 ↑ 1.0 1 471,315

Index Scan using test_visit_reports_pkey on test_visit_reports tvr (cost=0.43..3.39 rows=1 width=23) (actual time=0.038..0.038 rows=1 loops=471,315)

  • Index Cond: (tp.report_id = report_id)
23. 2,356.575 2,356.575 ↑ 1.0 1 471,315

Index Scan using u_user_pk on u_user usr (cost=0.29..0.32 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=471,315)

  • Index Cond: ((tvr.user_name)::text = (emp_username)::text)
24. 2,356.575 2,356.575 ↑ 1.0 1 471,315

Index Scan using sample_collection_id_pkey on sample_collection sc (cost=0.43..2.73 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=471,315)

  • Index Cond: (sample_collection_id = tp.sample_collection_id)
25. 6,598.410 6,598.410 ↑ 1.0 1 471,315

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..3.75 rows=1 width=37) (actual time=0.014..0.014 rows=1 loops=471,315)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
26. 2,356.575 2,356.575 ↓ 0.0 0 471,315

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.42..0.80 rows=1 width=39) (actual time=0.005..0.005 rows=0 loops=471,315)

  • Index Cond: ((incoming_visit_id)::text = (tp.pat_id)::text)
27. 1,646.293 1,646.293 ↑ 1.0 1 86,647

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.49 rows=1 width=49) (actual time=0.019..0.019 rows=1 loops=86,647)

  • Index Cond: ((pr.mr_no)::text = (mr_no)::text)
28. 0.000 0.000 ↑ 10.5 2 86,647

Materialize (cost=0.00..1.73 rows=21 width=14) (actual time=0.000..0.000 rows=2 loops=86,647)

29. 0.194 0.194 ↑ 1.0 21 1

Seq Scan on salutation_master sm (cost=0.00..1.63 rows=21 width=14) (actual time=0.007..0.194 rows=21 loops=1)

30. 519.882 519.882 ↑ 1.0 1 86,647

Index Scan using doctors_pkey on doctors doc (cost=0.28..0.32 rows=1 width=30) (actual time=0.006..0.006 rows=1 loops=86,647)

  • Index Cond: ((doctor_id)::text = (tp.pres_doctor)::text)
31. 433.235 433.235 ↑ 1.0 1 86,647

Index Scan using doctors_pkey on doctors cdoc (cost=0.28..0.34 rows=1 width=30) (actual time=0.005..0.005 rows=1 loops=86,647)

  • Index Cond: ((doctor_id)::text = (usr.doctor_id)::text)
32. 86.616 86.647 ↑ 1.5 12 86,647

Materialize (cost=0.00..1.63 rows=18 width=20) (actual time=0.000..0.001 rows=12 loops=86,647)

33. 0.031 0.031 ↑ 1.1 16 1

Seq Scan on diag_states_master dsm (cost=0.00..1.54 rows=18 width=20) (actual time=0.007..0.031 rows=16 loops=1)

34.          

SubPlan (for Nested Loop Left Join)

35. 20,968.574 20,968.574 ↓ 0.0 0 86,647

Index Scan using test_detail_prescribed_id_index on test_details td (cost=0.56..12.97 rows=1 width=0) (actual time=0.242..0.242 rows=0 loops=86,647)

  • Index Cond: (prescribed_id = tp.prescribed_id)
  • Filter: (original_test_details_id <> 0)
  • Rows Removed by Filter: 3
36. 0.000 0.000 ↓ 0.0 0

Seq Scan on test_details td_1 (cost=0.00..2,396,444.86 rows=5,377 width=4) (never executed)

  • Filter: (original_test_details_id <> 0)
Planning time : 81.819 ms
Execution time : 161,098.717 ms