explain.depesz.com

PostgreSQL's explain analyze made readable

Result: blsb

Settings
# exclusive inclusive rows x rows loops node
1. 125.848 356,803.861 ↑ 10.7 40,119 1

Nested Loop Left Join (cost=20,336.61..15,702,052.53 rows=430,070 width=195) (actual time=846.489..356,803.861 rows=40,119 loops=1)

  • Buffers: shared hit=1412186 read=282564 dirtied=428 written=1642
2. 60.274 355,795.791 ↑ 10.7 40,101 1

Hash Left Join (cost=20,334.25..13,637,956.73 rows=430,070 width=187) (actual time=846.160..355,795.791 rows=40,101 loops=1)

  • Hash Cond: (ubd.dispute_sub_type_id = dst.id)
  • Buffers: shared hit=1213873 read=282206 dirtied=428 written=1642
3. 65.059 355,732.147 ↑ 10.7 40,101 1

Hash Left Join (cost=20,303.55..13,632,496.65 rows=430,070 width=159) (actual time=842.774..355,732.147 rows=40,101 loops=1)

  • Hash Cond: (cpa.account_status_master_id = asm.id)
  • Buffers: shared hit=1213873 read=282205 dirtied=428 written=1642
4. 64.142 355,667.055 ↑ 10.7 40,101 1

Hash Left Join (cost=20,290.40..13,626,570.12 rows=430,070 width=163) (actual time=842.717..355,667.055 rows=40,101 loops=1)

  • Hash Cond: (cpa.product_family_id = pf.id)
  • Buffers: shared hit=1213872 read=282205 dirtied=428 written=1642
5. 75.755 355,602.831 ↑ 10.7 40,101 1

Hash Left Join (cost=20,288.46..13,621,229.38 rows=430,070 width=163) (actual time=842.612..355,602.831 rows=40,101 loops=1)

  • Hash Cond: (cpa.lender_id = len.id)
  • Buffers: shared hit=1213871 read=282205 dirtied=428 written=1642
6. 97.761 355,525.357 ↑ 10.7 40,101 1

Nested Loop Left Join (cost=20,233.69..13,615,748.44 rows=430,070 width=140) (actual time=840.849..355,525.357 rows=40,101 loops=1)

  • Buffers: shared hit=1213838 read=282205 dirtied=428 written=1642
7. 124.571 352,861.132 ↑ 10.7 40,101 1

Nested Loop Left Join (cost=20,220.15..7,260,066.46 rows=430,070 width=136) (actual time=830.078..352,861.132 rows=40,101 loops=1)

  • Buffers: shared hit=839119 read=282154 dirtied=428 written=1642
8. 87.834 86,666.426 ↑ 10.7 40,101 1

Nested Loop Left Join (cost=20,209.83..2,810,994.12 rows=430,070 width=128) (actual time=778.238..86,666.426 rows=40,101 loops=1)

  • Buffers: shared hit=551664 read=98623 dirtied=425 written=692
9. 103.283 59,590.619 ↑ 10.7 40,101 1

Nested Loop Left Join (cost=20,209.27..2,186,854.84 rows=430,070 width=128) (actual time=762.855..59,590.619 rows=40,101 loops=1)

  • Buffers: shared hit=375302 read=78700 dirtied=385 written=460
10. 77.846 49,662.591 ↑ 10.7 40,101 1

Hash Left Join (cost=20,208.71..1,223,127.28 rows=430,070 width=128) (actual time=760.203..49,662.591 rows=40,101 loops=1)

  • Hash Cond: (uso.order_payment_id = o.id)
  • Buffers: shared hit=182775 read=70841 dirtied=385 written=351
11. 133.057 49,282.278 ↑ 10.7 40,101 1

Nested Loop Left Join (cost=11,737.48..1,209,226.44 rows=430,070 width=128) (actual time=456.434..49,282.278 rows=40,101 loops=1)

  • Buffers: shared hit=178536 read=70841 dirtied=385 written=351
12. 85.022 4,516.808 ↑ 10.7 40,101 1

Hash Left Join (cost=11,736.91..65,626.31 rows=430,070 width=65) (actual time=444.890..4,516.808 rows=40,101 loops=1)

  • Hash Cond: (ubd.service_order_id = uso.id)
  • Buffers: shared hit=10314 read=38604 dirtied=382
13. 3,988.343 3,988.343 ↑ 10.7 40,101 1

Seq Scan on user_bureau_disputes ubd (cost=0.00..48,459.78 rows=430,070 width=57) (actual time=0.035..3,988.343 rows=40,101 loops=1)

  • Filter: (dispute_type_id = 11)
  • Rows Removed by Filter: 2146
  • Buffers: shared hit=4192 read=38604 dirtied=382
14. 207.372 443.443 ↑ 1.0 243,553 1

Hash (cost=8,615.85..8,615.85 rows=249,685 width=16) (actual time=443.443..443.443 rows=243,553 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 13027kB
  • Buffers: shared hit=6119
15. 236.071 236.071 ↑ 1.0 243,553 1

Seq Scan on user_service_orders uso (cost=0.00..8,615.85 rows=249,685 width=16) (actual time=0.006..236.071 rows=243,553 loops=1)

  • Buffers: shared hit=6119
16. 44,632.413 44,632.413 ↑ 1.0 1 40,101

Index Scan using customer_profile_accounts_pkey on customer_profile_accounts cpa (cost=0.57..2.66 rows=1 width=67) (actual time=1.113..1.113 rows=1 loops=40,101)

  • Index Cond: (customer_profile_account_id = ubd.report_account_id)
  • Buffers: shared hit=168222 read=32237 dirtied=3 written=351
17. 139.936 302.467 ↑ 1.0 187,411 1

Hash (cost=6,119.99..6,119.99 rows=188,099 width=16) (actual time=302.467..302.467 rows=187,411 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10823kB
  • Buffers: shared hit=4239
18. 162.531 162.531 ↑ 1.0 187,411 1

Seq Scan on orders o (cost=0.00..6,119.99 rows=188,099 width=16) (actual time=0.014..162.531 rows=187,411 loops=1)

  • Buffers: shared hit=4239
19. 9,824.745 9,824.745 ↑ 1.0 1 40,101

Index Scan using idx_customer_profile_id_customer_profiles on customer_profiles cp (cost=0.56..2.24 rows=1 width=12) (actual time=0.245..0.245 rows=1 loops=40,101)

  • Index Cond: (customer_profile_id = cpa.customer_profile_id)
  • Buffers: shared hit=192527 read=7859 written=109
20. 26,987.973 26,987.973 ↑ 1.0 1 40,101

Index Only Scan using equifax_reports_pkey on equifax_reports er (cost=0.56..1.45 rows=1 width=4) (actual time=0.673..0.673 rows=1 loops=40,101)

  • Index Cond: (id = cp.report_id)
  • Heap Fetches: 18696
  • Buffers: shared hit=176362 read=19923 dirtied=40 written=232
21. 200.505 266,070.135 ↑ 1.0 1 40,101

Aggregate (cost=10.31..10.32 rows=1 width=8) (actual time=6.635..6.635 rows=1 loops=40,101)

  • Buffers: shared hit=287455 read=183531 dirtied=3 written=950
22. 265,869.630 265,869.630 ↑ 1.0 2 40,101

Index Scan using idx_account_no_cpa on customer_profile_accounts (cost=0.57..10.31 rows=2 width=8) (actual time=5.467..6.630 rows=2 loops=40,101)

  • Index Cond: ((account_no)::text = (cpa.account_no)::text)
  • Filter: (created_on <= o.payment_date)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=287455 read=183531 dirtied=3 written=950
23. 160.404 2,566.464 ↓ 0.0 0 40,101

Limit (cost=13.54..14.76 rows=1 width=4) (actual time=0.063..0.064 rows=0 loops=40,101)

  • Buffers: shared hit=374719 read=51
24. 160.404 2,406.060 ↓ 0.0 0 40,101

Bitmap Heap Scan on lender_account_status las (cost=13.54..14.76 rows=1 width=4) (actual time=0.060..0.060 rows=0 loops=40,101)

  • Recheck Cond: ((product_family_id = cpa.product_family_id) AND (account_status_id = cpa.account_status_master_id) AND (lender_id = cpa.lender_id))
  • Heap Blocks: exact=18865
  • Buffers: shared hit=374719 read=51
25. 336.445 2,245.656 ↓ 0.0 0 40,101

BitmapAnd (cost=13.54..13.54 rows=1 width=0) (actual time=0.056..0.056 rows=0 loops=40,101)

  • Buffers: shared hit=355880 read=25
26. 922.323 922.323 ↓ 1.4 365 40,101

Bitmap Index Scan on idx_las_product_family_id (cost=0.00..3.45 rows=262 width=0) (actual time=0.023..0.023 rows=365 loops=40,101)

  • Index Cond: (product_family_id = cpa.product_family_id)
  • Buffers: shared hit=124473 read=10
27. 474.636 474.636 ↑ 1.2 198 39,553

Bitmap Index Scan on idx_las_account_status_id (cost=0.00..4.47 rows=239 width=0) (actual time=0.012..0.012 rows=198 loops=39,553)

  • Index Cond: (account_status_id = cpa.account_status_master_id)
  • Buffers: shared hit=118339 read=6
28. 512.252 512.252 ↑ 1.3 241 39,404

Bitmap Index Scan on idx_las_lender_id (cost=0.00..5.11 rows=324 width=0) (actual time=0.013..0.013 rows=241 loops=39,404)

  • Index Cond: (lender_id = cpa.lender_id)
  • Buffers: shared hit=113068 read=9
29. 0.821 1.719 ↓ 1.1 1,196 1

Hash (cost=41.01..41.01 rows=1,101 width=31) (actual time=1.719..1.719 rows=1,196 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 93kB
  • Buffers: shared hit=30
30. 0.898 0.898 ↓ 1.1 1,196 1

Seq Scan on lenders len (cost=0.00..41.01 rows=1,101 width=31) (actual time=0.010..0.898 rows=1,196 loops=1)

  • Buffers: shared hit=30
31. 0.040 0.082 ↓ 1.2 49 1

Hash (cost=1.42..1.42 rows=42 width=8) (actual time=0.082..0.082 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
32. 0.042 0.042 ↓ 1.2 49 1

Seq Scan on product_families pf (cost=0.00..1.42 rows=42 width=8) (actual time=0.011..0.042 rows=49 loops=1)

  • Buffers: shared hit=1
33. 0.012 0.033 ↑ 23.3 6 1

Hash (cost=11.40..11.40 rows=140 width=4) (actual time=0.033..0.033 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
34. 0.021 0.021 ↑ 23.3 6 1

Seq Scan on account_status_master asm (cost=0.00..11.40 rows=140 width=4) (actual time=0.017..0.021 rows=6 loops=1)

  • Buffers: shared hit=1
35. 0.013 3.370 ↑ 230.0 4 1

Hash (cost=19.20..19.20 rows=920 width=36) (actual time=3.370..3.370 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
36. 3.357 3.357 ↑ 230.0 4 1

Seq Scan on dispute_sub_types dst (cost=0.00..19.20 rows=920 width=36) (actual time=3.355..3.357 rows=4 loops=1)

  • Buffers: shared read=1
37. 240.606 882.222 ↑ 1.0 1 40,101

Bitmap Heap Scan on leads l (cost=2.36..4.79 rows=1 width=16) (actual time=0.021..0.022 rows=1 loops=40,101)

  • Recheck Cond: ((user_bureau_dispute_id = ubd.id) OR (user_lender_account_id = ubd.user_lender_account_id))
  • Filter: (((ubd.user_lender_account_id IS NULL) AND (user_bureau_dispute_id = ubd.id)) OR ((ubd.user_lender_account_id IS NOT NULL) AND (user_lender_account_id = ubd.user_lender_account_id)))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=35169
  • Buffers: shared hit=198313 read=358
38. 80.202 641.616 ↓ 0.0 0 40,101

BitmapOr (cost=2.36..2.36 rows=2 width=0) (actual time=0.016..0.016 rows=0 loops=40,101)

  • Buffers: shared hit=163144 read=358
39. 120.303 120.303 ↓ 0.0 0 40,101

Bitmap Index Scan on idx_ubd_id_leads (cost=0.00..1.11 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=40,101)

  • Index Cond: (user_bureau_dispute_id = ubd.id)
  • Buffers: shared hit=80399 read=7
40. 441.111 441.111 ↑ 1.0 1 40,101

Bitmap Index Scan on idx_ula_id_leads (cost=0.00..1.25 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=40,101)

  • Index Cond: (user_lender_account_id = ubd.user_lender_account_id)
  • Buffers: shared hit=82745 read=351