explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fph

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 148,206.741 ↑ 10.0 2 1

Limit (cost=3,138,010,672.20..3,138,010,887.40 rows=20 width=602) (actual time=148,206.733..148,206.741 rows=2 loops=1)

2. 0.505 148,206.739 ↑ 3,554,481.7 3 1

GroupAggregate (cost=3,138,010,661.44..3,252,749,322.11 rows=10,663,445 width=602) (actual time=148,206.722..148,206.739 rows=3 loops=1)

3. 0.041 148,206.234 ↑ 35,544,814.3 3 1

Sort (cost=3,138,010,661.44..3,138,277,247.55 rows=106,634,443 width=602) (actual time=148,206.233..148,206.234 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.007 148,206.193 ↑ 35,544,814.3 3 1

Subquery Scan on foo (cost=3,055,008,588.90..3,066,205,205.42 rows=106,634,443 width=602) (actual time=148,206.188..148,206.193 rows=3 loops=1)

5. 0.006 148,206.186 ↑ 35,544,814.3 3 1

Unique (cost=3,055,008,588.90..3,065,138,860.99 rows=106,634,443 width=365) (actual time=148,206.181..148,206.186 rows=3 loops=1)

6. 0.048 148,206.180 ↑ 35,544,814.3 3 1

Sort (cost=3,055,008,588.90..3,055,275,175.01 rows=106,634,443 width=365) (actual time=148,206.180..148,206.180 rows=3 loops=1)

  • Sort Key: st.sample_type, sc.sample_sno, sc.sample_assertion_batch, ((sc.sample_date)::date), scc.collection_center, sc.sample_collection_id, sc1.transfer_time, sc1.transfer_other_details, sc.receipt_time, sc.receipt_other_details, d.ddept_id, diagnostics_departments.ddept_name, d.test_name, sc.sample_qty, sc.assertion_time, sc.rejected_time, tp.conducted, st.sample_type_id, (COALESCE(pr.mr_no, isr.mr_no)), (COALESCE(pr.patient_id, isr.incoming_visit_id)), pr.collection_center_id, (COALESCE(isr.patient_name, get_patient_full_name(sm.salutation, pd.patient_name, pd.middle_name, pd.last_name))), (CASE WHEN (isr.incoming_visit_id IS NOT NULL) THEN 't'::bpchar ELSE pr.visit_type END), sc.sample_date, isrd.orig_sample_no, (COALESCE(pr.center_id, isr.center_id)), ih.hospital_name, isr.patient_name, isr.patient_other_info, (CASE WHEN (sc.sample_status = 'R'::bpchar) THEN tp.priority ELSE tp.priority END), dom.outsource_dest_id, (COALESCE(om.oh_name, hcm.center_name)), (CASE WHEN is_outhouse_test(d.test_id, pr.center_id) THEN 'O'::text ELSE 'I'::text END), b.status, bc.charge_head
  • Sort Method: quicksort Memory: 25kB
7. 12.147 148,206.132 ↑ 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=148,130.209..148,206.132 rows=3 loops=1)

  • Hash Cond: (sc.sample_type_id = st.sample_type_id)
8. 59.748 148,193.829 ↑ 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=148,118.875..148,193.829 rows=3 loops=1)

  • Hash Cond: (sc.sample_collection_id = sc1.sample_collection_id)
9. 126.578 146,931.091 ↑ 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=146,915.443..146,931.091 rows=3 loops=1)

  • Merge Cond: ((bac.activity_id)::text = (COALESCE((tp.prescribed_id)::text, (tp.prescribed_id)::text)))
10. 30,698.172 109,528.761 ↑ 6.1 1,210,059 1

Sort (cost=4,948,298.34..4,966,890.71 rows=7,436,947 width=15) (actual time=108,355.745..109,528.761 rows=1,210,059 loops=1)

  • Sort Key: bac.activity_id
  • Sort Method: external merge Disk: 189232kB
11. 10,825.779 78,830.589 ↓ 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=46,535.256..78,830.589 rows=7,473,537 loops=1)

  • Hash Cond: ((bc.bill_no)::text = (b.bill_no)::text)
12. 14,204.049 65,692.878 ↓ 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=44,218.850..65,692.878 rows=7,473,537 loops=1)

  • Hash Cond: ((bac.charge_id)::text = (bc.charge_id)::text)
13. 7,272.113 7,272.113 ↓ 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.013..7,272.113 rows=7,473,537 loops=1)

  • Filter: ((activity_code)::text = 'DIA'::text)
  • Rows Removed by Filter: 15589674
14. 6,841.409 44,216.716 ↑ 1.0 26,507,872 1

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

  • Buckets: 131072 Batches: 64 Memory Usage: 25992kB
15. 37,375.307 37,375.307 ↑ 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..37,375.307 rows=26,507,872 loops=1)

16. 1,029.904 2,311.932 ↓ 1.0 5,518,785 1

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

  • Buckets: 131072 Batches: 8 Memory Usage: 32921kB
17. 1,282.028 1,282.028 ↓ 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.089..1,282.028 rows=5,518,785 loops=1)

18. 0.007 37,275.752 ↑ 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=37,275.749..37,275.752 rows=3 loops=1)

19. 0.040 37,275.745 ↑ 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=37,275.744..37,275.745 rows=3 loops=1)

  • Sort Key: (COALESCE((tp.prescribed_id)::text, (tp.prescribed_id)::text))
  • Sort Method: quicksort Memory: 25kB
20. 0.024 37,275.705 ↑ 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=37,214.541..37,275.705 rows=3 loops=1)

  • Hash Cond: (pr.collection_center_id = scc.collection_center_id)
21. 0.022 37,275.677 ↑ 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=37,214.520..37,275.677 rows=3 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
22. 130.343 37,275.639 ↑ 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=37,214.486..37,275.639 rows=3 loops=1)

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
23. 0.392 35,001.142 ↑ 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,688.802..35,001.142 rows=3 loops=1)

  • Hash Cond: (((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text) AND (tp.prescribed_id = isrd.prescribed_id))
24. 0.127 34,524.620 ↑ 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=34,212.290..34,524.620 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
25. 437.957 34,362.991 ↑ 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=13,324.908..34,362.991 rows=22 loops=1)

  • Hash Cond: ((sc.patient_id)::text = (pr.patient_id)::text)
26. 9,721.339 28,055.664 ↑ 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,609.006..28,055.664 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
27. 12,768.890 18,295.911 ↑ 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,752.301..18,295.911 rows=83,813,868 loops=1)

28. 19.288 1,727.533 ↑ 515.6 257 1

Nested Loop Left Join (cost=7.27..381,384.48 rows=132,504 width=126) (actual time=1,670.939..1,727.533 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
29. 1,695.801 1,695.801 ↑ 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=1,064.978..1,695.801 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
30. 12.345 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)

31. 0.018 0.099 ↑ 1.0 67 1

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

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
32. 0.033 0.061 ↑ 1.0 67 1

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

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
33. 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.004..0.007 rows=67 loops=1)

34. 0.011 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
35. 0.010 0.010 ↑ 1.0 59 1

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

36. 0.007 0.020 ↑ 1.0 23 1

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

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

38. 3,406.079 3,799.488 ↑ 1.1 326,124 257

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

39. 135.175 393.409 ↑ 1.1 326,124 1

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

  • Hash Cond: ((tp.test_id)::text = (d.test_id)::text)
40. 200.042 256.602 ↑ 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=79.696..256.602 rows=326,124 loops=1)

  • Recheck Cond: ((conducted)::text = ANY ('{N,NRN}'::text[]))
41. 56.560 56.560 ↑ 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=56.560..56.560 rows=326,142 loops=1)

  • Index Cond: ((conducted)::text = ANY ('{N,NRN}'::text[]))
42. 0.344 1.632 ↑ 1.0 1,798 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 164kB
43. 0.393 1.288 ↑ 1.0 1,798 1

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

  • Hash Cond: ((d.ddept_id)::text = (diagnostics_departments.ddept_id)::text)
44. 0.885 0.885 ↑ 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.885 rows=1,798 loops=1)

45. 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
46. 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.002..0.005 rows=12 loops=1)

47. 3.687 38.414 ↑ 1.0 25,665 1

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

48. 34.727 34.727 ↑ 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.011..34.727 rows=25,665 loops=1)

49. 1,201.330 5,869.370 ↓ 1.0 4,937,318 1

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

  • Buckets: 65536 Batches: 8 Memory Usage: 45864kB
50. 4,668.040 4,668.040 ↓ 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.797..4,668.040 rows=4,937,318 loops=1)

51. 66.257 161.502 ↑ 1.0 225,307 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 21907kB
52. 79.241 95.245 ↑ 1.0 225,307 1

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

  • Hash Cond: ((isr.orig_lab_name)::text = (ih.hospital_id)::text)
53. 15.995 15.995 ↑ 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.018..15.995 rows=225,307 loops=1)

54. 0.004 0.009 ↑ 1.0 18 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
55. 0.005 0.005 ↑ 1.0 18 1

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

56. 252.079 476.130 ↑ 1.0 817,383 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 51021kB
57. 224.051 224.051 ↑ 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.014..224.051 rows=817,383 loops=1)

58. 708.805 2,144.154 ↑ 1.0 2,416,521 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 46233kB
59. 1,435.349 1,435.349 ↑ 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.643..1,435.349 rows=2,416,521 loops=1)

60. 0.006 0.016 ↑ 1.0 21 1

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

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

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

62. 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
63. 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)

64. 439.731 1,202.990 ↑ 1.0 3,099,808 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 28420kB
65. 763.259 763.259 ↑ 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.009..763.259 rows=3,099,808 loops=1)

66. 0.016 0.156 ↑ 1.0 49 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
67. 0.140 0.140 ↑ 1.0 49 1

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