explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1rvN

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 118,985.925 ↑ 10.0 2 1

Limit (cost=3,077,943,326.42..3,077,943,348.12 rows=20 width=602) (actual time=118,985.917..118,985.925 rows=2 loops=1)

2. 0.049 118,985.923 ↑ 35,544,814.3 3 1

GroupAggregate (cost=3,077,943,325.33..3,193,641,695.99 rows=106,634,443 width=602) (actual time=118,985.906..118,985.923 rows=3 loops=1)

3. 0.056 118,985.874 ↑ 35,544,814.3 3 1

Sort (cost=3,077,943,325.33..3,078,209,911.44 rows=106,634,443 width=602) (actual time=118,985.873..118,985.874 rows=3 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: 25kB
4. 0.005 118,985.818 ↑ 35,544,814.3 3 1

Subquery Scan on foo (cost=2,934,851,425.55..3,006,137,869.31 rows=106,634,443 width=602) (actual time=118,907.989..118,985.818 rows=3 loops=1)

5. 0.460 118,985.813 ↑ 35,544,814.3 3 1

Hash Join (cost=2,934,851,425.55..3,005,071,524.88 rows=106,634,443 width=365) (actual time=118,907.989..118,985.813 rows=3 loops=1)

  • Hash Cond: (sc.sample_type_id = st.sample_type_id)
6. 61.965 118,985.182 ↑ 35,544,814.3 3 1

Hash Left Join (cost=2,934,851,324.44..2,949,754,806.47 rows=106,634,443 width=350) (actual time=118,907.605..118,985.182 rows=3 loops=1)

  • Hash Cond: (sc.sample_collection_id = sc1.sample_collection_id)
7. 127.670 117,722.935 ↑ 35,544,814.3 3 1

Merge Join (cost=2,934,698,834.80..2,937,874,047.96 rows=106,634,443 width=341) (actual time=117,707.198..117,722.935 rows=3 loops=1)

  • Merge Cond: ((bac.activity_id)::text = (COALESCE((tp.prescribed_id)::text, (tp.prescribed_id)::text)))
8. 30,946.812 81,736.605 ↑ 6.1 1,210,059 1

Sort (cost=4,948,298.34..4,966,890.71 rows=7,436,947 width=15) (actual time=80,567.938..81,736.605 rows=1,210,059 loops=1)

  • Sort Key: bac.activity_id
  • Sort Method: external merge Disk: 189232kB
9. 12,300.199 50,789.793 ↓ 1.0 7,473,537 1

Hash Join (cost=2,539,559.92..3,972,410.21 rows=7,436,947 width=15) (actual time=24,776.645..50,789.793 rows=7,473,537 loops=1)

  • Hash Cond: ((bc.bill_no)::text = (b.bill_no)::text)
10. 9,911.730 30,591.712 ↓ 1.0 7,473,537 1

Hash Join (cost=2,128,888.21..3,284,387.56 rows=7,436,947 width=27) (actual time=16,876.925..30,591.712 rows=7,473,537 loops=1)

  • Hash Cond: ((bac.charge_id)::text = (bc.charge_id)::text)
11. 3,803.760 3,803.760 ↓ 1.0 7,473,537 1

Seq Scan on bill_activity_charge bac (cost=0.00..543,165.55 rows=7,436,947 width=17) (actual time=0.016..3,803.760 rows=7,473,537 loops=1)

  • Filter: ((activity_code)::text = 'DIA'::text)
  • Rows Removed by Filter: 15589674
12. 5,502.353 16,876.222 ↑ 1.0 26,507,872 1

Hash (cost=1,616,286.76..1,616,286.76 rows=26,510,276 width=30) (actual time=16,876.222..16,876.222 rows=26,507,872 loops=1)

  • Buckets: 131072 Batches: 64 Memory Usage: 25992kB
13. 11,373.869 11,373.869 ↑ 1.0 26,507,872 1

Seq Scan on bill_charge bc (cost=0.00..1,616,286.76 rows=26,510,276 width=30) (actual time=0.003..11,373.869 rows=26,507,872 loops=1)

14. 1,292.302 7,897.882 ↓ 1.0 5,518,785 1

Hash (cost=314,787.87..314,787.87 rows=5,515,987 width=16) (actual time=7,897.882..7,897.882 rows=5,518,785 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 32921kB
15. 6,605.580 6,605.580 ↓ 1.0 5,518,785 1

Seq Scan on bill b (cost=0.00..314,787.87 rows=5,515,987 width=16) (actual time=0.931..6,605.580 rows=5,518,785 loops=1)

16. 0.010 35,858.660 ↑ 38,044,086.3 3 1

Materialize (cost=2,929,750,536.45..2,930,321,197.75 rows=114,132,259 width=337) (actual time=35,858.656..35,858.660 rows=3 loops=1)

17. 0.039 35,858.650 ↑ 38,044,086.3 3 1

Sort (cost=2,929,750,536.45..2,930,035,867.10 rows=114,132,259 width=337) (actual time=35,858.649..35,858.650 rows=3 loops=1)

  • Sort Key: (COALESCE((tp.prescribed_id)::text, (tp.prescribed_id)::text))
  • Sort Method: quicksort Memory: 25kB
18. 0.017 35,858.611 ↑ 38,044,086.3 3 1

Hash Left Join (cost=592,331.63..2,878,586,889.26 rows=114,132,259 width=337) (actual time=35,797.163..35,858.611 rows=3 loops=1)

  • Hash Cond: (pr.collection_center_id = scc.collection_center_id)
19. 0.018 35,858.590 ↑ 38,044,086.3 3 1

Hash Left Join (cost=592,330.60..2,877,017,569.67 rows=114,132,259 width=328) (actual time=35,797.148..35,858.590 rows=3 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
20. 131.762 35,858.554 ↑ 38,044,086.3 3 1

Hash Left Join (cost=592,329.13..2,875,448,249.64 rows=114,132,259 width=332) (actual time=35,797.114..35,858.554 rows=3 loops=1)

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
21. 0.386 34,366.759 ↑ 38,044,086.3 3 1

Hash Left Join (cost=432,710.17..2,863,417,216.53 rows=114,132,259 width=299) (actual time=34,047.675..34,366.759 rows=3 loops=1)

  • Hash Cond: (((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text) AND (tp.prescribed_id = isrd.prescribed_id))
22. 0.133 34,093.324 ↑ 38,044,086.3 3 1

Hash Left Join (cost=399,861.60..2,853,258,456.17 rows=114,132,259 width=286) (actual time=33,774.248..34,093.324 rows=3 loops=1)

  • Hash Cond: ((sc.patient_id)::text = (isr.incoming_visit_id)::text)
  • Filter: (COALESCE(pr.center_id, isr.center_id) = 13)
  • Rows Removed by Filter: 19
23. 442.192 33,932.473 ↑ 1,037,565,995.2 22 1

Hash Left Join (cost=387,375.81..2,484,775,045.13 rows=22,826,451,895 width=236) (actual time=12,776.498..33,932.473 rows=22 loops=1)

  • Hash Cond: ((sc.patient_id)::text = (pr.patient_id)::text)
24. 9,826.203 27,891.522 ↑ 1,037,565,995.2 22 1

Merge Left Join (cost=10,967.06..693,905,423.87 rows=22,826,451,895 width=195) (actual time=5,326.650..27,891.522 rows=22 loops=1)

  • Merge Cond: (sc.sample_collection_id = sr.sample_collection_id)
  • Filter: CASE WHEN (sc.sample_status = 'R'::bpchar) THEN (sr.test_prescribed_id = tp.sample_collection_id) ELSE (sc.sample_collection_id = tp.prescribed_id) END
  • Rows Removed by Filter: 83813846
25. 12,882.167 18,059.958 ↑ 544.7 83,813,868 1

Nested Loop (cost=10,966.77..571,269,984.65 rows=45,652,903,790 width=199) (actual time=1,506.434..18,059.958 rows=83,813,868 loops=1)

26. 19.531 1,494.210 ↑ 515.6 257 1

Nested Loop Left Join (cost=7.27..381,384.48 rows=132,504 width=126) (actual time=1,439.210..1,494.210 rows=257 loops=1)

  • Join Filter: (sc.outsource_dest_id = dom.outsource_dest_id)
  • Rows Removed by Join Filter: 273945
  • Filter: ((dom.outsource_dest_type IS NULL) OR (dom.outsource_dest_type <> 'C'::bpchar))
  • Rows Removed by Filter: 3891
27. 1,462.235 1,462.235 ↑ 36.3 4,148 1

Index Scan using sample_collection_id_pkey on sample_collection sc (cost=0.43..204,947.06 rows=150,471 width=101) (actual time=891.752..1,462.235 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
28. 12.355 12.444 ↑ 1.0 67 4,148

Materialize (cost=6.84..10.34 rows=67 width=31) (actual time=0.000..0.003 rows=67 loops=4,148)

29. 0.012 0.089 ↑ 1.0 67 1

Hash Left Join (cost=6.84..10.00 rows=67 width=31) (actual time=0.061..0.089 rows=67 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
30. 0.032 0.060 ↑ 1.0 67 1

Hash Left Join (cost=2.33..4.84 rows=67 width=28) (actual time=0.040..0.060 rows=67 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
31. 0.007 0.007 ↑ 1.0 67 1

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

32. 0.008 0.021 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=24) (actual time=0.021..0.021 rows=59 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
33. 0.013 0.013 ↑ 1.0 59 1

Seq Scan on outhouse_master om (cost=0.00..1.59 rows=59 width=24) (actual time=0.003..0.013 rows=59 loops=1)

34. 0.007 0.017 ↑ 1.0 23 1

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

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

36. 3,325.365 3,683.581 ↑ 1.1 326,124 257

Materialize (cost=10,959.50..229,515.81 rows=344,539 width=73) (actual time=0.262..14.333 rows=326,124 loops=257)

37. 140.607 358.216 ↑ 1.1 326,124 1

Hash Join (cost=10,959.50..227,793.12 rows=344,539 width=73) (actual time=67.215..358.216 rows=326,124 loops=1)

  • Hash Cond: ((tp.test_id)::text = (d.test_id)::text)
38. 170.925 216.202 ↑ 1.1 326,124 1

Bitmap Heap Scan on tests_prescribed tp (cost=10,383.05..222,048.59 rows=344,539 width=19) (actual time=65.784..216.202 rows=326,124 loops=1)

  • Recheck Cond: ((conducted)::text = ANY ('{N,NRN}'::text[]))
39. 45.277 45.277 ↑ 1.1 326,142 1

Bitmap Index Scan on tests_prescribed_conducted_idx (cost=0.00..10,296.91 rows=344,539 width=0) (actual time=45.277..45.277 rows=326,142 loops=1)

  • Index Cond: ((conducted)::text = ANY ('{N,NRN}'::text[]))
40. 0.353 1.407 ↑ 1.0 1,798 1

Hash (cost=553.97..553.97 rows=1,798 width=62) (actual time=1.407..1.407 rows=1,798 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 164kB
41. 0.403 1.054 ↑ 1.0 1,798 1

Hash Join (cost=1.27..553.97 rows=1,798 width=62) (actual time=0.023..1.054 rows=1,798 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (diagnostics_departments.ddept_id)::text)
42. 0.641 0.641 ↑ 1.0 1,798 1

Seq Scan on diagnostics d (cost=0.00..527.98 rows=1,798 width=49) (actual time=0.004..0.641 rows=1,798 loops=1)

43. 0.005 0.010 ↑ 1.0 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
44. 0.005 0.005 ↑ 1.0 12 1

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

45. 2.030 5.361 ↑ 1.0 25,665 1

Materialize (cost=0.29..887.48 rows=25,665 width=8) (actual time=0.013..5.361 rows=25,665 loops=1)

46. 3.331 3.331 ↑ 1.0 25,665 1

Index Scan using idx_sample_rejections_sample_collection_id on sample_rejections sr (cost=0.29..823.31 rows=25,665 width=8) (actual time=0.010..3.331 rows=25,665 loops=1)

47. 1,235.491 5,598.759 ↓ 1.0 4,937,318 1

Hash (cost=271,308.89..271,308.89 rows=4,936,789 width=41) (actual time=5,598.759..5,598.759 rows=4,937,318 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 45864kB
48. 4,363.268 4,363.268 ↓ 1.0 4,937,318 1

Seq Scan on patient_registration pr (cost=0.00..271,308.89 rows=4,936,789 width=41) (actual time=0.559..4,363.268 rows=4,937,318 loops=1)

49. 58.730 160.718 ↑ 1.0 225,307 1

Hash (cost=9,669.45..9,669.45 rows=225,307 width=65) (actual time=160.718..160.718 rows=225,307 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 21907kB
50. 85.382 101.988 ↑ 1.0 225,307 1

Hash Left Join (cost=1.41..9,669.45 rows=225,307 width=65) (actual time=0.028..101.988 rows=225,307 loops=1)

  • Hash Cond: ((isr.orig_lab_name)::text = (ih.hospital_id)::text)
51. 16.594 16.594 ↑ 1.0 225,307 1

Seq Scan on incoming_sample_registration isr (cost=0.00..6,570.07 rows=225,307 width=62) (actual time=0.006..16.594 rows=225,307 loops=1)

52. 0.003 0.012 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=17) (actual time=0.012..0.012 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
53. 0.009 0.009 ↑ 1.0 18 1

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

54. 164.519 273.049 ↑ 1.0 817,383 1

Hash (cost=14,999.83..14,999.83 rows=817,383 width=30) (actual time=273.049..273.049 rows=817,383 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 51021kB
55. 108.530 108.530 ↑ 1.0 817,383 1

Seq Scan on incoming_sample_registration_details isrd (cost=0.00..14,999.83 rows=817,383 width=30) (actual time=0.006..108.530 rows=817,383 loops=1)

56. 597.605 1,360.033 ↑ 1.0 2,416,521 1

Hash (cost=108,086.76..108,086.76 rows=2,420,576 width=48) (actual time=1,360.033..1,360.033 rows=2,416,521 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 46233kB
57. 762.428 762.428 ↑ 1.0 2,416,521 1

Seq Scan on patient_details pd (cost=0.00..108,086.76 rows=2,420,576 width=48) (actual time=0.004..762.428 rows=2,416,521 loops=1)

58. 0.009 0.018 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=14) (actual time=0.018..0.018 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
59. 0.009 0.009 ↑ 1.0 21 1

Seq Scan on salutation_master sm (cost=0.00..1.21 rows=21 width=14) (actual time=0.006..0.009 rows=21 loops=1)

60. 0.001 0.004 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=13) (actual time=0.004..0.004 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
61. 0.003 0.003 ↑ 1.0 1 1

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

62. 431.742 1,200.282 ↑ 1.0 3,099,808 1

Hash (cost=98,591.51..98,591.51 rows=3,100,651 width=13) (actual time=1,200.282..1,200.282 rows=3,099,808 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 28420kB
63. 768.540 768.540 ↑ 1.0 3,099,808 1

Seq Scan on sample_collection sc1 (cost=0.00..98,591.51 rows=3,100,651 width=13) (actual time=0.007..768.540 rows=3,099,808 loops=1)

64. 0.015 0.171 ↑ 1.0 49 1

Hash (cost=100.49..100.49 rows=49 width=19) (actual time=0.171..0.171 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
65. 0.156 0.156 ↑ 1.0 49 1

Seq Scan on sample_type st (cost=0.00..100.49 rows=49 width=19) (actual time=0.004..0.156 rows=49 loops=1)