explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vWHl

Settings
# exclusive inclusive rows x rows loops node
1. 28.911 35,174.087 ↑ 5.2 11,895 1

GroupAggregate (cost=10,000,413,480.95..10,000,423,363.69 rows=62,192 width=20) (actual time=35,141.669..35,174.087 rows=11,895 loops=1)

  • Group Key: last_app.client_id, cls.level, last_app.create_time
  • Filter: (((((array_agg(sib.is_forbidden) || array_agg(stb.is_forbidden)) || array_agg(scb.is_forbidden)) || array_agg(spb.is_forbidden)) @> '{t}'::boolean[]) IS FALSE)
  • Rows Removed by Filter: 1646
2.          

CTE last_app

3. 69.916 632.266 ↑ 1.3 70,975 1

Unique (cost=146,139.11..147,121.88 rows=92,709 width=372) (actual time=453.281..632.266 rows=70,975 loops=1)

4. 419.927 562.350 ↓ 1.0 199,141 1

Sort (cost=146,139.11..146,630.50 rows=196,553 width=372) (actual time=453.278..562.350 rows=199,141 loops=1)

  • Sort Key: application.client_id, application.obj_id DESC
  • Sort Method: external merge Disk: 56696kB
5. 124.175 142.423 ↓ 1.0 199,141 1

Bitmap Heap Scan on application (cost=4,151.71..61,671.63 rows=196,553 width=372) (actual time=27.031..142.423 rows=199,141 loops=1)

  • Recheck Cond: (application_state_id = 10)
  • Heap Blocks: exact=50339
6. 18.248 18.248 ↓ 1.0 199,726 1

Bitmap Index Scan on application_state_id_idx (cost=0.00..4,102.57 rows=196,553 width=0) (actual time=18.247..18.248 rows=199,726 loops=1)

  • Index Cond: (application_state_id = 10)
7. 10.776 35,145.176 ↑ 29.5 13,607 1

Sort (cost=10,000,266,359.07..10,000,267,361.19 rows=400,849 width=20) (actual time=35,141.619..35,145.176 rows=13,607 loops=1)

  • Sort Key: last_app.client_id, cls.level, last_app.create_time
  • Sort Method: quicksort Memory: 1075kB
8. 19.277 35,134.400 ↑ 29.5 13,607 1

Merge Left Join (cost=10,000,211,657.52..10,000,220,833.16 rows=400,849 width=20) (actual time=35,106.550..35,134.400 rows=13,607 loops=1)

  • Merge Cond: ((lower(COALESCE((cp.passer || cp.passno), cp.passid_number))) = (lower(spb.passport)))
  • Join Filter: (scb.is_forbidden IS TRUE)
  • Rows Removed by Join Filter: 606
9. 62.388 35,031.177 ↓ 2.4 13,607 1

Sort (cost=210,429.62..210,443.85 rows=5,692 width=41) (actual time=35,027.318..35,031.177 rows=13,607 loops=1)

  • Sort Key: (lower(COALESCE((cp.passer || cp.passno), cp.passid_number)))
  • Sort Method: quicksort Memory: 1482kB
10. 40.154 34,968.789 ↓ 2.4 13,607 1

Hash Left Join (cost=116,809.92..210,074.59 rows=5,692 width=41) (actual time=21,525.566..34,968.789 rows=13,607 loops=1)

  • Hash Cond: (last_app.m_telephone = stb.tel)
11. 335.180 34,827.798 ↓ 2.6 13,555 1

Merge Left Join (cost=112,889.71..206,052.32 rows=5,159 width=72) (actual time=21,424.438..34,827.798 rows=13,555 loops=1)

  • Merge Cond: (c.personal_id = cp.id)
12. 18.797 21,426.073 ↓ 2.6 13,555 1

Sort (cost=112,889.28..112,902.18 rows=5,159 width=54) (actual time=21,419.964..21,426.073 rows=13,555 loops=1)

  • Sort Key: c.personal_id
  • Sort Method: quicksort Memory: 1443kB
13. 141.704 21,407.276 ↓ 2.6 13,555 1

Merge Left Join (cost=65,765.80..112,571.15 rows=5,159 width=54) (actual time=13,232.373..21,407.276 rows=13,555 loops=1)

  • Merge Cond: (last_app.client_id = c.id)
14. 26.931 13,617.338 ↓ 2.6 13,555 1

Merge Left Join (cost=65,765.38..69,923.62 rows=5,159 width=50) (actual time=13,228.764..13,617.338 rows=13,555 loops=1)

  • Merge Cond: (last_app.client_id = cls.client_id)
15. 14.764 13,234.948 ↓ 2.6 13,555 1

Sort (cost=65,765.09..65,777.98 rows=5,159 width=46) (actual time=13,228.057..13,234.948 rows=13,555 loops=1)

  • Sort Key: last_app.client_id
  • Sort Method: quicksort Memory: 1443kB
16. 379.554 13,220.184 ↓ 2.6 13,555 1

Merge Left Join (cost=24,355.06..65,446.96 rows=5,159 width=46) (actual time=12,329.440..13,220.184 rows=13,555 loops=1)

  • Merge Cond: (last_app.m_cl_inn = sib.inn)
17. 59.322 12,306.376 ↓ 2.6 13,541 1

Sort (cost=24,354.64..24,367.52 rows=5,151 width=77) (actual time=12,301.321..12,306.376 rows=13,541 loops=1)

  • Sort Key: last_app.m_cl_inn
  • Sort Method: quicksort Memory: 1442kB
18. 19.869 12,247.054 ↓ 2.6 13,541 1

Hash Left Join (cost=20,665.96..24,037.06 rows=5,151 width=77) (actual time=2,301.895..12,247.054 rows=13,541 loops=1)

  • Hash Cond: (last_app.client_id = scb.client_id)
19. 9,650.036 12,213.455 ↓ 2.6 13,541 1

Hash Left Join (cost=20,561.74..23,919.25 rows=5,151 width=76) (actual time=2,288.154..12,213.455 rows=13,541 loops=1)

  • Hash Cond: (last_app.obj_id = bl.application_id)
  • Filter: (get_max_dpd_for_loan(bl.obj_id) < 30)
  • Rows Removed by Filter: 10770
20. 732.693 732.693 ↓ 1.6 24,311 1

CTE Scan on last_app (cost=0.00..2,085.95 rows=15,452 width=80) (actual time=453.289..732.693 rows=24,311 loops=1)

  • Filter: ((closed_loan IS TRUE) AND (closed_loan_count > 0))
  • Rows Removed by Filter: 46664
21. 86.896 1,830.726 ↓ 1.0 214,361 1

Hash (cost=17,047.08..17,047.08 rows=214,213 width=8) (actual time=1,830.726..1,830.726 rows=214,361 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 3130kB
22. 1,743.830 1,743.830 ↓ 1.0 214,361 1

Index Scan using billing_loan_application_id_uniq on billing_loan bl (cost=0.42..17,047.08 rows=214,213 width=8) (actual time=2.192..1,743.830 rows=214,361 loops=1)

23. 0.296 13.730 ↓ 1.0 901 1

Hash (cost=93.12..93.12 rows=888 width=5) (actual time=13.730..13.730 rows=901 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
24. 13.434 13.434 ↓ 1.0 901 1

Index Scan using scoring_client_blocking_client_id_key on scoring_client_blocking scb (cost=0.28..93.12 rows=888 width=5) (actual time=1.520..13.434 rows=901 loops=1)

  • Filter: (is_forbidden IS TRUE)
  • Rows Removed by Filter: 9
25. 534.254 534.254 ↑ 1.0 717,960 1

Index Scan using scoring_inn_blocking_inn_index on scoring_inn_blocking sib (cost=0.42..39,132.74 rows=752,896 width=11) (actual time=3.968..534.254 rows=717,960 loops=1)

  • Filter: (is_forbidden IS TRUE)
  • Rows Removed by Filter: 13925
26. 355.459 355.459 ↑ 1.0 56,279 1

Index Scan using client_loyalty_status_client_id_key on client_loyalty_status cls (cost=0.29..3,938.75 rows=56,958 width=8) (actual time=0.701..355.459 rows=56,279 loops=1)

27. 7,648.234 7,648.234 ↑ 1.0 483,602 1

Index Scan using client_pkey on client c (cost=0.42..41,371.94 rows=484,443 width=8) (actual time=3.104..7,648.234 rows=483,602 loops=1)

28. 13,066.545 13,066.545 ↓ 1.0 1,381,815 1

Index Scan using client_personal_pkey on client_personal cp (cost=0.43..89,641.22 rows=1,377,773 width=26) (actual time=0.035..13,066.545 rows=1,381,815 loops=1)

29. 15.866 100.837 ↓ 1.0 40,856 1

Hash (cost=3,418.06..3,418.06 rows=40,172 width=14) (actual time=100.837..100.837 rows=40,856 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2388kB
30. 84.971 84.971 ↓ 1.0 40,856 1

Index Scan using scoring_telephone_blocking_tel_index on scoring_telephone_blocking stb (cost=0.41..3,418.06 rows=40,172 width=14) (actual time=6.887..84.971 rows=40,856 loops=1)

  • Filter: (is_forbidden IS TRUE)
  • Rows Removed by Filter: 727
31. 73.243 83.946 ↑ 1.0 13,995 1

Sort (cost=10,000,001,227.90..10,000,001,263.44 rows=14,218 width=11) (actual time=79.219..83.946 rows=13,995 loops=1)

  • Sort Key: (lower(spb.passport))
  • Sort Method: quicksort Memory: 1457kB
32. 10.703 10.703 ↑ 1.0 13,947 1

Seq Scan on scoring_passport_blocking spb (cost=10,000,000,000.00..10,000,000,247.18 rows=14,218 width=11) (actual time=0.741..10.703 rows=13,947 loops=1)

Planning time : 4.956 ms
Execution time : 35,188.250 ms