explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mYUz

Settings
# exclusive inclusive rows x rows loops node
1. 34.835 45,360.344 ↑ 5.2 11,894 1

GroupAggregate (cost=257,959.92..272,939.51 rows=62,192 width=52) (actual time=45,319.134..45,360.344 rows=11,894 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.204 26,009.456 ↑ 1.3 70,971 1

Unique (cost=146,139.11..147,121.88 rows=92,709 width=372) (actual time=25,803.158..26,009.456 rows=70,971 loops=1)

4. 493.635 25,940.252 ↓ 1.0 199,133 1

Sort (cost=146,139.11..146,630.50 rows=196,553 width=372) (actual time=25,803.155..25,940.252 rows=199,133 loops=1)

  • Sort Key: application.client_id, application.create_time DESC
  • Sort Method: external merge Disk: 56696kB
5. 25,351.662 25,446.617 ↓ 1.0 199,133 1

Bitmap Heap Scan on application (cost=4,151.71..61,671.63 rows=196,553 width=372) (actual time=104.005..25,446.617 rows=199,133 loops=1)

  • Recheck Cond: (application_state_id = 10)
  • Heap Blocks: exact=50337
6. 94.955 94.955 ↓ 1.0 199,717 1

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

  • Index Cond: (application_state_id = 10)
7. 14.460 45,325.509 ↑ 29.5 13,606 1

Sort (cost=110,838.04..111,840.16 rows=400,849 width=20) (actual time=45,319.093..45,325.509 rows=13,606 loops=1)

  • Sort Key: last_app.client_id, cls.level, last_app.create_time
  • Sort Method: quicksort Memory: 1076kB
8. 19.824 45,311.049 ↑ 29.5 13,606 1

Merge Left Join (cost=56,136.49..65,312.13 rows=400,849 width=20) (actual time=45,283.526..45,311.049 rows=13,606 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. 73.372 45,204.964 ↓ 2.4 13,606 1

Sort (cost=54,908.59..54,922.82 rows=5,692 width=41) (actual time=45,200.533..45,204.964 rows=13,606 loops=1)

  • Sort Key: (lower(COALESCE((cp.passer || cp.passno), cp.passid_number)))
  • Sort Method: quicksort Memory: 2241kB
10. 62.776 45,131.592 ↓ 2.4 13,606 1

Hash Left Join (cost=13,070.55..54,553.56 rows=5,692 width=41) (actual time=26,016.513..45,131.592 rows=13,606 loops=1)

  • Hash Cond: (last_app.m_telephone = stb.tel)
11. 29.594 45,038.598 ↓ 2.6 13,554 1

Nested Loop Left Join (cost=11,377.68..52,758.63 rows=5,159 width=72) (actual time=25,986.159..45,038.598 rows=13,554 loops=1)

12. 29.943 43,086.324 ↓ 2.6 13,540 1

Nested Loop Left Join (cost=11,377.26..35,964.20 rows=5,151 width=103) (actual time=25,986.128..43,086.324 rows=13,540 loops=1)

13. 30.490 35,067.781 ↓ 2.6 13,540 1

Nested Loop Left Join (cost=11,376.83..32,224.45 rows=5,151 width=85) (actual time=25,986.105..35,067.781 rows=13,540 loops=1)

14. 15.764 30,853.431 ↓ 2.6 13,540 1

Hash Left Join (cost=11,376.41..14,761.03 rows=5,151 width=81) (actual time=25,985.160..30,853.431 rows=13,540 loops=1)

  • Hash Cond: (last_app.client_id = scb.client_id)
15. 29.710 30,836.950 ↓ 2.6 13,540 1

Hash Left Join (cost=11,335.35..14,706.38 rows=5,151 width=80) (actual time=25,984.426..30,836.950 rows=13,540 loops=1)

  • Hash Cond: (last_app.client_id = cls.client_id)
16. 4,510.851 30,768.217 ↓ 2.6 13,540 1

Hash Left Join (cost=9,507.79..12,865.31 rows=5,151 width=76) (actual time=25,945.300..30,768.217 rows=13,540 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
17. 26,116.328 26,116.328 ↓ 1.6 24,310 1

CTE Scan on last_app (cost=0.00..2,085.95 rows=15,452 width=80) (actual time=25,803.166..26,116.328 rows=24,310 loops=1)

  • Filter: ((closed_loan IS TRUE) AND (closed_loan_count > 0))
  • Rows Removed by Filter: 46661
18. 71.041 141.038 ↓ 1.0 214,354 1

Hash (cost=5,993.13..5,993.13 rows=214,213 width=8) (actual time=141.037..141.038 rows=214,354 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 3130kB
19. 69.997 69.997 ↓ 1.0 214,354 1

Seq Scan on billing_loan bl (cost=0.00..5,993.13 rows=214,213 width=8) (actual time=0.016..69.997 rows=214,354 loops=1)

20. 18.727 39.023 ↑ 1.0 56,288 1

Hash (cost=1,115.58..1,115.58 rows=56,958 width=8) (actual time=39.023..39.023 rows=56,288 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2711kB
21. 20.296 20.296 ↑ 1.0 56,288 1

Seq Scan on client_loyalty_status cls (cost=0.00..1,115.58 rows=56,958 width=8) (actual time=0.072..20.296 rows=56,288 loops=1)

22. 0.270 0.717 ↓ 1.0 901 1

Hash (cost=29.96..29.96 rows=888 width=5) (actual time=0.717..0.717 rows=901 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
23. 0.447 0.447 ↓ 1.0 901 1

Seq Scan on scoring_client_blocking scb (cost=0.00..29.96 rows=888 width=5) (actual time=0.012..0.447 rows=901 loops=1)

  • Filter: (is_forbidden IS TRUE)
  • Rows Removed by Filter: 9
24. 4,183.860 4,183.860 ↑ 1.0 1 13,540

Index Scan using client_pkey on client c (cost=0.42..3.39 rows=1 width=8) (actual time=0.309..0.309 rows=1 loops=13,540)

  • Index Cond: (last_app.client_id = id)
25. 7,988.600 7,988.600 ↑ 1.0 1 13,540

Index Scan using client_personal_pkey on client_personal cp (cost=0.43..0.73 rows=1 width=26) (actual time=0.590..0.590 rows=1 loops=13,540)

  • Index Cond: (c.personal_id = id)
26. 1,922.680 1,922.680 ↓ 0.0 0 13,540

Index Scan using scoring_inn_blocking_inn_scoring_inn_blocking_type_id_key on scoring_inn_blocking sib (cost=0.42..3.25 rows=1 width=11) (actual time=0.140..0.142 rows=0 loops=13,540)

  • Index Cond: (last_app.m_cl_inn = inn)
  • Filter: (is_forbidden IS TRUE)
  • Rows Removed by Filter: 0
27. 14.094 30.218 ↓ 1.0 40,856 1

Hash (cost=1,190.72..1,190.72 rows=40,172 width=14) (actual time=30.218..30.218 rows=40,856 loops=1)

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

Seq Scan on scoring_telephone_blocking stb (cost=0.00..1,190.72 rows=40,172 width=14) (actual time=0.014..16.124 rows=40,856 loops=1)

  • Filter: (is_forbidden IS TRUE)
  • Rows Removed by Filter: 727
29. 71.264 86.261 ↑ 1.0 13,995 1

Sort (cost=1,227.90..1,263.44 rows=14,218 width=11) (actual time=82.979..86.261 rows=13,995 loops=1)

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

Seq Scan on scoring_passport_blocking spb (cost=0.00..247.18 rows=14,218 width=11) (actual time=2.066..14.997 rows=13,947 loops=1)

Planning time : 2.332 ms
Execution time : 45,374.412 ms