explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rWkK

Settings
# exclusive inclusive rows x rows loops node
1. 0.340 19,515.684 ↑ 46.4 409 1

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

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

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

  • Group Key: m.referral_id, m.name
3. 0.632 19,514.353 ↑ 233.8 812 1

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

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

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

5. 0.324 19,513.357 ↑ 233.8 812 1

Append (cost=169,719.55..1,211,579.79 rows=189,850 width=176) (actual time=5,465.915..19,513.357 rows=812 loops=1)

6. 39.048 5,520.958 ↑ 3.1 403 1

GroupAggregate (cost=169,719.55..169,794.55 rows=1,250 width=158) (actual time=5,465.914..5,520.958 rows=403 loops=1)

  • Group Key: p_1.referral_id, r.name
7. 117.584 5,481.910 ↓ 69.6 86,957 1

Sort (cost=169,719.55..169,722.68 rows=1,250 width=46) (actual time=5,458.925..5,481.910 rows=86,957 loops=1)

  • Sort Key: p_1.referral_id, r.name
  • Sort Method: external merge Disk: 4520kB
8. 55.026 5,364.326 ↓ 69.6 86,957 1

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

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

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

10. 26.146 957.566 ↓ 312.5 21,253 1

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

11. 54.843 931.420 ↓ 312.6 21,255 1

Sort (cost=148,777.31..148,777.48 rows=68 width=607) (actual time=916.746..931.420 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: 10744kB
12. 68.326 876.577 ↓ 312.6 21,255 1

Hash Left Join (cost=30,600.87..148,775.24 rows=68 width=607) (actual time=467.837..876.577 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: 21574
  • 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. 15.979 415.620 ↓ 1.6 21,455 1

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

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

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

15. 29.114 319.018 ↓ 1.5 21,455 1

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

16. 9.264 246.992 ↓ 1.5 21,456 1

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

17. 0.011 0.011 ↑ 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.011 rows=3 loops=1)

  • Filter: (report_metric_id = 1)
  • Rows Removed by Filter: 23
18. 41.367 237.717 ↓ 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=66.580..79.239 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: 4013
  • Filter: (NOT void)
  • Rows Removed by Filter: 420
  • Heap Blocks: exact=21323
19. 2.775 196.350 ↓ 0.0 0 3

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

20. 7.086 7.086 ↓ 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.362..2.362 rows=22,544 loops=3)

  • Index Cond: (service_id = rmm.service_id)
21. 186.489 186.489 ↓ 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=62.163..62.163 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.014 0.033 ↑ 1.0 41 1

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

  • Buckets: 1024 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. 45.169 392.631 ↓ 7.2 66,207 1

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

  • Buckets: 32768 (originally 16384) Batches: 4 (originally 1) Memory Usage: 3841kB
27. 104.761 347.462 ↓ 7.2 66,207 1

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

28. 36.913 110.281 ↓ 7.1 66,210 1

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

29. 0.042 0.168 ↑ 1.0 3 1

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

  • Merge Cond: (s.id = rmm_1.service_id)
30. 0.106 0.106 ↑ 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.014..0.106 rows=163 loops=1)

  • Heap Fetches: 163
31. 0.009 0.020 ↑ 1.0 3 1

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

  • Sort Key: rmm_1.service_id
  • Sort Method: quicksort Memory: 25kB
32. 0.011 0.011 ↑ 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.007..0.011 rows=3 loops=1)

  • Filter: (report_metric_id = 1)
  • Rows Removed by Filter: 23
33. 73.200 73.200 ↓ 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.011..24.400 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.489 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.041 ↑ 1.0 56 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
44. 0.023 0.023 ↑ 1.0 56 1

Seq Scan on payment_type pt (cost=0.00..1.56 rows=56 width=9) (actual time=0.008..0.023 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: 83228
47. 0.542 1.145 ↓ 1.0 1,757 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 115kB
48. 0.603 0.603 ↓ 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.603 rows=1,757 loops=1)

49. 0.187 13,992.075 ↑ 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,932.390..13,992.075 rows=409 loops=1)

50. 41.828 13,991.888 ↑ 461.1 409 1

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

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

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

  • Sort Key: p_3.referral_id, r2.name
  • Sort Method: external merge Disk: 4760kB
52. 85.517 13,853.691 ↑ 10.6 91,776 1

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

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

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

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

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

55. 56.628 466.192 ↓ 1.6 21,455 1

Sort (cost=144,100.78..144,134.61 rows=13,535 width=607) (actual time=456.119..466.192 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: 10880kB
56. 15.277 409.564 ↓ 1.6 21,455 1

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

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

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

58. 28.368 313.735 ↓ 1.5 21,455 1

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

59. 8.834 242.455 ↓ 1.5 21,456 1

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

60. 0.017 0.017 ↑ 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.005..0.017 rows=3 loops=1)

  • Filter: (report_metric_id = 1)
  • Rows Removed by Filter: 23
61. 40.566 233.604 ↓ 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=65.469..77.868 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: 4013
  • Filter: (NOT void)
  • Rows Removed by Filter: 420
  • Heap Blocks: exact=21323
62. 2.718 193.038 ↓ 0.0 0 3

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

63. 6.963 6.963 ↓ 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.321..2.321 rows=22,544 loops=3)

  • Index Cond: (service_id = rmm_2.service_id)
64. 183.357 183.357 ↓ 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=61.119..61.119 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.036 ↑ 1.0 41 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
68. 0.022 0.022 ↑ 1.0 41 1

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

69. 0.529 1.126 ↓ 1.0 1,757 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 115kB
70. 0.597 0.597 ↓ 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.597 rows=1,757 loops=1)

71. 216.975 13,265.220 ↑ 2.7 601,708 1

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

  • Buckets: 65536 Batches: 32 Memory Usage: 1460kB
72. 417.871 13,048.245 ↑ 2.7 601,708 1

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

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

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

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

Hash Left Join (cost=235,007.25..416,748.28 rows=1,705,646 width=24) (actual time=2,700.043..9,832.707 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: 2546106
75. 4,906.687 8,850.775 ↑ 1.0 3,168,941 1

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

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

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

77. 1,124.998 2,699.199 ↑ 1.0 3,168,952 1

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

  • Buckets: 65536 Batches: 64 Memory Usage: 2880kB
78. 1,574.201 1,574.201 ↑ 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.005..1,574.201 rows=3,168,952 loops=1)

  • Filter: (NOT void)
  • Rows Removed by Filter: 691394
79. 0.019 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: 1024 Batches: 1 Memory Usage: 11kB
80. 0.024 0.024 ↑ 1.0 56 1

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

81. 855.756 1,859.566 ↓ 1.0 2,520,429 1

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

  • Buckets: 131072 Batches: 64 Memory Usage: 2872kB
82. 1,003.810 1,003.810 ↓ 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.005..1,003.810 rows=2,520,429 loops=1)

  • Filter: (NOT void)
  • Rows Removed by Filter: 17509
83. 40.423 102.876 ↓ 1.0 126,855 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3514kB
84. 62.453 62.453 ↓ 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.453 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: 3269
Planning time : 10.496 ms