explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BcRf

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 3,029.069 ↓ 20.0 20 1

Limit (cost=609,475.83..609,475.83 rows=1 width=602) (actual time=3,028.904..3,029.069 rows=20 loops=1)

2. 0.220 3,029.065 ↓ 21.0 21 1

GroupAggregate (cost=609,474.74..609,475.83 rows=1 width=602) (actual time=3,028.893..3,029.065 rows=21 loops=1)

3. 0.159 3,028.845 ↓ 24.0 24 1

Sort (cost=609,474.74..609,474.75 rows=1 width=602) (actual time=3,028.844..3,028.845 rows=24 loops=1)

  • Sort Key: foo.sample_date_time, foo.sample_sno, foo.sample_assertion_batch, foo.sample_type, foo.sample_date, 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.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: 45kB
4. 0.008 3,028.686 ↓ 61.0 61 1

Subquery Scan on foo (cost=609,474.21..609,474.73 rows=1 width=602) (actual time=3,028.657..3,028.686 rows=61 loops=1)

5. 0.411 3,028.678 ↓ 61.0 61 1

HashAggregate (cost=609,474.21..609,474.72 rows=1 width=366) (actual time=3,028.656..3,028.678 rows=61 loops=1)

6. 6.885 3,028.267 ↓ 161.0 161 1

Nested Loop (cost=144,115.65..609,474.12 rows=1 width=366) (actual time=2,238.183..3,028.267 rows=161 loops=1)

7. 0.144 3,021.060 ↓ 161.0 161 1

Nested Loop (cost=144,115.39..609,471.85 rows=1 width=351) (actual time=2,237.993..3,021.060 rows=161 loops=1)

8. 0.082 3,019.950 ↓ 161.0 161 1

Nested Loop (cost=144,114.83..609,469.81 rows=1 width=363) (actual time=2,237.978..3,019.950 rows=161 loops=1)

9. 0.140 3,018.902 ↓ 161.0 161 1

Nested Loop (cost=144,114.27..609,467.27 rows=1 width=353) (actual time=2,237.963..3,018.902 rows=161 loops=1)

10. 0.103 3,017.957 ↓ 161.0 161 1

Nested Loop Left Join (cost=144,113.82..609,463.99 rows=1 width=351) (actual time=2,237.946..3,017.957 rows=161 loops=1)

11. 0.105 3,017.854 ↓ 161.0 161 1

Nested Loop Left Join (cost=144,113.39..609,462.20 rows=1 width=346) (actual time=2,237.943..3,017.854 rows=161 loops=1)

12. 0.266 3,017.749 ↓ 161.0 161 1

Nested Loop Left Join (cost=144,112.96..609,459.66 rows=1 width=346) (actual time=2,237.940..3,017.749 rows=161 loops=1)

  • Join Filter: (scc.collection_center_id = pr.collection_center_id)
13. 0.100 3,017.483 ↓ 161.0 161 1

Nested Loop Left Join (cost=144,112.96..609,458.64 rows=1 width=337) (actual time=2,237.927..3,017.483 rows=161 loops=1)

14. 0.147 3,017.383 ↓ 161.0 161 1

Nested Loop Left Join (cost=144,112.82..609,458.48 rows=1 width=334) (actual time=2,237.925..3,017.383 rows=161 loops=1)

  • Join Filter: ((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text)
15. 0.053 3,017.075 ↓ 161.0 161 1

Nested Loop Left Join (cost=144,112.39..609,456.73 rows=1 width=317) (actual time=2,237.915..3,017.075 rows=161 loops=1)

  • Filter: (COALESCE(pr.center_id, isr.center_id) = 13)
  • Rows Removed by Filter: 1612
16. 1.436 3,013.476 ↓ 1,773.0 1,773 1

Nested Loop Left Join (cost=144,111.97..609,454.91 rows=1 width=270) (actual time=2,237.901..3,013.476 rows=1,773 loops=1)

17. 0.323 3,010.267 ↓ 1,773.0 1,773 1

Nested Loop Left Join (cost=144,111.84..609,454.75 rows=1 width=274) (actual time=2,237.893..3,010.267 rows=1,773 loops=1)

18. 1.217 2,979.803 ↓ 1,773.0 1,773 1

Nested Loop Left Join (cost=144,111.41..609,452.92 rows=1 width=241) (actual time=2,237.875..2,979.803 rows=1,773 loops=1)

19. 1.276 2,969.721 ↓ 1,773.0 1,773 1

Nested Loop (cost=144,110.97..609,448.13 rows=1 width=200) (actual time=2,237.859..2,969.721 rows=1,773 loops=1)

20. 0.453 2,966.672 ↓ 1,773.0 1,773 1

Hash Left Join (cost=144,110.84..609,447.96 rows=1 width=187) (actual time=2,237.851..2,966.672 rows=1,773 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
21. 0.959 2,966.201 ↓ 1,773.0 1,773 1

Nested Loop Left Join (cost=144,106.32..609,443.43 rows=1 width=184) (actual time=2,237.821..2,966.201 rows=1,773 loops=1)

22. 0.766 2,963.469 ↓ 1,773.0 1,773 1

Nested Loop (cost=144,106.18..609,443.08 rows=1 width=171) (actual time=2,237.811..2,963.469 rows=1,773 loops=1)

23. 1.036 2,948.519 ↓ 1,773.0 1,773 1

Nested Loop Left Join (cost=144,105.90..609,441.50 rows=1 width=138) (actual time=2,237.533..2,948.519 rows=1,773 loops=1)

  • Filter: ((CASE WHEN (sc.sample_status = 'R'::bpchar) THEN tpr.conducted ELSE tp.conducted END)::text = ANY ('{N,NRN}'::text[]))
  • Rows Removed by Filter: 104
24. 0.342 2,945.168 ↓ 231.5 463 1

Nested Loop Left Join (cost=144,105.47..609,415.08 rows=2 width=129) (actual time=2,237.264..2,945.168 rows=463 loops=1)

25. 0.178 2,924.454 ↓ 231.5 463 1

Nested Loop Left Join (cost=144,105.03..609,400.43 rows=2 width=114) (actual time=2,236.394..2,924.454 rows=463 loops=1)

26. 1,824.267 2,923.813 ↓ 231.5 463 1

Hash Join (cost=144,104.74..609,397.28 rows=2 width=110) (actual time=2,236.376..2,923.813 rows=463 loops=1)

  • Hash Cond: (((tp_1.sample_no)::text = (sc.sample_sno)::text) AND ((tp_1.mr_no)::text = (sc.mr_no)::text))
27. 516.142 516.142 ↓ 1.0 7,554,345 1

Seq Scan on tests_prescribed tp_1 (cost=0.00..276,500.72 rows=7,551,672 width=28) (actual time=0.004..516.142 rows=7,554,345 loops=1)

28. 0.136 583.404 ↑ 521.7 254 1

Hash (cost=142,117.18..142,117.18 rows=132,504 width=125) (actual time=583.404..583.404 rows=254 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 30kB
29. 0.800 583.268 ↑ 515.6 257 1

Hash Left Join (cost=2.51..142,117.18 rows=132,504 width=125) (actual time=516.716..583.268 rows=257 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: 3891
30. 582.446 582.446 ↑ 36.3 4,148 1

Seq Scan on sample_collection sc (cost=0.00..141,225.46 rows=150,471 width=116) (actual time=359.531..582.446 rows=4,148 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 >= '2018-12-29'::date))
  • Rows Removed by Filter: 3095660
31. 0.008 0.022 ↑ 1.0 67 1

Hash (cost=1.67..1.67 rows=67 width=15) (actual time=0.022..0.022 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
32. 0.014 0.014 ↑ 1.0 67 1

Seq Scan on diag_outsource_master dom (cost=0.00..1.67 rows=67 width=15) (actual time=0.003..0.014 rows=67 loops=1)

33. 0.463 0.463 ↓ 0.0 0 463

Index Scan using idx_sample_rejections_sample_collection_id on sample_rejections sr (cost=0.29..1.55 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=463)

  • Index Cond: (sc.sample_collection_id = sample_collection_id)
34. 20.372 20.372 ↑ 1.0 1 463

Index Scan using tests_prescribed_pkey on tests_prescribed tpr (cost=0.43..7.31 rows=1 width=15) (actual time=0.044..0.044 rows=1 loops=463)

  • Index Cond: (prescribed_id = CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END)
35. 2.315 2.315 ↑ 11.2 4 463

Index Scan using idx_tests_prescribed_sample_collection_id on tests_prescribed tp (cost=0.43..12.54 rows=45 width=19) (actual time=0.004..0.005 rows=4 loops=463)

  • Index Cond: (sample_collection_id = CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END)
36. 14.184 14.184 ↑ 1.0 1 1,773

Index Scan using diagnostics_pkey on diagnostics d (cost=0.28..1.57 rows=1 width=49) (actual time=0.008..0.008 rows=1 loops=1,773)

  • Index Cond: ((COALESCE(tp.test_id, tpr.test_id))::text = (test_id)::text)
37. 1.773 1.773 ↑ 1.0 1 1,773

Index Scan using outhouse_master_pkey on outhouse_master om (cost=0.14..0.34 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=1,773)

  • Index Cond: ((oh_id)::text = (dom.outsource_dest)::text)
38. 0.010 0.018 ↑ 1.0 23 1

Hash (cost=4.23..4.23 rows=23 width=16) (actual time=0.018..0.018 rows=23 loops=1)

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

Seq Scan on hospital_center_master hcm (cost=0.00..4.23 rows=23 width=16) (actual time=0.003..0.008 rows=23 loops=1)

40. 1.773 1.773 ↑ 1.0 1 1,773

Index Scan using diagnostics_departments_pkey on diagnostics_departments (cost=0.14..0.16 rows=1 width=23) (actual time=0.001..0.001 rows=1 loops=1,773)

  • Index Cond: ((ddept_id)::text = (d.ddept_id)::text)
41. 8.865 8.865 ↑ 1.0 1 1,773

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..4.78 rows=1 width=41) (actual time=0.005..0.005 rows=1 loops=1,773)

  • Index Cond: ((patient_id)::text = (sc.patient_id)::text)
42. 30.141 30.141 ↑ 1.0 1 1,773

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..1.82 rows=1 width=48) (actual time=0.017..0.017 rows=1 loops=1,773)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
43. 1.773 1.773 ↑ 1.0 1 1,773

Index Scan using salutation_master_pkey on salutation_master sm (cost=0.14..0.16 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=1,773)

  • Index Cond: ((salutation_id)::text = (pd.salutation)::text)
44. 3.546 3.546 ↓ 0.0 0 1,773

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.42..1.80 rows=1 width=62) (actual time=0.002..0.002 rows=0 loops=1,773)

  • Index Cond: ((sc.patient_id)::text = (incoming_visit_id)::text)
45. 0.161 0.161 ↓ 0.0 0 161

Index Scan using incoming_sample_registration_details_pkey on incoming_sample_registration_details isrd (cost=0.42..1.73 rows=1 width=30) (actual time=0.001..0.001 rows=0 loops=161)

  • Index Cond: (tp.prescribed_id = prescribed_id)
46. 0.000 0.000 ↓ 0.0 0 161

Index Scan using incoming_hospitals_pkey on incoming_hospitals ih (cost=0.14..0.16 rows=1 width=17) (actual time=0.000..0.000 rows=0 loops=161)

  • Index Cond: ((hospital_id)::text = (isr.orig_lab_name)::text)
47. 0.000 0.000 ↑ 1.0 1 161

Seq Scan on sample_collection_centers scc (cost=0.00..1.01 rows=1 width=13) (actual time=0.000..0.000 rows=1 loops=161)

48. 0.000 0.000 ↓ 0.0 0 161

Index Scan using tests_prescribed_pkey on tests_prescribed tp1 (cost=0.43..2.53 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=161)

  • Index Cond: (prescribed_id = isrd.source_test_prescribed)
49. 0.000 0.000 ↓ 0.0 0 161

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

  • Index Cond: (sample_collection_id = tp1.sample_collection_id)
50. 0.805 0.805 ↑ 1.0 1 161

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.45..3.27 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=161)

  • Index Cond: (COALESCE((tp.prescribed_id)::text, (tpr.prescribed_id)::text) = (activity_id)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
51. 0.966 0.966 ↑ 1.0 1 161

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..2.52 rows=1 width=30) (actual time=0.006..0.006 rows=1 loops=161)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
52. 0.966 0.966 ↑ 1.0 1 161

Index Scan using bill_pkey on bill b (cost=0.56..2.03 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=161)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
53. 0.322 0.322 ↑ 1.0 1 161

Index Scan using sample_type_pkey on sample_type st (cost=0.27..1.75 rows=1 width=19) (actual time=0.001..0.002 rows=1 loops=161)

  • Index Cond: (sample_type_id = sc.sample_type_id)