explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LYdd

Settings
# exclusive inclusive rows x rows loops node
1. 15.611 70,350.217 ↑ 1.0 1 1

Aggregate (cost=302,719.85..302,719.86 rows=1 width=8) (actual time=70,350.216..70,350.217 rows=1 loops=1)

2.          

CTE closed_good_client

3. 85.401 67,811.278 ↓ 4.6 27,750 1

GroupAggregate (cost=236,814.15..236,949.89 rows=6,033 width=12) (actual time=67,684.069..67,811.278 rows=27,750 loops=1)

  • Group Key: app.client_id, clls.level
4. 213.047 67,725.877 ↓ 13.8 83,102 1

Sort (cost=236,814.15..236,829.23 rows=6,033 width=16) (actual time=67,684.053..67,725.877 rows=83,102 loops=1)

  • Sort Key: app.client_id, clls.level
  • Sort Method: external merge Disk: 2120kB
5. 118.940 67,512.830 ↓ 13.8 83,102 1

Hash Left Join (cost=177,916.80..236,435.31 rows=6,033 width=16) (actual time=17,761.827..67,512.830 rows=83,102 loops=1)

  • Hash Cond: (app.client_id = clls.client_id)
6. 49,716.659 67,290.244 ↓ 13.8 83,102 1

Bitmap Heap Scan on application app (cost=176,089.24..234,591.92 rows=6,033 width=12) (actual time=17,657.945..67,290.244 rows=83,102 loops=1)

  • Recheck Cond: (application_state_id = 10)
  • Filter: ((closed_loan IS TRUE) AND (NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)))
  • Rows Removed by Filter: 116022
  • Heap Blocks: exact=50334
7. 326.327 326.327 ↓ 1.0 199,703 1

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

  • Index Cond: (application_state_id = 10)
8.          

SubPlan (forBitmap Heap Scan)

9. 14.512 14,321.100 ↑ 4.5 19,504 1

Group (cost=67,027.48..84,330.84 rows=88,255 width=4) (actual time=14,287.165..14,321.100 rows=19,504 loops=1)

  • Group Key: a.client_id
10. 0.000 14,306.588 ↑ 7.4 19,504 1

Gather Merge (cost=67,027.48..83,971.62 rows=143,686 width=4) (actual time=14,286.767..14,306.588 rows=19,504 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 11.142 42,544.227 ↑ 11.1 6,501 3

Group (cost=66,027.45..66,386.67 rows=71,843 width=4) (actual time=14,175.781..14,181.409 rows=6,501 loops=3)

  • Group Key: a.client_id
12. 30.093 42,533.085 ↑ 11.1 6,501 3

Sort (cost=66,027.45..66,207.06 rows=71,843 width=4) (actual time=14,175.778..14,177.695 rows=6,501 loops=3)

  • Sort Key: a.client_id
  • Sort Method: quicksort Memory: 495kB
13. 42,464.845 42,502.992 ↑ 11.1 6,501 3

Parallel Bitmap Heap Scan on application a (cost=4,145.68..60,232.39 rows=71,843 width=4) (actual time=55.101..14,167.664 rows=6,501 loops=3)

  • Recheck Cond: (application_state_id = 10)
  • Filter: (closed_loan IS FALSE)
  • Rows Removed by Filter: 59873
  • Heap Blocks: exact=17390
14. 38.147 38.147 ↓ 1.0 199,703 1

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

  • Index Cond: (application_state_id = 10)
15. 49.560 2,926.158 ↑ 2.5 23,728 1

Gather (cost=1,000.00..87,283.92 rows=59,906 width=4) (actual time=7.900..2,926.158 rows=23,728 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 2,740.602 2,876.598 ↑ 3.2 7,909 3

Nested Loop Left Join (cost=0.00..80,293.32 rows=24,961 width=4) (actual time=2.559..2,876.598 rows=7,909 loops=3)

17. 135.651 135.651 ↑ 3.2 7,909 3

Parallel Seq Scan on billing_loan bl (cost=0.00..5,747.68 rows=24,961 width=4) (actual time=0.679..135.651 rows=7,909 loops=3)

  • Filter: ((billing_loan_state_id = ANY ('{20,21,24}'::integer[])) AND (((fact_term_end_date)::date - term_end_date) > 30))
  • Rows Removed by Filter: 63536
18. 0.345 0.345 ↑ 1.0 1 23,728

Index Scan using application_pkey_hash on application apl (cost=0.00..2.99 rows=1 width=8) (actual time=0.345..0.345 rows=1 loops=23,728)

  • Index Cond: (bl.application_id = obj_id)
  • Rows Removed by Index Recheck: 0
19. 23.397 103.646 ↑ 1.0 56,284 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2711kB
20. 80.249 80.249 ↑ 1.0 56,284 1

Seq Scan on client_loyalty_status clls (cost=0.00..1,115.58 rows=56,958 width=8) (actual time=1.297..80.249 rows=56,284 loops=1)

21.          

CTE info_good_loan

22. 113.939 70,086.579 ↓ 3,997.7 27,984 1

Merge Join (cost=63,884.50..64,186.20 rows=7 width=42) (actual time=69,885.818..70,086.579 rows=27,984 loops=1)

  • Merge Cond: ((cgcl.client_id = a_1.client_id) AND (cgcl.max_create_time = ((a_1.create_time)::date)))
23. 16.430 67,858.258 ↓ 4.6 27,750 1

Sort (cost=499.49..514.57 rows=6,033 width=12) (actual time=67,851.353..67,858.258 rows=27,750 loops=1)

  • Sort Key: cgcl.client_id, cgcl.max_create_time
  • Sort Method: quicksort Memory: 2069kB
24. 67,841.828 67,841.828 ↓ 4.6 27,750 1

CTE Scan on closed_good_client cgcl (cost=0.00..120.66 rows=6,033 width=12) (actual time=67,684.074..67,841.828 rows=27,750 loops=1)

25. 438.735 2,114.382 ↓ 7.4 179,620 1

Sort (cost=63,385.01..63,445.33 rows=24,130 width=42) (actual time=2,034.451..2,114.382 rows=179,620 loops=1)

  • Sort Key: a_1.client_id, ((a_1.create_time)::date)
  • Sort Method: external sort Disk: 11640kB
26. 1,461.282 1,675.647 ↓ 7.4 179,620 1

Bitmap Heap Scan on application a_1 (cost=4,108.61..61,628.52 rows=24,130 width=42) (actual time=228.326..1,675.647 rows=179,620 loops=1)

  • Recheck Cond: (application_state_id = 10)
  • Filter: (closed_loan IS TRUE)
  • Rows Removed by Filter: 19504
  • Heap Blocks: exact=50334
27. 214.365 214.365 ↓ 1.0 199,703 1

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

  • Index Cond: (application_state_id = 10)
28.          

CTE info_good_cl_no_bl_list

29. 70,188.302 70,313.268 ↓ 26,143.0 26,143 1

CTE Scan on info_good_loan gl_1 (cost=1,583.54..1,583.74 rows=1 width=108) (actual time=70,033.200..70,313.268 rows=26,143 loops=1)

  • Filter: ((NOT (hashed SubPlan 5)) AND (NOT (hashed SubPlan 6)) AND (NOT (hashed SubPlan 7)))
  • Rows Removed by Filter: 1841
30.          

SubPlan (forCTE Scan)

31. 16.449 16.449 ↓ 1.0 901 1

Seq Scan on scoring_client_blocking clb (cost=0.00..29.96 rows=888 width=4) (actual time=7.164..16.449 rows=901 loops=1)

  • Filter: (is_forbidden IS TRUE)
  • Rows Removed by Filter: 9
32. 16.314 16.314 ↑ 1.1 12,721 1

Seq Scan on scoring_ip_blocking ib (cost=0.00..225.97 rows=13,697 width=7) (actual time=7.763..16.314 rows=12,721 loops=1)

  • Filter: (is_forbidden IS TRUE)
  • Rows Removed by Filter: 514
33. 92.203 92.203 ↓ 1.0 40,856 1

Seq Scan on scoring_telephone_blocking tb (cost=0.00..1,190.72 rows=40,172 width=13) (actual time=2.738..92.203 rows=40,856 loops=1)

  • Filter: (is_forbidden IS TRUE)
  • Rows Removed by Filter: 727
34. 70,334.606 70,334.606 ↓ 26,143.0 26,143 1

CTE Scan on info_good_cl_no_bl_list gl (cost=0.00..0.02 rows=1 width=0) (actual time=70,033.203..70,334.606 rows=26,143 loops=1)

Planning time : 32.950 ms
Execution time : 70,363.666 ms