explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q897

Settings
# exclusive inclusive rows x rows loops node
1. 0.049 2,617.165 ↑ 9.7 9 1

Hash Left Join (cost=480,853.39..492,704.89 rows=87 width=163) (actual time=2,410.340..2,617.165 rows=9 loops=1)

  • Hash Cond: (cr_a.collection_report_result_id = cr_a_result.id)
2. 0.006 2,617.103 ↑ 9.7 9 1

Nested Loop Left Join (cost=480,852.19..492,699.88 rows=87 width=497) (actual time=2,410.304..2,617.103 rows=9 loops=1)

3. 0.018 2,617.088 ↑ 9.7 9 1

Hash Left Join (cost=480,851.90..492,500.85 rows=87 width=497) (actual time=2,410.298..2,617.088 rows=9 loops=1)

  • Hash Cond: (bl.billing_loan_state_id = bls.id)
4. 0.008 2,617.052 ↑ 9.7 9 1

Nested Loop Left Join (cost=480,850.60..492,499.25 rows=87 width=462) (actual time=2,410.267..2,617.052 rows=9 loops=1)

5. 0.176 2,617.035 ↑ 9.7 9 1

Hash Left Join (cost=480,850.18..492,311.27 rows=87 width=462) (actual time=2,410.260..2,617.035 rows=9 loops=1)

  • Hash Cond: (a.obj_id = c.application_id)
6. 0.017 2,588.997 ↑ 9.7 9 1

Nested Loop Left Join (cost=479,096.23..490,556.96 rows=87 width=450) (actual time=2,382.228..2,588.997 rows=9 loops=1)

7. 0.011 2,588.863 ↑ 9.7 9 1

Hash Left Join (cost=479,095.80..489,303.89 rows=87 width=446) (actual time=2,382.193..2,588.863 rows=9 loops=1)

  • Hash Cond: (client_personal.client_id = sc.client_id)
8. 0.013 2,588.365 ↑ 9.7 9 1

Hash Left Join (cost=479,063.44..489,271.29 rows=87 width=338) (actual time=2,381.701..2,588.365 rows=9 loops=1)

  • Hash Cond: (sgg.service_id = ss.obj_id)
9. 0.024 2,588.344 ↑ 9.7 9 1

Hash Left Join (cost=479,062.28..489,269.78 rows=87 width=335) (actual time=2,381.687..2,588.344 rows=9 loops=1)

  • Hash Cond: (sg.service_id = s.obj_id)
10. 98.700 2,588.301 ↑ 9.7 9 1

Hash Right Join (cost=479,061.12..489,268.26 rows=87 width=332) (actual time=2,381.652..2,588.301 rows=9 loops=1)

  • Hash Cond: (client_personal.inn = vself.inn)
11. 425.782 2,488.799 ↓ 1.2 399,180 1

Unique (cost=478,599.73..484,301.44 rows=327,604 width=309) (actual time=1,658.058..2,488.799 rows=399,180 loops=1)

12. 1,450.106 2,063.017 ↓ 1.0 1,143,944 1

Sort (cost=478,599.73..481,450.58 rows=1,140,342 width=309) (actual time=1,658.056..2,063.017 rows=1,143,944 loops=1)

  • Sort Key: client_personal.client_id, client_personal.id DESC
  • Sort Method: external merge Disk: 75416kB
13. 612.911 612.911 ↓ 1.0 1,143,944 1

Seq Scan on client_personal (cost=0.00..36,471.42 rows=1,140,342 width=309) (actual time=0.021..612.911 rows=1,143,944 loops=1)

14. 0.014 0.802 ↑ 5.1 9 1

Hash (cost=460.82..460.82 rows=46 width=278) (actual time=0.801..0.802 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.051 0.788 ↑ 5.1 9 1

Nested Loop Left Join (cost=6.08..460.82 rows=46 width=278) (actual time=0.188..0.788 rows=9 loops=1)

16. 0.014 0.719 ↑ 5.1 9 1

Nested Loop Left Join (cost=5.80..391.14 rows=46 width=220) (actual time=0.138..0.719 rows=9 loops=1)

17. 0.038 0.633 ↑ 5.1 9 1

Nested Loop (cost=5.37..284.72 rows=46 width=93) (actual time=0.121..0.633 rows=9 loops=1)

18. 0.027 0.166 ↓ 2.8 39 1

Nested Loop Left Join (cost=4.82..148.11 rows=14 width=78) (actual time=0.043..0.166 rows=39 loops=1)

19. 0.037 0.061 ↓ 2.8 39 1

Bitmap Heap Scan on scoring_contact_all vall (cost=4.54..59.94 rows=14 width=20) (actual time=0.030..0.061 rows=39 loops=1)

  • Recheck Cond: (inn = '3133204502'::text)
  • Heap Blocks: exact=24
20. 0.024 0.024 ↓ 2.8 39 1

Bitmap Index Scan on scoring_contact_all_inn_idx (cost=0.00..4.54 rows=14 width=0) (actual time=0.023..0.024 rows=39 loops=1)

  • Index Cond: (inn = '3133204502'::text)
21. 0.078 0.078 ↑ 1.0 1 39

Index Scan using service_guide_pkey on service_guide sg (cost=0.28..6.30 rows=1 width=66) (actual time=0.002..0.002 rows=1 loops=39)

  • Index Cond: (obj_id = vall.service_guide_id)
22. 0.429 0.429 ↓ 0.0 0 39

Index Only Scan using scoring_contact_all_service_id_value_inn_uniq on scoring_contact_all vself (cost=0.56..9.74 rows=2 width=31) (actual time=0.011..0.011 rows=0 loops=39)

  • Index Cond: (value_norm = vall.value_norm)
  • Filter: (inn <> '3133204502'::text)
  • Rows Removed by Filter: 3
  • Heap Fetches: 55
23. 0.072 0.072 ↓ 0.0 0 9

Index Scan using scoring_inn_blocking_inn_scoring_inn_blocking_type_id_key on scoring_inn_blocking si (cost=0.42..2.30 rows=1 width=137) (actual time=0.008..0.008 rows=0 loops=9)

  • Index Cond: (inn = vself.inn)
  • Filter: is_forbidden
24. 0.018 0.018 ↑ 1.0 1 9

Index Scan using service_guide_pkey on service_guide sgg (cost=0.28..1.51 rows=1 width=66) (actual time=0.002..0.002 rows=1 loops=9)

  • Index Cond: (obj_id = vself.service_guide_id)
25. 0.005 0.019 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=11) (actual time=0.019..0.019 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.014 0.014 ↑ 1.0 7 1

Seq Scan on service s (cost=0.00..1.07 rows=7 width=11) (actual time=0.010..0.014 rows=7 loops=1)

27. 0.004 0.008 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=11) (actual time=0.007..0.008 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.004 0.004 ↑ 1.0 7 1

Seq Scan on service ss (cost=0.00..1.07 rows=7 width=11) (actual time=0.002..0.004 rows=7 loops=1)

29. 0.258 0.487 ↓ 1.0 708 1

Hash (cost=23.83..23.83 rows=683 width=116) (actual time=0.486..0.487 rows=708 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 112kB
30. 0.229 0.229 ↓ 1.0 708 1

Seq Scan on scoring_client_blocking sc (cost=0.00..23.83 rows=683 width=116) (actual time=0.004..0.229 rows=708 loops=1)

31. 0.117 0.117 ↓ 0.0 0 9

Index Scan using application_m_cl_inn_idx on application a (cost=0.43..14.38 rows=2 width=15) (actual time=0.013..0.013 rows=0 loops=9)

  • Index Cond: (m_cl_inn = vself.inn)
  • Filter: (application_state_id = 10)
  • Rows Removed by Filter: 8
32. 13.139 27.862 ↓ 1.0 41,953 1

Hash (cost=1,230.09..1,230.09 rows=41,909 width=16) (actual time=27.861..27.862 rows=41,953 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2191kB
33. 14.723 14.723 ↓ 1.0 41,953 1

Seq Scan on collection c (cost=0.00..1,230.09 rows=41,909 width=16) (actual time=0.004..14.723 rows=41,953 loops=1)

34. 0.009 0.009 ↓ 0.0 0 9

Index Scan using billing_loan_application_id_uniq on billing_loan bl (cost=0.42..2.16 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=9)

  • Index Cond: (a.obj_id = application_id)
35. 0.010 0.018 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=43) (actual time=0.018..0.018 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.008 0.008 ↑ 1.0 13 1

Seq Scan on billing_loan_state bls (cost=0.00..1.13 rows=13 width=43) (actual time=0.004..0.008 rows=13 loops=1)

37. 0.009 0.009 ↓ 0.0 0 9

Index Scan using collection_report_last_collection_report_id_is_history on collection_report cr_a (cost=0.29..2.28 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=9)

  • Index Cond: ((c.id = collection_id) AND (is_history = false))
  • Filter: (NOT is_history)
38. 0.006 0.013 ↓ 1.1 10 1

Hash (cost=1.09..1.09 rows=9 width=18) (actual time=0.013..0.013 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.007 0.007 ↓ 1.1 10 1

Seq Scan on collection_report_result cr_a_result (cost=0.00..1.09 rows=9 width=18) (actual time=0.004..0.007 rows=10 loops=1)