explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rapI

Settings
# exclusive inclusive rows x rows loops node
1. 87.615 1,576.219 ↓ 27.6 52,397 1

Sort (cost=40,698.36..40,703.11 rows=1,899 width=407) (actual time=1,561.288..1,576.219 rows=52,397 loops=1)

  • Sort Key: arr."AdjustmentPostedDate
  • Sort Method: external merge Disk: 17,288kB
2. 260.371 1,488.604 ↓ 27.6 52,397 1

Nested Loop Left Join (cost=1,322.95..40,594.95 rows=1,899 width=407) (actual time=11.499..1,488.604 rows=52,397 loops=1)

3. 74.849 704.263 ↓ 27.6 52,397 1

Nested Loop Left Join (cost=1,322.39..38,235.62 rows=1,899 width=281) (actual time=11.453..704.263 rows=52,397 loops=1)

  • Filter: (((arr."RemittanceRefKey" IS NOT NULL) AND (r."DepositDate" >= '2020-01-01'::date) AND (r."DepositDate" <= '2020-06-01'::date)) OR ((arr."RemittanceRefKey" IS NULL) AND (cla."ClaimCreatedDate" >= '2019-12-31 18:00:00-05'::timestamp with time zone) AND (cla."ClaimCreatedDate" <= '2020-05-31 19:00:00-04'::timestamp with time zone)))
  • Rows Removed by Filter: 834
4. 26.254 576.183 ↓ 22.1 53,231 1

Nested Loop Left Join (cost=1,321.97..37,116.96 rows=2,405 width=274) (actual time=11.166..576.183 rows=53,231 loops=1)

5. 25.034 443.467 ↓ 22.1 53,231 1

Nested Loop (cost=1,321.54..35,866.33 rows=2,405 width=258) (actual time=11.150..443.467 rows=53,231 loops=1)

6. 24.983 296.217 ↓ 9.5 30,554 1

Nested Loop (cost=1,321.11..32,796.47 rows=3,212 width=177) (actual time=10.965..296.217 rows=30,554 loops=1)

7. 29.396 179.572 ↓ 8.0 30,554 1

Nested Loop (cost=1,320.68..31,008.69 rows=3,829 width=177) (actual time=10.953..179.572 rows=30,554 loops=1)

8. 7.821 58.514 ↓ 6.7 30,554 1

Hash Join (cost=1,320.26..28,895.49 rows=4,565 width=142) (actual time=10.939..58.514 rows=30,554 loops=1)

  • Hash Cond: (cla."BranchRefKey" = b."RefKey")
9. 9.215 49.600 ↓ 5.6 30,554 1

Hash Join (cost=1,109.23..28,670.17 rows=5,443 width=125) (actual time=9.826..49.600 rows=30,554 loops=1)

  • Hash Cond: (cla."InsuranceRefKey" = i."RefKey")
10. 6.319 30.912 ↓ 4.7 30,554 1

Nested Loop (cost=64.29..27,608.19 rows=6,489 width=92) (actual time=0.235..30.912 rows=30,554 loops=1)

11. 0.489 0.489 ↑ 1.0 8 1

Seq Scan on "Clinic" c (cost=0.00..175.00 rows=8 width=44) (actual time=0.040..0.489 rows=8 loops=1)

  • Filter: (("ClinicType" = ANY ('{12,15}'::integer[])) AND ("CorporationKey" = 397))
  • Rows Removed by Filter: 2,964
12. 20.992 24.104 ↓ 3.8 3,819 8

Bitmap Heap Scan on "Claim" cla (cost=64.29..3,419.01 rows=1,014 width=64) (actual time=0.617..3.013 rows=3,819 loops=8)

  • Recheck Cond: ("ClinicRefKey" = c."RefKey")
  • Heap Blocks: exact=11,025
13. 3.112 3.112 ↓ 3.9 4,002 8

Bitmap Index Scan on custindx_claim (cost=0.00..64.03 rows=1,014 width=0) (actual time=0.389..0.389 rows=4,002 loops=8)

  • Index Cond: ("ClinicRefKey" = c."RefKey")
14. 5.078 9.473 ↓ 1.0 25,384 1

Hash (cost=729.42..729.42 rows=25,242 width=49) (actual time=9.473..9.473 rows=25,384 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,393kB
15. 4.395 4.395 ↓ 1.0 25,384 1

Seq Scan on "Insurance" i (cost=0.00..729.42 rows=25,242 width=49) (actual time=0.004..4.395 rows=25,384 loops=1)

16. 0.581 1.093 ↑ 1.0 3,552 1

Hash (cost=166.01..166.01 rows=3,601 width=33) (actual time=1.093..1.093 rows=3,552 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 271kB
17. 0.512 0.512 ↑ 1.0 3,552 1

Seq Scan on "Branch" b (cost=0.00..166.01 rows=3,601 width=33) (actual time=0.006..0.512 rows=3,552 loops=1)

18. 91.662 91.662 ↑ 1.0 1 30,554

Index Scan using "Patient_ukey" on "Patient" p (cost=0.42..0.46 rows=1 width=51) (actual time=0.003..0.003 rows=1 loops=30,554)

  • Index Cond: ("RefKey" = cla."PatientRefKey")
19. 91.662 91.662 ↑ 1.0 1 30,554

Index Scan using "Episode_ukey" on "Episode" e (cost=0.43..0.47 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=30,554)

  • Index Cond: ("RefKey" = cla."EpisodeRefKey")
20. 122.216 122.216 ↑ 4.5 2 30,554

Index Only Scan using custindx_adjustmentrecordreport on "AdjustmentReportRecord" arr (cost=0.43..0.87 rows=9 width=97) (actual time=0.003..0.004 rows=2 loops=30,554)

  • Index Cond: ("ClaimRefKey" = cla."RefKey")
  • Filter: ((("RemittanceRefKey" IS NOT NULL) OR ("RemittanceRefKey" IS NULL)) AND ("DeletedAt" > now()))
  • Rows Removed by Filter: 0
  • Heap Fetches: 55,039
21. 106.462 106.462 ↑ 1.0 1 53,231

Index Scan using "RemittanceClaim_ukey" on "RemittanceClaim" rc (cost=0.43..0.52 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=53,231)

  • Index Cond: (arr."RemittanceClaimRefKey" = "RefKey")
22. 53.231 53.231 ↑ 1.0 1 53,231

Index Scan using "Remittance_ukey" on "Remittance" r (cost=0.42..0.45 rows=1 width=23) (actual time=0.001..0.001 rows=1 loops=53,231)

  • Index Cond: (arr."RemittanceRefKey" = "RefKey")
23. 523.970 523.970 ↑ 1.0 1 52,397

Index Scan using "LineItem_ukey" on "LineItem" li (cost=0.56..1.20 rows=1 width=36) (actual time=0.010..0.010 rows=1 loops=52,397)

  • Index Cond: (arr."LineItemBaseKeyList" = "LineItemBaseKeyList")