explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xLUs

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 4,523.882 ↓ 20.0 20 1

Limit (cost=609,468.77..609,468.77 rows=1 width=602) (actual time=4,523.704..4,523.882 rows=20 loops=1)

2. 0.246 4,523.879 ↓ 21.0 21 1

GroupAggregate (cost=609,467.68..609,468.77 rows=1 width=602) (actual time=4,523.693..4,523.879 rows=21 loops=1)

3. 0.159 4,523.633 ↓ 24.0 24 1

Sort (cost=609,467.68..609,467.69 rows=1 width=602) (actual time=4,523.629..4,523.633 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.007 4,523.474 ↓ 61.0 61 1

Subquery Scan on foo (cost=609,467.15..609,467.67 rows=1 width=602) (actual time=4,523.450..4,523.474 rows=61 loops=1)

5. 1.114 4,523.467 ↓ 61.0 61 1

HashAggregate (cost=609,467.15..609,467.66 rows=1 width=366) (actual time=4,523.449..4,523.467 rows=61 loops=1)

6. 18.873 4,522.353 ↓ 161.0 161 1

Nested Loop (cost=144,120.86..609,467.05 rows=1 width=366) (actual time=1,698.036..4,522.353 rows=161 loops=1)

7. 0.340 4,502.675 ↓ 161.0 161 1

Nested Loop (cost=144,120.60..609,464.78 rows=1 width=351) (actual time=1,697.845..4,502.675 rows=161 loops=1)

8. 0.290 4,499.759 ↓ 161.0 161 1

Nested Loop (cost=144,120.04..609,462.74 rows=1 width=363) (actual time=1,697.827..4,499.759 rows=161 loops=1)

9. 0.382 4,497.054 ↓ 161.0 161 1

Nested Loop (cost=144,119.48..609,460.21 rows=1 width=353) (actual time=1,697.808..4,497.054 rows=161 loops=1)

10. 0.246 4,494.579 ↓ 161.0 161 1

Nested Loop Left Join (cost=144,119.03..609,456.93 rows=1 width=351) (actual time=1,697.791..4,494.579 rows=161 loops=1)

11. 0.246 4,494.333 ↓ 161.0 161 1

Nested Loop Left Join (cost=144,118.60..609,455.14 rows=1 width=346) (actual time=1,697.788..4,494.333 rows=161 loops=1)

12. 0.447 4,494.087 ↓ 161.0 161 1

Nested Loop Left Join (cost=144,118.17..609,452.60 rows=1 width=346) (actual time=1,697.786..4,494.087 rows=161 loops=1)

  • Join Filter: (scc.collection_center_id = pr.collection_center_id)
13. 0.241 4,493.318 ↓ 161.0 161 1

Nested Loop Left Join (cost=144,118.17..609,451.58 rows=1 width=337) (actual time=1,697.778..4,493.318 rows=161 loops=1)

14. 0.182 4,493.077 ↓ 161.0 161 1

Nested Loop Left Join (cost=144,118.03..609,451.41 rows=1 width=334) (actual time=1,697.776..4,493.077 rows=161 loops=1)

  • Join Filter: ((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text)
15. 1.329 4,492.251 ↓ 161.0 161 1

Nested Loop Left Join (cost=144,117.60..609,449.67 rows=1 width=317) (actual time=1,697.767..4,492.251 rows=161 loops=1)

  • Filter: (COALESCE(pr.center_id, isr.center_id) = 13)
  • Rows Removed by Filter: 1612
16. 1.813 4,482.057 ↓ 1,773.0 1,773 1

Nested Loop Left Join (cost=144,117.18..609,447.85 rows=1 width=270) (actual time=1,697.756..4,482.057 rows=1,773 loops=1)

17. 1.120 4,473.152 ↓ 1,773.0 1,773 1

Nested Loop Left Join (cost=144,117.05..609,447.69 rows=1 width=274) (actual time=1,697.748..4,473.152 rows=1,773 loops=1)

18. 3.046 4,443.664 ↓ 1,773.0 1,773 1

Nested Loop Left Join (cost=144,116.62..609,445.86 rows=1 width=241) (actual time=1,697.730..4,443.664 rows=1,773 loops=1)

19. 1.528 4,415.796 ↓ 1,773.0 1,773 1

Nested Loop (cost=144,116.18..609,441.07 rows=1 width=200) (actual time=1,697.712..4,415.796 rows=1,773 loops=1)

20. 1.296 4,407.176 ↓ 1,773.0 1,773 1

Hash Left Join (cost=144,116.05..609,440.90 rows=1 width=187) (actual time=1,697.704..4,407.176 rows=1,773 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
21. 2.119 4,405.858 ↓ 1,773.0 1,773 1

Nested Loop Left Join (cost=144,111.53..609,436.36 rows=1 width=184) (actual time=1,697.670..4,405.858 rows=1,773 loops=1)

22. 1.758 4,398.420 ↓ 1,773.0 1,773 1

Nested Loop (cost=144,111.39..609,436.02 rows=1 width=171) (actual time=1,697.661..4,398.420 rows=1,773 loops=1)

23. 4.186 4,382.478 ↓ 1,773.0 1,773 1

Nested Loop Left Join (cost=144,111.11..609,434.44 rows=1 width=138) (actual time=1,697.649..4,382.478 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. 4.074 4,370.784 ↓ 938.5 1,877 1

Nested Loop Left Join (cost=144,110.68..609,419.78 rows=2 width=129) (actual time=1,697.628..4,370.784 rows=1,877 loops=1)

  • Join Filter: (tp.sample_collection_id = CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END)
25. 0.453 3,056.420 ↓ 231.5 463 1

Nested Loop Left Join (cost=144,105.03..609,400.43 rows=2 width=129) (actual time=1,682.917..3,056.420 rows=463 loops=1)

26. 1,867.221 3,054.578 ↓ 231.5 463 1

Hash Join (cost=144,104.74..609,397.28 rows=2 width=125) (actual time=1,682.902..3,054.578 rows=463 loops=1)

  • Hash Cond: (((tp1_1.sample_no)::text = (sc.sample_sno)::text) AND ((tp1_1.mr_no)::text = (sc.mr_no)::text))
27. 586.667 586.667 ↓ 1.0 7,554,334 1

Seq Scan on tests_prescribed tp1_1 (cost=0.00..276,500.72 rows=7,551,672 width=28) (actual time=0.003..586.667 rows=7,554,334 loops=1)

28. 0.155 600.690 ↑ 521.7 254 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 30kB
29. 0.878 600.535 ↑ 515.6 257 1

Hash Left Join (cost=2.51..142,117.18 rows=132,504 width=125) (actual time=530.393..600.535 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. 599.636 599.636 ↑ 36.3 4,148 1

Seq Scan on sample_collection sc (cost=0.00..141,225.46 rows=150,471 width=116) (actual time=366.497..599.636 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.011 0.021 ↑ 1.0 67 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
32. 0.010 0.010 ↑ 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.010 rows=67 loops=1)

33. 1.389 1.389 ↓ 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.003..0.003 rows=0 loops=463)

  • Index Cond: (sc.sample_collection_id = sample_collection_id)
34. 2.315 1,310.290 ↓ 4.0 4 463

Bitmap Heap Scan on tests_prescribed tp (cost=5.65..9.66 rows=1 width=47) (actual time=2.828..2.830 rows=4 loops=463)

  • Recheck Cond: (((mr_no)::text = (sc.mr_no)::text) AND ((sample_no)::text = (sc.sample_sno)::text))
35. 2.315 1,307.975 ↓ 0.0 0 463

BitmapAnd (cost=5.65..5.65 rows=1 width=0) (actual time=2.825..2.825 rows=0 loops=463)

36. 1,225.561 1,225.561 ↑ 1.8 25 463

Bitmap Index Scan on tests_prescribed_mrno_idx (cost=0.00..2.28 rows=45 width=0) (actual time=2.647..2.647 rows=25 loops=463)

  • Index Cond: ((mr_no)::text = (sc.mr_no)::text)
37. 80.099 80.099 ↑ 11.2 4 463

Bitmap Index Scan on idx_tests_prescribed_sample_no (cost=0.00..3.12 rows=45 width=0) (actual time=0.173..0.173 rows=4 loops=463)

  • Index Cond: ((sample_no)::text = (sc.sample_sno)::text)
38. 7.508 7.508 ↑ 1.0 1 1,877

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

  • Index Cond: (prescribed_id = CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END)
39. 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.007..0.008 rows=1 loops=1,773)

  • Index Cond: ((COALESCE(tp.test_id, tpr.test_id))::text = (test_id)::text)
40. 5.319 5.319 ↑ 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.003..0.003 rows=1 loops=1,773)

  • Index Cond: ((oh_id)::text = (dom.outsource_dest)::text)
41. 0.008 0.022 ↑ 1.0 23 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
42. 0.014 0.014 ↑ 1.0 23 1

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

43. 7.092 7.092 ↑ 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.003..0.004 rows=1 loops=1,773)

  • Index Cond: ((ddept_id)::text = (d.ddept_id)::text)
44. 24.822 24.822 ↑ 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.013..0.014 rows=1 loops=1,773)

  • Index Cond: ((patient_id)::text = (sc.patient_id)::text)
45. 28.368 28.368 ↑ 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.015..0.016 rows=1 loops=1,773)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
46. 7.092 7.092 ↑ 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.004..0.004 rows=1 loops=1,773)

  • Index Cond: ((salutation_id)::text = (pd.salutation)::text)
47. 8.865 8.865 ↓ 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.005..0.005 rows=0 loops=1,773)

  • Index Cond: ((sc.patient_id)::text = (incoming_visit_id)::text)
48. 0.644 0.644 ↓ 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.004..0.004 rows=0 loops=161)

  • Index Cond: (tp.prescribed_id = prescribed_id)
49. 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)
50. 0.322 0.322 ↑ 1.0 1 161

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

51. 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)
52. 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)
53. 2.093 2.093 ↑ 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.013..0.013 rows=1 loops=161)

  • Index Cond: (COALESCE((tp.prescribed_id)::text, (tpr.prescribed_id)::text) = (activity_id)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
54. 2.415 2.415 ↑ 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.015..0.015 rows=1 loops=161)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
55. 2.576 2.576 ↑ 1.0 1 161

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

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
56. 0.805 0.805 ↑ 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.003..0.005 rows=1 loops=161)

  • Index Cond: (sample_type_id = sc.sample_type_id)