explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u6z7

Settings
# exclusive inclusive rows x rows loops node
1. 0.338 19,805.803 ↑ 46.4 409 1

Sort (cost=1,254,001.20..1,254,048.66 rows=18,985 width=232) (actual time=19,805.732..19,805.803 rows=409 loops=1)

  • Sort Key: (sum(m.patients)) DESC
  • Sort Method: quicksort Memory: 82kB
2. 1.034 19,805.465 ↑ 46.4 409 1

GroupAggregate (cost=1,246,348.91..1,250,573.07 rows=18,985 width=232) (actual time=19,804.279..19,805.465 rows=409 loops=1)

  • Group Key: m.referral_id, m.name
3. 0.630 19,804.431 ↑ 233.8 812 1

Sort (cost=1,246,348.91..1,246,823.53 rows=189,850 width=176) (actual time=19,804.268..19,804.431 rows=812 loops=1)

  • Sort Key: m.referral_id, m.name
  • Sort Method: quicksort Memory: 107kB
4. 0.381 19,803.801 ↑ 233.8 812 1

Subquery Scan on m (cost=169,719.55..1,213,478.29 rows=189,850 width=176) (actual time=6,080.995..19,803.801 rows=812 loops=1)

5. 0.323 19,803.420 ↑ 233.8 812 1

Append (cost=169,719.55..1,211,579.79 rows=189,850 width=176) (actual time=6,080.994..19,803.420 rows=812 loops=1)

6. 39.229 6,136.162 ↑ 3.1 403 1

GroupAggregate (cost=169,719.55..169,794.55 rows=1,250 width=158) (actual time=6,080.993..6,136.162 rows=403 loops=1)

  • Group Key: p_1.referral_id, r.name
7. 117.749 6,096.933 ↓ 69.6 86,957 1

Sort (cost=169,719.55..169,722.68 rows=1,250 width=46) (actual time=6,073.853..6,096.933 rows=86,957 loops=1)

  • Sort Key: p_1.referral_id, r.name
  • Sort Method: external merge Disk: 4,520kB
8. 55.525 5,979.184 ↓ 69.6 86,957 1

Hash Left Join (cost=148,918.94..169,655.26 rows=1,250 width=46) (actual time=1,525.320..5,979.184 rows=86,957 loops=1)

  • Hash Cond: (p_1.referral_id = r.id)
9. 65.385 5,922.476 ↓ 69.6 86,957 1

Nested Loop Left Join (cost=148,781.42..169,514.44 rows=1,250 width=32) (actual time=1,524.129..5,922.476 rows=86,957 loops=1)

10. 26.360 1,563.985 ↓ 312.5 21,253 1

Unique (cost=148,777.31..148,786.15 rows=68 width=607) (actual time=1,522.970..1,563.985 rows=21,253 loops=1)

11. 58.717 1,537.625 ↓ 312.6 21,255 1

Sort (cost=148,777.31..148,777.48 rows=68 width=607) (actual time=1,522.968..1,537.625 rows=21,255 loops=1)

  • Sort Key: p_1.id, p_1.organization_id, p_1.default_location_id, p_1.first_name, p_1.middle_name, p_1.last_name, p_1.nickname, p_1.phonetical_name, p_1.username, p_1.address, p_1.address2, p_1.city, p_1.state_id, p_1.zip, p_1.email, p_1.gender, p_1.birth_date, p_1.height, p_1.weight, p_1.marital_status, p_1.shoe_size, p_1.memo, p_1.occupation, p_1.referral_id, p_1.legacy_spouse, p_1.legacy_children, p_1.imported_from, p_1.created, p_1.last_updated, p_1.last_updated_by_user_id, p_1.created_by_user_id, p_1.legacy_atlas_id, p_1.legacy_atlas_sin, p_1.employer, p_1.appt_txt_opt_in, p_1.deleted, p_1.merged_into_patient_id, p_1.referring_patient_id, p_1.language_preference_id, p_1.referral_type_id, p_1.profile_photo_url, p_1.default_billing_chd_user_id, p_1.default_calendar_chd_user_id, p_1.smartphone_checkin_guid, p_1.inactive_date, p_1.fortis_contact_id, p_1.patient_status_id, p_1.memo_color_id, p_1.owner_chd_user_id, p_1.bounced_email, p_1.complaint_email
  • Sort Method: external merge Disk: 10,744kB
12. 685.978 1,478.908 ↓ 312.6 21,255 1

Hash Left Join (cost=30,600.87..148,775.24 rows=68 width=607) (actual time=456.453..1,478.908 rows=21,255 loops=1)

  • Hash Cond: (i_1.patient_id = i_2.patient_id)
  • Join Filter: ((ii_1.id <> ii_2.id) AND (ii_2.posting_date < ii_1.posting_date))
  • Rows Removed by Join Filter: 21,574
  • Filter: ((ROW(i_2.patient_id, ii_2.id, ii_2.invoice_id, ii_2.inventory_item_id, ii_2.service_id, ii_2.tax_rate_id, ii_2.third_party_payer_item_id, ii_2.invoice_adjustment_type_id, ii_2.purchased_gift_detail_id, ii_2.by_chd_user_id, ii_2.billing_chd_user_id, ii_2.original_amount, ii_2.imported_from, ii_2.deleted, ii_2.posting_date, ii_2.created, ii_2.last_updated, ii_2.last_updated_by_user_id, ii_2.created_by_user_id, ii_2.third_party_payer_detail_id, ii_2.service_code_id, ii_2.service_code_modifier_1_id, ii_2.payment_type_id, ii_2.void, ii_2.transfer_to_patient_id, ii_2.transfer_from_invoice_item_id, ii_2.memo, ii_2.transfer_from_patient_id, ii_2.transfer_from_location_account_id, ii_2.third_party_payer_problem, ii_2.transfer_to_location_account_id, ii_2.third_party_payer_problem_description, ii_2.invoice_adjustment_payment_type_id, ii_2.pending, ii_2.fortis_transaction_id, ii_2.custom_third_party_payer_problem, ii_2.custom_third_party_payer_problem_description, ii_2.service_code_modifier_2_id, ii_2.service_code_modifier_3_id, ii_2.service_code_modifier_4_id, ii_2.insurance_payment, ii_2.write_off_reason_id, ii_2.posting_date_ref, ii_2.created_date_ref)) IS NULL)
  • Rows Removed by Filter: 204
13. 16.012 408.145 ↓ 1.6 21,455 1

Hash Join (cost=21,637.48..139,514.48 rows=13,535 width=631) (actual time=71.577..408.145 rows=21,455 loops=1)

  • Hash Cond: (i_1.location_id = l_1.id)
14. 15.073 392.101 ↓ 1.6 21,455 1

Nested Loop (cost=21,633.56..139,471.35 rows=13,535 width=639) (actual time=71.537..392.101 rows=21,455 loops=1)

15. 28.173 312.663 ↓ 1.5 21,455 1

Nested Loop (cost=21,633.14..132,549.79 rows=13,897 width=32) (actual time=71.522..312.663 rows=21,455 loops=1)

16. 9.342 241.578 ↓ 1.5 21,456 1

Nested Loop (cost=21,632.71..119,963.46 rows=13,982 width=24) (actual time=71.506..241.578 rows=21,456 loops=1)

17. 0.012 0.012 ↑ 1.0 3 1

Seq Scan on service_report_metric_mapping rmm (cost=0.00..2.33 rows=3 width=8) (actual time=0.003..0.012 rows=3 loops=1)

  • Filter: (report_metric_id = 1)
  • Rows Removed by Filter: 23
18. 40.995 232.224 ↓ 1.5 7,152 3

Bitmap Heap Scan on invoice_item ii_1 (cost=21,632.71..39,940.44 rows=4,661 width=32) (actual time=64.852..77.408 rows=7,152 loops=3)

  • Recheck Cond: ((service_id = rmm.service_id) AND (posting_date_ref >= '2019-01-01'::date) AND (posting_date_ref <= '2019-12-31'::date))
  • Rows Removed by Index Recheck: 4,013
  • Filter: (NOT void)
  • Rows Removed by Filter: 420
  • Heap Blocks: exact=21,323
19. 2.745 191.229 ↓ 0.0 0 3

BitmapAnd (cost=21,632.71..21,632.71 rows=5,670 width=0) (actual time=63.743..63.743 rows=0 loops=3)

20. 6.981 6.981 ↓ 1.0 22,544 3

Bitmap Index Scan on invoice_item_service_id_idx (cost=0.00..686.77 rows=22,179 width=0) (actual time=2.327..2.327 rows=22,544 loops=3)

  • Index Cond: (service_id = rmm.service_id)
21. 181.503 181.503 ↓ 1.0 1,003,217 3

Bitmap Index Scan on invoice_item_posting_date_ref_idx (cost=0.00..20,740.82 rows=991,239 width=0) (actual time=60.501..60.501 rows=1,003,217 loops=3)

  • Index Cond: ((posting_date_ref >= '2019-01-01'::date) AND (posting_date_ref <= '2019-12-31'::date))
22. 42.912 42.912 ↑ 1.0 1 21,456

Index Scan using invoice_pkey on invoice i_1 (cost=0.43..0.90 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=21,456)

  • Index Cond: (id = ii_1.invoice_id)
  • Filter: (NOT void)
  • Rows Removed by Filter: 0
23. 64.365 64.365 ↑ 1.0 1 21,455

Index Scan using patient_pkey on patient p_1 (cost=0.42..0.50 rows=1 width=607) (actual time=0.003..0.003 rows=1 loops=21,455)

  • Index Cond: (id = i_1.patient_id)
  • Filter: (default_location_id = ANY ('{0,27,8,16,44,18,14,21,32,25,24,15,30,31,34,10,1,19,17,13,7,45,46,33,23,42,12,11,29,4,28,43,26,20,41,22,6,5,2,3}'::bigint[]))
24. 0.013 0.032 ↑ 1.0 41 1

Hash (cost=3.41..3.41 rows=41 width=8) (actual time=0.032..0.032 rows=41 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
25. 0.019 0.019 ↑ 1.0 41 1

Seq Scan on location l_1 (cost=0.00..3.41 rows=41 width=8) (actual time=0.006..0.019 rows=41 loops=1)

26. 43.867 384.785 ↓ 7.2 66,207 1

Hash (cost=8,847.83..8,847.83 rows=9,245 width=56) (actual time=384.785..384.785 rows=66,207 loops=1)

  • Buckets: 32,768 (originally 16384) Batches: 4 (originally 1) Memory Usage: 3,841kB
27. 101.129 340.918 ↓ 7.2 66,207 1

Nested Loop (cost=4.17..8,847.83 rows=9,245 width=56) (actual time=0.080..340.918 rows=66,207 loops=1)

28. 35.859 107.369 ↓ 7.1 66,210 1

Nested Loop (cost=3.74..3,609.29 rows=9,301 width=424) (actual time=0.068..107.369 rows=66,210 loops=1)

29. 0.044 0.173 ↑ 1.0 3 1

Merge Join (cost=3.31..23.54 rows=3 width=16) (actual time=0.048..0.173 rows=3 loops=1)

  • Merge Cond: (s.id = rmm_1.service_id)
30. 0.108 0.108 ↑ 6.3 163 1

Index Only Scan using service_pkey on service s (cost=0.28..114.28 rows=1,028 width=8) (actual time=0.017..0.108 rows=163 loops=1)

  • Heap Fetches: 163
31. 0.011 0.021 ↑ 1.0 3 1

Sort (cost=2.35..2.36 rows=3 width=8) (actual time=0.018..0.021 rows=3 loops=1)

  • Sort Key: rmm_1.service_id
  • Sort Method: quicksort Memory: 25kB
32. 0.010 0.010 ↑ 1.0 3 1

Seq Scan on service_report_metric_mapping rmm_1 (cost=0.00..2.33 rows=3 width=8) (actual time=0.006..0.010 rows=3 loops=1)

  • Filter: (report_metric_id = 1)
  • Rows Removed by Filter: 23
33. 71.337 71.337 ↓ 1.2 22,070 3

Index Scan using invoice_item_service_id_idx on invoice_item ii_2 (cost=0.43..1,012.94 rows=18,231 width=424) (actual time=0.012..23.779 rows=22,070 loops=3)

  • Index Cond: (service_id = s.id)
  • Filter: (NOT void)
  • Rows Removed by Filter: 474
34. 132.420 132.420 ↑ 1.0 1 66,210

Index Scan using invoice_pkey on invoice i_2 (cost=0.43..0.56 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=66,210)

  • Index Cond: (id = ii_2.invoice_id)
  • Filter: (NOT void)
  • Rows Removed by Filter: 0
35. 86.796 4,293.106 ↑ 4.8 4 21,253

Nested Loop (cost=4.11..304.63 rows=19 width=24) (actual time=0.072..0.202 rows=4 loops=21,253)

36. 67.327 4,123.082 ↑ 4.8 4 21,253

Nested Loop (cost=3.97..301.63 rows=19 width=32) (actual time=0.070..0.194 rows=4 loops=21,253)

37. 212.488 3,889.299 ↑ 4.8 4 21,253

Hash Left Join (cost=3.55..292.17 rows=19 width=32) (actual time=0.067..0.183 rows=4 loops=21,253)

  • Hash Cond: (ii.payment_type_id = pt.id)
  • Filter: (pt.in_cash OR (iil.billed_paid_amount > 0))
  • Rows Removed by Filter: 23
38. 488.160 3,676.769 ↑ 1.3 27 21,253

Nested Loop (cost=1.29..289.82 rows=35 width=40) (actual time=0.014..0.173 rows=27 loops=21,253)

39. 294.108 2,061.541 ↑ 1.3 27 21,253

Nested Loop (cost=0.86..261.95 rows=35 width=32) (actual time=0.010..0.097 rows=27 loops=21,253)

40. 361.301 361.301 ↑ 6.3 17 21,253

Index Scan using invoice_patient_id_idx on invoice i (cost=0.43..176.89 rows=107 width=24) (actual time=0.003..0.017 rows=17 loops=21,253)

  • Index Cond: (patient_id = p_1.id)
  • Filter: (NOT void)
  • Rows Removed by Filter: 0
41. 1,406.132 1,406.132 ↓ 2.0 2 351,533

Index Scan using invoice_item_invoice_id_idx on invoice_item ii (cost=0.43..0.78 rows=1 width=24) (actual time=0.003..0.004 rows=2 loops=351,533)

  • Index Cond: (invoice_id = i.id)
  • Filter: ((NOT void) AND (posting_date_ref >= '2019-01-01'::date) AND (posting_date_ref <= '2019-12-31'::date))
  • Rows Removed by Filter: 0
42. 1,127.068 1,127.068 ↑ 1.0 1 563,534

Index Scan using invoice_item_ledger_pkey on invoice_item_ledger iil (cost=0.43..0.80 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=563,534)

  • Index Cond: (invoice_item_id = ii.id)
43. 0.018 0.042 ↑ 1.0 56 1

Hash (cost=1.56..1.56 rows=56 width=9) (actual time=0.042..0.042 rows=56 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
44. 0.024 0.024 ↑ 1.0 56 1

Seq Scan on payment_type pt (cost=0.00..1.56 rows=56 width=9) (actual time=0.008..0.024 rows=56 loops=1)

45. 166.456 166.456 ↑ 1.0 1 83,228

Index Scan using patient_pkey on patient p (cost=0.42..0.50 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=83,228)

  • Index Cond: (id = i.patient_id)
  • Filter: (default_location_id = ANY ('{0,27,8,16,44,18,14,21,32,25,24,15,30,31,34,10,1,19,17,13,7,45,46,33,23,42,12,11,29,4,28,43,26,20,41,22,6,5,2,3}'::bigint[]))
46. 83.228 83.228 ↑ 1.0 1 83,228

Index Only Scan using location_pkey on location l (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=83,228)

  • Index Cond: (id = i.location_id)
  • Heap Fetches: 83,228
47. 0.565 1.183 ↓ 1.0 1,757 1

Hash (cost=116.12..116.12 rows=1,712 width=22) (actual time=1.183..1.183 rows=1,757 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 115kB
48. 0.618 0.618 ↓ 1.0 1,757 1

Seq Scan on referral r (cost=0.00..116.12 rows=1,712 width=22) (actual time=0.005..0.618 rows=1,757 loops=1)

49. 0.191 13,666.935 ↑ 461.1 409 1

Subquery Scan on "*SELECT* 2" (cost=1,012,874.33..1,041,772.73 rows=188,600 width=158) (actual time=13,607.867..13,666.935 rows=409 loops=1)

50. 41.533 13,666.744 ↑ 461.1 409 1

GroupAggregate (cost=1,012,874.33..1,039,415.23 rows=188,600 width=154) (actual time=13,607.865..13,666.744 rows=409 loops=1)

  • Group Key: p_3.referral_id, r2.name
51. 95.651 13,625.211 ↑ 10.6 91,776 1

Sort (cost=1,012,874.33..1,015,307.88 rows=973,420 width=46) (actual time=13,600.400..13,625.211 rows=91,776 loops=1)

  • Sort Key: p_3.referral_id, r2.name
  • Sort Method: external merge Disk: 4,760kB
52. 86.518 13,529.560 ↑ 10.6 91,776 1

Hash Left Join (cost=814,926.49..856,162.56 rows=973,420 width=46) (actual time=13,398.795..13,529.560 rows=91,776 loops=1)

  • Hash Cond: (p_3.id = i_3.patient_id)
53. 14.536 495.375 ↓ 1.6 21,341 1

Hash Left Join (cost=144,238.30..146,168.81 rows=13,535 width=30) (actual time=450.499..495.375 rows=21,341 loops=1)

  • Hash Cond: (p_3.referral_id = r2.id)
54. 20.369 479.695 ↓ 1.6 21,341 1

Unique (cost=144,100.78..145,860.33 rows=13,535 width=607) (actual time=449.341..479.695 rows=21,341 loops=1)

55. 55.567 459.326 ↓ 1.6 21,455 1

Sort (cost=144,100.78..144,134.61 rows=13,535 width=607) (actual time=449.339..459.326 rows=21,455 loops=1)

  • Sort Key: p_3.id, p_3.organization_id, p_3.default_location_id, p_3.first_name, p_3.middle_name, p_3.last_name, p_3.nickname, p_3.phonetical_name, p_3.username, p_3.address, p_3.address2, p_3.city, p_3.state_id, p_3.zip, p_3.email, p_3.gender, p_3.birth_date, p_3.height, p_3.weight, p_3.marital_status, p_3.shoe_size, p_3.memo, p_3.occupation, p_3.referral_id, p_3.legacy_spouse, p_3.legacy_children, p_3.imported_from, p_3.created, p_3.last_updated, p_3.last_updated_by_user_id, p_3.created_by_user_id, p_3.legacy_atlas_id, p_3.legacy_atlas_sin, p_3.employer, p_3.appt_txt_opt_in, p_3.deleted, p_3.merged_into_patient_id, p_3.referring_patient_id, p_3.language_preference_id, p_3.referral_type_id, p_3.profile_photo_url, p_3.default_billing_chd_user_id, p_3.default_calendar_chd_user_id, p_3.smartphone_checkin_guid, p_3.inactive_date, p_3.fortis_contact_id, p_3.patient_status_id, p_3.memo_color_id, p_3.owner_chd_user_id, p_3.bounced_email, p_3.complaint_email
  • Sort Method: external merge Disk: 10,880kB
56. 15.296 403.759 ↓ 1.6 21,455 1

Hash Join (cost=21,637.48..139,514.48 rows=13,535 width=607) (actual time=71.655..403.759 rows=21,455 loops=1)

  • Hash Cond: (i_4.location_id = l_2.id)
57. 14.288 388.426 ↓ 1.6 21,455 1

Nested Loop (cost=21,633.56..139,471.35 rows=13,535 width=615) (actual time=71.608..388.426 rows=21,455 loops=1)

58. 27.338 309.773 ↓ 1.5 21,455 1

Nested Loop (cost=21,633.14..132,549.79 rows=13,897 width=16) (actual time=71.593..309.773 rows=21,455 loops=1)

59. 9.143 239.523 ↓ 1.5 21,456 1

Nested Loop (cost=21,632.71..119,963.46 rows=13,982 width=8) (actual time=71.580..239.523 rows=21,456 loops=1)

60. 0.013 0.013 ↑ 1.0 3 1

Seq Scan on service_report_metric_mapping rmm_2 (cost=0.00..2.33 rows=3 width=8) (actual time=0.004..0.013 rows=3 loops=1)

  • Filter: (report_metric_id = 1)
  • Rows Removed by Filter: 23
61. 39.717 230.367 ↓ 1.5 7,152 3

Bitmap Heap Scan on invoice_item ii_4 (cost=21,632.71..39,940.44 rows=4,661 width=16) (actual time=64.654..76.789 rows=7,152 loops=3)

  • Recheck Cond: ((service_id = rmm_2.service_id) AND (posting_date_ref >= '2019-01-01'::date) AND (posting_date_ref <= '2019-12-31'::date))
  • Rows Removed by Index Recheck: 4,013
  • Filter: (NOT void)
  • Rows Removed by Filter: 420
  • Heap Blocks: exact=21,323
62. 2.661 190.650 ↓ 0.0 0 3

BitmapAnd (cost=21,632.71..21,632.71 rows=5,670 width=0) (actual time=63.550..63.550 rows=0 loops=3)

63. 6.654 6.654 ↓ 1.0 22,544 3

Bitmap Index Scan on invoice_item_service_id_idx (cost=0.00..686.77 rows=22,179 width=0) (actual time=2.218..2.218 rows=22,544 loops=3)

  • Index Cond: (service_id = rmm_2.service_id)
64. 181.335 181.335 ↓ 1.0 1,003,217 3

Bitmap Index Scan on invoice_item_posting_date_ref_idx (cost=0.00..20,740.82 rows=991,239 width=0) (actual time=60.445..60.445 rows=1,003,217 loops=3)

  • Index Cond: ((posting_date_ref >= '2019-01-01'::date) AND (posting_date_ref <= '2019-12-31'::date))
65. 42.912 42.912 ↑ 1.0 1 21,456

Index Scan using invoice_pkey on invoice i_4 (cost=0.43..0.90 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=21,456)

  • Index Cond: (id = ii_4.invoice_id)
  • Filter: (NOT void)
  • Rows Removed by Filter: 0
66. 64.365 64.365 ↑ 1.0 1 21,455

Index Scan using patient_pkey on patient p_3 (cost=0.42..0.50 rows=1 width=607) (actual time=0.003..0.003 rows=1 loops=21,455)

  • Index Cond: (id = i_4.patient_id)
  • Filter: (default_location_id = ANY ('{0,27,8,16,44,18,14,21,32,25,24,15,30,31,34,10,1,19,17,13,7,45,46,33,23,42,12,11,29,4,28,43,26,20,41,22,6,5,2,3}'::bigint[]))
67. 0.014 0.037 ↑ 1.0 41 1

Hash (cost=3.41..3.41 rows=41 width=8) (actual time=0.037..0.037 rows=41 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
68. 0.023 0.023 ↑ 1.0 41 1

Seq Scan on location l_2 (cost=0.00..3.41 rows=41 width=8) (actual time=0.010..0.023 rows=41 loops=1)

69. 0.546 1.144 ↓ 1.0 1,757 1

Hash (cost=116.12..116.12 rows=1,712 width=22) (actual time=1.144..1.144 rows=1,757 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 115kB
70. 0.598 0.598 ↓ 1.0 1,757 1

Seq Scan on referral r2 (cost=0.00..116.12 rows=1,712 width=22) (actual time=0.007..0.598 rows=1,757 loops=1)

71. 218.218 12,947.667 ↑ 2.7 601,708 1

Hash (cost=640,374.32..640,374.32 rows=1,651,110 width=24) (actual time=12,947.667..12,947.667 rows=601,708 loops=1)

  • Buckets: 65,536 Batches: 32 Memory Usage: 1,460kB
72. 497.678 12,729.449 ↑ 2.7 601,708 1

Hash Join (cost=397,364.51..640,374.32 rows=1,651,110 width=24) (actual time=4,689.034..12,729.449 rows=601,708 loops=1)

  • Hash Cond: (i_3.patient_id = p_2.id)
73. 707.690 12,128.211 ↑ 2.7 622,806 1

Hash Join (cost=379,849.54..598,046.90 rows=1,695,355 width=24) (actual time=4,567.521..12,128.211 rows=622,806 loops=1)

  • Hash Cond: (ii_3.invoice_id = i_3.id)
74. 969.145 9,555.065 ↑ 2.7 622,835 1

Hash Left Join (cost=235,007.25..416,748.28 rows=1,705,646 width=24) (actual time=2,699.479..9,555.065 rows=622,835 loops=1)

  • Hash Cond: (ii_3.payment_type_id = pt_1.id)
  • Filter: (pt_1.in_cash OR (iil_1.billed_paid_amount > 0))
  • Rows Removed by Filter: 2,546,106
75. 4,654.209 8,585.877 ↑ 1.0 3,168,941 1

Hash Join (cost=235,004.99..408,318.87 rows=3,181,994 width=32) (actual time=2,698.980..8,585.877 rows=3,168,941 loops=1)

  • Hash Cond: (iil_1.invoice_item_id = ii_3.id)
76. 1,233.109 1,233.109 ↑ 1.0 3,860,336 1

Seq Scan on invoice_item_ledger iil_1 (cost=0.00..99,112.39 rows=3,871,039 width=24) (actual time=0.008..1,233.109 rows=3,860,336 loops=1)

77. 1,127.428 2,698.559 ↑ 1.0 3,168,952 1

Hash (cost=176,487.84..176,487.84 rows=3,187,292 width=24) (actual time=2,698.559..2,698.559 rows=3,168,952 loops=1)

  • Buckets: 65,536 Batches: 64 Memory Usage: 2,880kB
78. 1,571.131 1,571.131 ↑ 1.0 3,168,952 1

Seq Scan on invoice_item ii_3 (cost=0.00..176,487.84 rows=3,187,292 width=24) (actual time=0.006..1,571.131 rows=3,168,952 loops=1)

  • Filter: (NOT void)
  • Rows Removed by Filter: 691,395
79. 0.020 0.043 ↑ 1.0 56 1

Hash (cost=1.56..1.56 rows=56 width=9) (actual time=0.043..0.043 rows=56 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
80. 0.023 0.023 ↑ 1.0 56 1

Seq Scan on payment_type pt_1 (cost=0.00..1.56 rows=56 width=9) (actual time=0.008..0.023 rows=56 loops=1)

81. 861.262 1,865.456 ↓ 1.0 2,520,429 1

Hash (cost=102,163.70..102,163.70 rows=2,455,167 width=16) (actual time=1,865.456..1,865.456 rows=2,520,429 loops=1)

  • Buckets: 131,072 Batches: 64 Memory Usage: 2,872kB
82. 1,004.194 1,004.194 ↓ 1.0 2,520,429 1

Seq Scan on invoice i_3 (cost=0.00..102,163.70 rows=2,455,167 width=16) (actual time=0.004..1,004.194 rows=2,520,429 loops=1)

  • Filter: (NOT void)
  • Rows Removed by Filter: 17,510
83. 40.802 103.560 ↓ 1.0 126,855 1

Hash (cost=15,441.52..15,441.52 rows=126,356 width=8) (actual time=103.560..103.560 rows=126,855 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 3,514kB
84. 62.758 62.758 ↓ 1.0 126,855 1

Seq Scan on patient p_2 (cost=0.00..15,441.52 rows=126,356 width=8) (actual time=0.005..62.758 rows=126,855 loops=1)

  • Filter: (default_location_id = ANY ('{0,27,8,16,44,18,14,21,32,25,24,15,30,31,34,10,1,19,17,13,7,45,46,33,23,42,12,11,29,4,28,43,26,20,41,22,6,5,2,3}'::bigint[]))
  • Rows Removed by Filter: 3,269