explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I7kR

Settings
# exclusive inclusive rows x rows loops node
1. 130.339 100,500.335 ↑ 5.4 40,870 1

Nested Loop Left Join (cost=21,622.50..7,077,853.89 rows=219,940 width=195) (actual time=799.665..100,500.335 rows=40,870 loops=1)

2. 52.212 99,675.512 ↑ 5.4 40,852 1

Hash Left Join (cost=21,620.11..6,015,848.50 rows=219,940 width=187) (actual time=798.825..99,675.512 rows=40,852 loops=1)

  • Hash Cond: (ubd.dispute_sub_type_id = dst.id)
3. 57.980 99,622.953 ↑ 5.4 40,852 1

Hash Left Join (cost=21,589.41..6,013,041.20 rows=219,940 width=159) (actual time=798.461..99,622.953 rows=40,852 loops=1)

  • Hash Cond: (cpa.account_status_master_id = asm.id)
4. 57.204 99,564.610 ↑ 5.4 40,852 1

Hash Left Join (cost=21,576.26..6,010,003.91 rows=219,940 width=163) (actual time=798.080..99,564.610 rows=40,852 loops=1)

  • Hash Cond: (cpa.product_family_id = pf.id)
5. 68.594 99,507.281 ↑ 5.4 40,852 1

Hash Left Join (cost=21,574.31..6,007,271.68 rows=219,940 width=163) (actual time=797.932..99,507.281 rows=40,852 loops=1)

  • Hash Cond: (cpa.lender_id = len.id)
6. 86.171 99,436.490 ↑ 5.4 40,852 1

Nested Loop Left Join (cost=21,519.54..6,004,441.93 rows=219,940 width=140) (actual time=795.643..99,436.490 rows=40,852 loops=1)

7. 90.179 97,103.459 ↑ 5.4 40,852 1

Nested Loop Left Join (cost=21,506.00..2,754,113.63 rows=219,940 width=136) (actual time=794.687..97,103.459 rows=40,852 loops=1)

8. 0.000 1,051.932 ↑ 5.4 40,852 1

Gather (cost=21,495.69..478,835.25 rows=219,940 width=128) (actual time=785.462..1,051.932 rows=40,852 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
9. 2,182.089 8,610.788 ↑ 6.7 8,170 5

Nested Loop Left Join (cost=20,495.69..455,841.25 rows=54,985 width=128) (actual time=755.436..8,610.788 rows=8,170 loops=5)

10. 1,287.217 6,428.434 ↑ 6.7 8,170 5

Nested Loop Left Join (cost=20,495.12..342,121.11 rows=54,985 width=128) (actual time=754.420..6,428.434 rows=8,170 loops=5)

11. 3,850.881 5,141.062 ↑ 6.7 8,170 5

Nested Loop Left Join (cost=20,494.56..216,736.59 rows=54,985 width=128) (actual time=753.939..5,141.062 rows=8,170 loops=5)

12. 14.194 1,289.713 ↑ 6.7 8,170 5

Hash Left Join (cost=20,493.99..65,401.90 rows=54,985 width=65) (actual time=752.982..1,289.713 rows=8,170 loops=5)

  • Hash Cond: (uso.order_payment_id = o.id)
13. 17.872 949.237 ↑ 6.7 8,170 5

Hash Left Join (cost=11,900.86..56,114.58 rows=54,985 width=65) (actual time=425.350..949.237 rows=8,170 loops=5)

  • Hash Cond: (ubd.service_order_id = uso.id)
14. 507.458 507.458 ↑ 6.7 8,170 5

Parallel Seq Scan on user_bureau_disputes ubd (cost=0.00..43,519.53 rows=54,985 width=57) (actual time=0.024..507.458 rows=8,170 loops=5)

  • Filter: (dispute_type_id = 11)
  • Rows Removed by Filter: 431
15. 189.245 423.907 ↑ 1.0 246,224 5

Hash (cost=8,814.27..8,814.27 rows=246,927 width=16) (actual time=423.907..423.907 rows=246,224 loops=5)

  • Buckets: 262144 Batches: 1 Memory Usage: 13152kB
16. 234.662 234.662 ↑ 1.0 246,224 5

Seq Scan on user_service_orders uso (cost=0.00..8,814.27 rows=246,927 width=16) (actual time=0.023..234.662 rows=246,224 loops=5)

17. 147.464 326.282 ↑ 1.0 190,079 5

Hash (cost=6,208.06..6,208.06 rows=190,806 width=16) (actual time=326.282..326.282 rows=190,079 loops=5)

  • Buckets: 262144 Batches: 1 Memory Usage: 10946kB
18. 178.818 178.818 ↑ 1.0 190,079 5

Seq Scan on orders o (cost=0.00..6,208.06 rows=190,806 width=16) (actual time=0.032..178.818 rows=190,079 loops=5)

19. 0.468 0.468 ↑ 1.0 1 40,852

Index Scan using customer_profile_accounts_pkey on customer_profile_accounts cpa (cost=0.57..2.75 rows=1 width=67) (actual time=0.468..0.468 rows=1 loops=40,852)

  • Index Cond: (customer_profile_account_id = ubd.report_account_id)
20. 0.155 0.155 ↑ 1.0 1 40,852

Index Scan using idx_customer_profile_id_customer_profiles on customer_profiles cp (cost=0.56..2.28 rows=1 width=12) (actual time=0.155..0.155 rows=1 loops=40,852)

  • Index Cond: (customer_profile_id = cpa.customer_profile_id)
21. 0.265 0.265 ↑ 1.0 1 40,852

Index Only Scan using equifax_reports_pkey on equifax_reports er (cost=0.56..2.07 rows=1 width=4) (actual time=0.265..0.265 rows=1 loops=40,852)

  • Index Cond: (id = cp.report_id)
  • Heap Fetches: 6
22. 163.408 95,961.348 ↑ 1.0 1 40,852

Aggregate (cost=10.31..10.32 rows=1 width=8) (actual time=2.348..2.349 rows=1 loops=40,852)

23. 95,797.940 95,797.940 ↑ 1.0 2 40,852

Index Scan using idx_account_no_cpa on customer_profile_accounts (cost=0.57..10.31 rows=2 width=8) (actual time=1.901..2.345 rows=2 loops=40,852)

  • Index Cond: ((account_no)::text = (cpa.account_no)::text)
  • Filter: (created_on <= o.payment_date)
  • Rows Removed by Filter: 6
24. 122.556 2,246.860 ↓ 0.0 0 40,852

Limit (cost=13.54..14.76 rows=1 width=4) (actual time=0.054..0.055 rows=0 loops=40,852)

25. 122.556 2,124.304 ↓ 0.0 0 40,852

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

  • 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=19113
26. 259.553 2,001.748 ↓ 0.0 0 40,852

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

27. 817.040 817.040 ↓ 1.4 365 40,852

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

  • Index Cond: (product_family_id = cpa.product_family_id)
28. 443.355 443.355 ↑ 1.2 198 40,305

Bitmap Index Scan on idx_las_account_status_id (cost=0.00..4.47 rows=239 width=0) (actual time=0.011..0.011 rows=198 loops=40,305)

  • Index Cond: (account_status_id = cpa.account_status_master_id)
29. 481.800 481.800 ↑ 1.3 241 40,150

Bitmap Index Scan on idx_las_lender_id (cost=0.00..5.11 rows=324 width=0) (actual time=0.012..0.012 rows=241 loops=40,150)

  • Index Cond: (lender_id = cpa.lender_id)
30. 0.986 2.197 ↓ 1.1 1,205 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 93kB
31. 1.211 1.211 ↓ 1.1 1,205 1

Seq Scan on lenders len (cost=0.00..41.01 rows=1,101 width=31) (actual time=0.021..1.211 rows=1,205 loops=1)

32. 0.075 0.125 ↓ 1.2 49 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
33. 0.050 0.050 ↓ 1.2 49 1

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

34. 0.009 0.363 ↑ 23.3 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.354 0.354 ↑ 23.3 6 1

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

36. 0.011 0.347 ↑ 230.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.336 0.336 ↑ 230.0 4 1

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

38. 245.112 694.484 ↑ 1.0 1 40,852

Bitmap Heap Scan on leads l (cost=2.39..4.82 rows=1 width=16) (actual time=0.017..0.017 rows=1 loops=40,852)

  • 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=35622
39. 81.704 449.372 ↓ 0.0 0 40,852

BitmapOr (cost=2.39..2.39 rows=2 width=0) (actual time=0.011..0.011 rows=0 loops=40,852)

40. 81.704 81.704 ↓ 0.0 0 40,852

Bitmap Index Scan on idx_ubd_id_leads (cost=0.00..1.12 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=40,852)

  • Index Cond: (user_bureau_dispute_id = ubd.id)
41. 285.964 285.964 ↑ 1.0 1 40,852

Bitmap Index Scan on idx_ula_id_leads (cost=0.00..1.27 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=40,852)

  • Index Cond: (user_lender_account_id = ubd.user_lender_account_id)