explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FWK4 : Prod-new

Settings
# exclusive inclusive rows x rows loops node
1. 0.086 12,360.646 ↑ 1.5 130 1

Sort (cost=898,619.40..898,619.50 rows=200 width=602) (actual time=12,360.640..12,360.646 rows=130 loops=1)

  • Sort Key: sc.sample_date
  • Sort Method: quicksort Memory: 90kB
2. 4.389 12,360.560 ↑ 1.5 130 1

HashAggregate (cost=898,617.27..898,617.87 rows=200 width=602) (actual time=12,360.515..12,360.560 rows=130 loops=1)

3. 0.840 12,356.171 ↓ 1.8 364 1

HashAggregate (cost=898,552.01..898,573.12 rows=203 width=366) (actual time=12,356.022..12,356.171 rows=364 loops=1)

4. 15.381 12,355.331 ↓ 1.8 364 1

Nested Loop (cost=423,954.39..898,548.25 rows=203 width=366) (actual time=12,181.335..12,355.331 rows=364 loops=1)

5. 0.287 12,339.222 ↓ 1.8 364 1

Nested Loop (cost=423,954.34..898,514.94 rows=203 width=351) (actual time=12,181.092..12,339.222 rows=364 loops=1)

6. 0.118 12,334.931 ↓ 1.8 364 1

Nested Loop (cost=423,954.23..898,485.62 rows=203 width=363) (actual time=12,181.064..12,334.931 rows=364 loops=1)

7. 0.112 12,331.537 ↓ 1.8 364 1

Nested Loop Left Join (cost=423,954.12..898,381.48 rows=203 width=353) (actual time=12,181.037..12,331.537 rows=364 loops=1)

8. 0.016 12,330.697 ↓ 1.8 364 1

Nested Loop Left Join (cost=423,954.03..898,358.23 rows=203 width=348) (actual time=12,181.032..12,330.697 rows=364 loops=1)

9. 0.143 12,329.589 ↓ 1.8 364 1

Hash Left Join (cost=423,953.95..898,217.39 rows=203 width=348) (actual time=12,181.028..12,329.589 rows=364 loops=1)

  • Hash Cond: (pr.collection_center_id = scc.collection_center_id)
10. 0.265 12,329.441 ↓ 1.8 364 1

Nested Loop (cost=423,952.94..898,215.63 rows=203 width=339) (actual time=12,181.011..12,329.441 rows=364 loops=1)

11. 0.155 12,326.992 ↓ 1.6 364 1

Hash Left Join (cost=423,952.85..898,170.08 rows=230 width=337) (actual time=12,180.983..12,326.992 rows=364 loops=1)

  • Hash Cond: ((isr.orig_lab_name)::text = (ih.hospital_id)::text)
12. 0.248 12,326.823 ↓ 1.6 364 1

Nested Loop Left Join (cost=423,951.73..898,168.10 rows=230 width=334) (actual time=12,180.963..12,326.823 rows=364 loops=1)

  • Join Filter: ((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text)
13. 0.132 12,325.847 ↓ 1.6 364 1

Hash Left Join (cost=423,951.65..898,146.16 rows=230 width=317) (actual time=12,180.949..12,325.847 rows=364 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
14. 0.240 12,325.702 ↓ 1.6 364 1

Nested Loop Left Join (cost=423,950.51..898,144.16 rows=230 width=321) (actual time=12,180.920..12,325.702 rows=364 loops=1)

15. 0.207 12,324.370 ↓ 1.6 364 1

Hash Join (cost=423,950.42..898,113.37 rows=230 width=288) (actual time=12,180.891..12,324.370 rows=364 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (diagnostics_departments.ddept_id)::text)
16. 0.158 12,324.154 ↓ 1.6 364 1

Nested Loop Left Join (cost=423,949.35..898,111.43 rows=230 width=275) (actual time=12,180.868..12,324.154 rows=364 loops=1)

  • Filter: (COALESCE(pr.center_id, isr.center_id) = 9)
  • Rows Removed by Filter: 388
17. 0.578 12,320.988 ↑ 61.1 752 1

Nested Loop Left Join (cost=423,949.26..890,225.16 rows=45,950 width=228) (actual time=12,163.502..12,320.988 rows=752 loops=1)

18. 0.197 12,315.146 ↑ 61.1 752 1

Hash Left Join (cost=423,949.18..784,745.38 rows=45,950 width=187) (actual time=12,163.472..12,315.146 rows=752 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
19. 0.259 12,314.924 ↑ 61.1 752 1

Hash Left Join (cost=423,945.03..784,636.47 rows=45,950 width=184) (actual time=12,163.440..12,314.924 rows=752 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
20. 0.611 12,314.632 ↑ 61.1 752 1

Hash Join (cost=423,943.64..784,479.23 rows=45,950 width=171) (actual time=12,163.389..12,314.632 rows=752 loops=1)

  • Hash Cond: ((COALESCE(tp.test_id, tpr.test_id))::text = (d.test_id)::text)
21. 1,752.534 12,312.834 ↑ 61.1 752 1

Merge Right Join (cost=423,421.93..783,773.72 rows=45,950 width=138) (actual time=12,162.191..12,312.834 rows=752 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: 103
22. 4,241.593 4,241.593 ↑ 1.2 6,567,138 1

Index Scan using idx_tests_prescribed_sample_collection_id on tests_prescribed tp (cost=0.09..327,414.58 rows=7,664,605 width=19) (actual time=0.011..4,241.593 rows=6,567,138 loops=1)

23. 0.380 6,318.707 ↑ 136.7 854 1

Sort (cost=423,421.84..423,480.22 rows=116,761 width=129) (actual time=6,318.553..6,318.707 rows=854 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: 84kB
24. 2,195.636 6,318.327 ↑ 278.0 420 1

Hash Left Join (cost=378,769.58..421,456.38 rows=116,761 width=129) (actual time=4,122.805..6,318.327 rows=420 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)
25. 2.137 739.315 ↑ 278.0 420 1

Hash Right Join (cost=87,349.65..87,629.74 rows=116,761 width=114) (actual time=739.125..739.315 rows=420 loops=1)

  • Hash Cond: (sr.sample_collection_id = sc.sample_collection_id)
26. 1.701 1.701 ↓ 1.0 25,963 1

Seq Scan on sample_rejections sr (cost=0.00..218.83 rows=25,944 width=8) (actual time=0.007..1.701 rows=25,963 loops=1)

27. 0.172 735.477 ↑ 278.0 420 1

Hash (cost=86,940.98..86,940.98 rows=116,761 width=110) (actual time=735.477..735.477 rows=420 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 44kB
28. 1.283 735.305 ↑ 278.0 420 1

Hash Left Join (cost=1.44..86,940.98 rows=116,761 width=110) (actual time=553.812..735.305 rows=420 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: 6631
29. 734.001 734.001 ↑ 18.8 7,051 1

Seq Scan on sample_collection sc (cost=0.00..86,760.10 rows=132,593 width=101) (actual time=483.691..734.001 rows=7,051 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-05'::date))
  • Rows Removed by Filter: 3140733
30. 0.011 0.021 ↑ 1.0 67 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
31. 0.010 0.010 ↑ 1.0 67 1

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

32. 1,666.696 3,383.376 ↓ 1.0 7,672,096 1

Hash (cost=227,168.82..227,168.82 rows=7,664,605 width=15) (actual time=3,383.376..3,383.376 rows=7,672,096 loops=1)

  • Buckets: 262144 Batches: 8 Memory Usage: 46886kB
33. 1,716.680 1,716.680 ↓ 1.0 7,672,096 1

Seq Scan on tests_prescribed tpr (cost=0.00..227,168.82 rows=7,664,605 width=15) (actual time=0.006..1,716.680 rows=7,672,096 loops=1)

34. 0.370 1.187 ↑ 1.0 1,802 1

Hash (cost=515.41..515.41 rows=1,802 width=49) (actual time=1.187..1.187 rows=1,802 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 143kB
35. 0.817 0.817 ↑ 1.0 1,802 1

Seq Scan on diagnostics d (cost=0.00..515.41 rows=1,802 width=49) (actual time=0.005..0.817 rows=1,802 loops=1)

36. 0.014 0.033 ↑ 1.0 59 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
37. 0.019 0.019 ↑ 1.0 59 1

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

38. 0.012 0.025 ↑ 1.0 23 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
39. 0.013 0.013 ↑ 1.0 23 1

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

40. 5.264 5.264 ↑ 1.0 1 752

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.09..2.29 rows=1 width=41) (actual time=0.006..0.007 rows=1 loops=752)

  • Index Cond: ((patient_id)::text = (sc.patient_id)::text)
41. 3.008 3.008 ↓ 0.0 0 752

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

  • Index Cond: ((sc.patient_id)::text = (incoming_visit_id)::text)
42. 0.004 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
43. 0.005 0.005 ↑ 1.0 12 1

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

44. 1.092 1.092 ↓ 0.0 0 364

Index Scan using patient_details_pkey on patient_details pd (cost=0.09..0.13 rows=1 width=48) (actual time=0.002..0.003 rows=0 loops=364)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
45. 0.006 0.013 ↑ 1.0 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
46. 0.007 0.007 ↑ 1.0 21 1

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

47. 0.728 0.728 ↑ 1.0 1 364

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.002..0.002 rows=1 loops=364)

  • Index Cond: (tp.prescribed_id = prescribed_id)
48. 0.007 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
49. 0.007 0.007 ↑ 1.0 18 1

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

50. 2.184 2.184 ↑ 1.0 1 364

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.09..0.20 rows=1 width=17) (actual time=0.006..0.006 rows=1 loops=364)

  • Index Cond: (COALESCE((tp.prescribed_id)::text, (tpr.prescribed_id)::text) = (activity_id)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
  • Rows Removed by Filter: 0
51. 0.001 0.005 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
52. 0.004 0.004 ↑ 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.004 rows=1 loops=1)

53. 1.092 1.092 ↑ 1.0 1 364

Index Scan using tests_prescribed_pkey on tests_prescribed tp1 (cost=0.09..0.69 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=364)

  • Index Cond: (prescribed_id = isrd.source_test_prescribed)
54. 0.728 0.728 ↑ 1.0 1 364

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

  • Index Cond: (sample_collection_id = tp1.sample_collection_id)
55. 3.276 3.276 ↑ 1.0 1 364

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.11..0.51 rows=1 width=30) (actual time=0.008..0.009 rows=1 loops=364)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
56. 4.004 4.004 ↑ 1.0 1 364

Index Scan using bill_pkey on bill b (cost=0.11..0.14 rows=1 width=16) (actual time=0.007..0.011 rows=1 loops=364)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
57. 0.728 0.728 ↑ 1.0 1 364

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

  • Index Cond: (sample_type_id = sc.sample_type_id)