explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2hm : Optimization for: plan #zyIa

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,379.938 23,399.667 ↑ 1.0 1 1

Aggregate (cost=387,882.43..387,882.44 rows=1 width=8) (actual time=23,399.666..23,399.667 rows=1 loops=1)

2. 2,651.643 22,019.729 ↑ 1.0 2,349,225 1

Hash Left Join (cost=116,623.60..381,823.87 rows=2,423,427 width=8) (actual time=3,823.952..22,019.729 rows=2,349,225 loops=1)

  • Hash Cond: (request.user_add = u.id)
3. 2,890.354 19,341.678 ↑ 1.0 2,349,225 1

Hash Join (cost=115,253.10..344,006.26 rows=2,423,427 width=16) (actual time=3,797.530..19,341.678 rows=2,349,225 loops=1)

  • Hash Cond: (request.request_type_id = c_3.id)
4. 4,521.053 16,451.081 ↑ 1.0 2,349,225 1

Hash Right Join (cost=115,250.01..337,351.78 rows=2,423,427 width=24) (actual time=3,797.275..16,451.081 rows=2,349,225 loops=1)

  • Hash Cond: (rsh.request_id = request.id)
5. 2,927.527 8,133.889 ↑ 1.0 2,349,225 1

Hash Join (cost=3.90..178,394.24 rows=2,373,875 width=8) (actual time=0.188..8,133.889 rows=2,349,225 loops=1)

  • Hash Cond: (rsh.status_id = c.id)
6. 2,766.313 5,206.332 ↑ 1.0 2,349,225 1

Hash Left Join (cost=2.66..169,760.72 rows=2,373,875 width=16) (actual time=0.115..5,206.332 rows=2,349,225 loops=1)

  • Hash Cond: (rsh.reason_id = c_1.id)
7. 2,439.923 2,439.923 ↑ 1.0 2,349,225 1

Seq Scan on fdc_request_status_history rsh (cost=0.00..160,856.02 rows=2,373,875 width=24) (actual time=0.011..2,439.923 rows=2,349,225 loops=1)

  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 2,535,819
8. 0.020 0.096 ↓ 1.1 25 1

Hash (cost=2.37..2.37 rows=23 width=8) (actual time=0.095..0.096 rows=25 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 0.040 0.076 ↓ 1.1 25 1

Hash Join (cost=1.07..2.37 rows=23 width=8) (actual time=0.030..0.076 rows=25 loops=1)

  • Hash Cond: (c_1.object_type_id = c_2.id)
10. 0.022 0.022 ↓ 1.1 25 1

Seq Scan on fdc_change_status_reason c_1 (cost=0.00..1.23 rows=23 width=16) (actual time=0.007..0.022 rows=25 loops=1)

11. 0.004 0.014 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.014..0.014 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.010 0.010 ↑ 1.0 3 1

Seq Scan on fdc_object_type c_2 (cost=0.00..1.03 rows=3 width=8) (actual time=0.007..0.010 rows=3 loops=1)

13. 0.011 0.030 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=8) (actual time=0.029..0.030 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.019 0.019 ↑ 1.0 11 1

Seq Scan on fdc_request_status c (cost=0.00..1.11 rows=11 width=8) (actual time=0.012..0.019 rows=11 loops=1)

15. 1,925.767 3,796.139 ↑ 1.0 2,349,225 1

Hash (cost=66,019.27..66,019.27 rows=2,423,427 width=40) (actual time=3,796.138..3,796.139 rows=2,349,225 loops=1)

  • Buckets: 524,288 Batches: 8 Memory Usage: 20,137kB
16. 1,870.372 1,870.372 ↑ 1.0 2,349,225 1

Seq Scan on fdc_request request (cost=0.00..66,019.27 rows=2,423,427 width=40) (actual time=0.018..1,870.372 rows=2,349,225 loops=1)

17. 0.104 0.243 ↓ 1.0 95 1

Hash (cost=1.93..1.93 rows=93 width=12) (actual time=0.242..0.243 rows=95 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
18. 0.139 0.139 ↓ 1.0 95 1

Seq Scan on fdc_request_type c_3 (cost=0.00..1.93 rows=93 width=12) (actual time=0.022..0.139 rows=95 loops=1)

19. 0.283 26.408 ↑ 9.9 377 1

Hash (cost=1,323.97..1,323.97 rows=3,722 width=8) (actual time=26.408..26.408 rows=377 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 47kB
20. 0.527 26.125 ↑ 9.9 377 1

Hash Right Join (cost=618.65..1,323.97 rows=3,722 width=8) (actual time=23.108..26.125 rows=377 loops=1)

  • Hash Cond: (ofp.id = per.id)
21. 1.241 20.884 ↑ 65.8 407 1

Hash Right Join (cost=383.95..951.69 rows=26,762 width=8) (actual time=18.386..20.884 rows=407 loops=1)

  • Hash Cond: (leg_per.person_root_id = leg_ver.person_root_id)
22. 1.290 1.290 ↑ 1.0 1,579 1

Seq Scan on fdc_person leg_per (cost=0.00..77.22 rows=1,636 width=16) (actual time=0.009..1.290 rows=1,579 loops=1)

  • Filter: ((NOT is_local_copy) AND (statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 1,146
23. 0.340 18.353 ↑ 8.1 407 1

Hash (cost=342.85..342.85 rows=3,288 width=16) (actual time=18.353..18.353 rows=407 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 52kB
24. 1.107 18.013 ↑ 8.1 407 1

Nested Loop Left Join (cost=159.52..342.85 rows=3,288 width=16) (actual time=8.226..18.013 rows=407 loops=1)

25. 0.584 12.429 ↓ 1.0 407 1

Hash Left Join (cost=159.38..241.70 rows=404 width=24) (actual time=8.188..12.429 rows=407 loops=1)

  • Hash Cond: (ofp.legal_person_id = leg_ver.id)
26. 2.062 8.156 ↓ 1.0 407 1

Hash Right Join (cost=75.38..156.64 rows=404 width=24) (actual time=4.479..8.156 rows=407 loops=1)

  • Hash Cond: (ind_ver.id = ofp.individual_id)
27. 1.634 1.634 ↓ 1.0 2,725 1

Seq Scan on fdc_person ind_ver (cost=0.00..50.11 rows=2,711 width=16) (actual time=0.009..1.634 rows=2,725 loops=1)

28. 0.316 4.460 ↓ 1.0 407 1

Hash (cost=70.33..70.33 rows=404 width=24) (actual time=4.459..4.460 rows=407 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
29. 2.001 4.144 ↓ 1.0 407 1

Hash Join (cost=13.09..70.33 rows=404 width=24) (actual time=0.581..4.144 rows=407 loops=1)

  • Hash Cond: (per_1.id = ofp.id)
30. 1.584 1.584 ↓ 1.0 2,725 1

Seq Scan on fdc_person per_1 (cost=0.00..50.11 rows=2,711 width=8) (actual time=0.009..1.584 rows=2,725 loops=1)

31. 0.283 0.559 ↓ 1.0 407 1

Hash (cost=8.04..8.04 rows=404 width=24) (actual time=0.559..0.559 rows=407 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
32. 0.276 0.276 ↓ 1.0 407 1

Seq Scan on fdc_official_person ofp (cost=0.00..8.04 rows=404 width=24) (actual time=0.009..0.276 rows=407 loops=1)

33. 1.929 3.689 ↓ 1.0 2,725 1

Hash (cost=50.11..50.11 rows=2,711 width=16) (actual time=3.688..3.689 rows=2,725 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 160kB
34. 1.760 1.760 ↓ 1.0 2,725 1

Seq Scan on fdc_person leg_ver (cost=0.00..50.11 rows=2,711 width=16) (actual time=0.008..1.760 rows=2,725 loops=1)

35. 4.477 4.477 ↑ 1.0 1 407

Index Scan using fdc_person_root_date_uk on fdc_person ind_per (cost=0.14..0.24 rows=1 width=16) (actual time=0.009..0.011 rows=1 loops=407)

  • Index Cond: (person_root_id = ind_ver.person_root_id)
  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 0
36. 0.285 4.714 ↑ 1.0 377 1

Hash (cost=229.99..229.99 rows=377 width=16) (actual time=4.713..4.714 rows=377 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
37. 1.229 4.429 ↑ 1.0 377 1

Hash Right Join (cost=142.93..229.99 rows=377 width=16) (actual time=1.892..4.429 rows=377 loops=1)

  • Hash Cond: (per.person_root_id = u.person_root_id)
38. 1.333 1.333 ↑ 1.0 1,579 1

Seq Scan on fdc_person per (cost=0.00..77.22 rows=1,636 width=16) (actual time=0.016..1.333 rows=1,579 loops=1)

  • Filter: ((NOT is_local_copy) AND (statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 1,146
39. 0.274 1.867 ↑ 1.0 377 1

Hash (cost=138.21..138.21 rows=377 width=16) (actual time=1.866..1.867 rows=377 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
40. 0.521 1.593 ↑ 1.0 377 1

Hash Right Join (cost=117.48..138.21 rows=377 width=16) (actual time=0.750..1.593 rows=377 loops=1)

  • Hash Cond: (up.user_id = u.id)
41. 0.342 0.342 ↓ 1.0 377 1

Seq Scan on fdc_user_password up (cost=0.00..19.74 rows=373 width=8) (actual time=0.011..0.342 rows=377 loops=1)

  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 116
42. 0.296 0.730 ↑ 1.0 377 1

Hash (cost=112.77..112.77 rows=377 width=32) (actual time=0.730..0.730 rows=377 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
43. 0.434 0.434 ↑ 1.0 377 1

Seq Scan on fdc_user u (cost=0.00..112.77 rows=377 width=32) (actual time=0.015..0.434 rows=377 loops=1)

Planning time : 10.167 ms
Execution time : 23,400.085 ms