explain.depesz.com

PostgreSQL's explain analyze made readable

Result: upTI

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 9,795.807 ↑ 1.0 20 1

Limit (cost=878,588.35..878,593.09 rows=20 width=602) (actual time=9,795.429..9,795.807 rows=20 loops=1)

  • Total runtime: 9797.215 ms
2. 0.439 9,795.805 ↑ 9.5 21 1

GroupAggregate (cost=878,588.11..878,635.58 rows=200 width=602) (actual time=9,795.406..9,795.805 rows=21 loops=1)

3. 0.112 9,795.366 ↑ 9.5 23 1

Sort (cost=878,588.11..878,588.22 rows=218 width=602) (actual time=9,795.366..9,795.366 rows=23 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.009 9,795.254 ↑ 4.0 54 1

Subquery Scan on foo (cost=878,563.09..878,586.41 rows=218 width=602) (actual time=9,795.223..9,795.254 rows=54 loops=1)

5. 0.200 9,795.245 ↑ 4.0 54 1

HashAggregate (cost=878,563.09..878,585.76 rows=218 width=365) (actual time=9,795.222..9,795.245 rows=54 loops=1)

6. 5.678 9,795.045 ↑ 4.0 55 1

Nested Loop (cost=418,313.86..878,559.06 rows=218 width=365) (actual time=9,697.179..9,795.045 rows=55 loops=1)

7. 0.103 9,789.147 ↑ 4.0 55 1

Nested Loop (cost=418,313.81..878,523.27 rows=218 width=350) (actual time=9,696.969..9,789.147 rows=55 loops=1)

8. 0.081 9,787.339 ↑ 4.0 55 1

Nested Loop (cost=418,313.69..878,491.85 rows=218 width=362) (actual time=9,696.945..9,787.339 rows=55 loops=1)

9. 0.096 9,786.543 ↑ 4.0 55 1

Nested Loop (cost=418,313.58..878,381.20 rows=218 width=352) (actual time=9,696.921..9,786.543 rows=55 loops=1)

10. 0.056 9,785.897 ↑ 4.0 55 1

Nested Loop Left Join (cost=418,313.49..878,338.00 rows=218 width=350) (actual time=9,696.898..9,785.897 rows=55 loops=1)

11. 0.065 9,785.786 ↑ 4.0 55 1

Nested Loop Left Join (cost=418,313.41..878,313.03 rows=218 width=345) (actual time=9,696.895..9,785.786 rows=55 loops=1)

12. 0.059 9,785.666 ↑ 4.0 55 1

Hash Left Join (cost=418,313.32..878,162.26 rows=218 width=345) (actual time=9,696.890..9,785.666 rows=55 loops=1)

  • Hash Cond: (pr.collection_center_id = scc.collection_center_id)
13. 0.056 9,785.602 ↑ 4.0 55 1

Hash Left Join (cost=418,312.31..878,160.43 rows=218 width=336) (actual time=9,696.879..9,785.602 rows=55 loops=1)

  • Hash Cond: ((isr.orig_lab_name)::text = (ih.hospital_id)::text)
14. 0.083 9,785.538 ↑ 4.0 55 1

Nested Loop Left Join (cost=418,311.20..878,158.50 rows=218 width=333) (actual time=9,696.865..9,785.538 rows=55 loops=1)

  • Join Filter: ((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text)
15. 0.077 9,785.235 ↑ 4.0 55 1

Hash Left Join (cost=418,311.11..878,137.70 rows=218 width=316) (actual time=9,696.853..9,785.235 rows=55 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
16. 0.078 9,785.149 ↑ 4.0 55 1

Nested Loop Left Join (cost=418,309.97..878,135.75 rows=218 width=320) (actual time=9,696.832..9,785.149 rows=55 loops=1)

17. 0.073 9,784.411 ↑ 4.0 55 1

Hash Join (cost=418,309.89..878,106.46 rows=218 width=287) (actual time=9,696.812..9,784.411 rows=55 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (diagnostics_departments.ddept_id)::text)
18. 0.123 9,784.328 ↑ 4.0 55 1

Nested Loop Left Join (cost=418,308.81..878,104.56 rows=218 width=274) (actual time=9,696.795..9,784.328 rows=55 loops=1)

  • Filter: (COALESCE(pr.center_id, isr.center_id) = 13)
  • Rows Removed by Filter: 352
19. 0.247 9,782.984 ↑ 107.1 407 1

Nested Loop Left Join (cost=418,308.73..870,647.13 rows=43,592 width=227) (actual time=9,693.288..9,782.984 rows=407 loops=1)

20. 0.109 9,779.481 ↑ 107.1 407 1

Hash Left Join (cost=418,308.64..771,032.03 rows=43,592 width=187) (actual time=9,693.263..9,779.481 rows=407 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
21. 0.126 9,779.355 ↑ 107.1 407 1

Hash Left Join (cost=418,304.49..770,928.50 rows=43,592 width=184) (actual time=9,693.240..9,779.355 rows=407 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
22. 0.346 9,779.206 ↑ 107.1 407 1

Hash Join (cost=418,303.11..770,779.26 rows=43,592 width=171) (actual time=9,693.207..9,779.206 rows=407 loops=1)

  • Hash Cond: ((COALESCE(tp.test_id, tpr.test_id))::text = (d.test_id)::text)
23. 1,667.602 9,777.452 ↑ 107.1 407 1

Merge Right Join (cost=417,781.42..770,083.21 rows=43,592 width=138) (actual time=9,691.789..9,777.452 rows=407 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: 101
24. 3,636.731 3,636.731 ↑ 1.2 6,483,038 1

Index Scan using idx_tests_prescribed_sample_collection_id on tests_prescribed tp (cost=0.09..320,765.22 rows=7,561,017 width=19) (actual time=0.010..3,636.731 rows=6,483,038 loops=1)

25. 0.265 4,473.119 ↑ 227.5 508 1

Sort (cost=417,781.33..417,839.12 rows=115,574 width=129) (actual time=4,473.029..4,473.119 rows=508 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: 65kB
26. 861.134 4,472.854 ↑ 401.3 288 1

Hash Left Join (cost=373,710.06..415,837.56 rows=115,574 width=129) (actual time=3,611.916..4,472.854 rows=288 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)
27. 2.025 667.428 ↑ 401.3 288 1

Hash Right Join (cost=86,231.45..86,508.14 rows=115,574 width=114) (actual time=667.311..667.428 rows=288 loops=1)

  • Hash Cond: (sr.sample_collection_id = sc.sample_collection_id)
28. 1.607 1.607 ↓ 1.0 25,686 1

Seq Scan on sample_rejections sr (cost=0.00..216.05 rows=25,682 width=8) (actual time=0.006..1.607 rows=25,686 loops=1)

29. 0.126 663.796 ↑ 401.3 288 1

Hash (cost=85,826.94..85,826.94 rows=115,574 width=110) (actual time=663.796..663.796 rows=288 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 30kB
30. 0.988 663.670 ↑ 401.3 288 1

Hash Left Join (cost=1.44..85,826.94 rows=115,574 width=110) (actual time=505.502..663.670 rows=288 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: 4909
31. 662.661 662.661 ↑ 25.3 5,197 1

Seq Scan on sample_collection sc (cost=0.00..85,649.03 rows=131,245 width=101) (actual time=441.789..662.661 rows=5,197 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: 3102785
32. 0.012 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
33. 0.009 0.009 ↑ 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.009 rows=67 loops=1)

34. 1,334.621 2,944.292 ↓ 1.0 7,573,345 1

Hash (cost=224,095.05..224,095.05 rows=7,561,017 width=15) (actual time=2,944.292..2,944.292 rows=7,573,345 loops=1)

  • Buckets: 262144 Batches: 8 Memory Usage: 46286kB
35. 1,609.671 1,609.671 ↓ 1.0 7,573,345 1

Seq Scan on tests_prescribed tpr (cost=0.00..224,095.05 rows=7,561,017 width=15) (actual time=0.006..1,609.671 rows=7,573,345 loops=1)

36. 0.493 1.408 ↑ 1.0 1,798 1

Hash (cost=515.39..515.39 rows=1,798 width=49) (actual time=1.408..1.408 rows=1,798 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 143kB
37. 0.915 0.915 ↑ 1.0 1,798 1

Seq Scan on diagnostics d (cost=0.00..515.39 rows=1,798 width=49) (actual time=0.006..0.915 rows=1,798 loops=1)

38. 0.011 0.023 ↑ 1.0 59 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
39. 0.012 0.012 ↑ 1.0 59 1

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

40. 0.009 0.017 ↑ 1.0 23 1

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

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

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

42. 3.256 3.256 ↑ 1.0 1 407

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.09..2.28 rows=1 width=40) (actual time=0.007..0.008 rows=1 loops=407)

  • Index Cond: ((patient_id)::text = (sc.patient_id)::text)
43. 1.221 1.221 ↓ 0.0 0 407

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

  • Index Cond: ((sc.patient_id)::text = (incoming_visit_id)::text)
44. 0.003 0.010 ↑ 1.0 12 1

Hash (cost=1.04..1.04 rows=12 width=23) (actual time=0.010..0.010 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
45. 0.007 0.007 ↑ 1.0 12 1

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

46. 0.660 0.660 ↑ 1.0 1 55

Index Scan using patient_details_pkey on patient_details pd (cost=0.09..0.13 rows=1 width=48) (actual time=0.011..0.012 rows=1 loops=55)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
47. 0.004 0.009 ↑ 1.0 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
48. 0.005 0.005 ↑ 1.0 21 1

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

49. 0.220 0.220 ↓ 0.0 0 55

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.004..0.004 rows=0 loops=55)

  • Index Cond: (tp.prescribed_id = prescribed_id)
50. 0.004 0.008 ↑ 1.0 18 1

Hash (cost=1.05..1.05 rows=18 width=17) (actual time=0.008..0.008 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
51. 0.004 0.004 ↑ 1.0 18 1

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

52. 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
53. 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)

54. 0.055 0.055 ↓ 0.0 0 55

Index Scan using tests_prescribed_pkey on tests_prescribed tp1 (cost=0.09..0.69 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=55)

  • Index Cond: (prescribed_id = isrd.source_test_prescribed)
55. 0.055 0.055 ↓ 0.0 0 55

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

  • Index Cond: (sample_collection_id = tp1.sample_collection_id)
56. 0.550 0.550 ↑ 1.0 1 55

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

  • Index Cond: (COALESCE((tp.prescribed_id)::text, (tpr.prescribed_id)::text) = (activity_id)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
57. 0.715 0.715 ↑ 1.0 1 55

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.11..0.50 rows=1 width=30) (actual time=0.012..0.013 rows=1 loops=55)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
58. 1.705 1.705 ↑ 1.0 1 55

Index Scan using bill_pkey on bill b (cost=0.11..0.14 rows=1 width=16) (actual time=0.012..0.031 rows=1 loops=55)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
59. 0.220 0.220 ↑ 1.0 1 55

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

  • Index Cond: (sample_type_id = sc.sample_type_id)