explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zyIa

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,359.651 23,604.225 ↑ 1.0 1 1

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

2. 2,632.917 22,244.574 ↑ 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,801.300..22,244.574 rows=2,349,225 loops=1)

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

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

  • Hash Cond: (request.request_type_id = c_3.id)
4. 4,563.223 16,740.131 ↑ 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,774.227..16,740.131 rows=2,349,225 loops=1)

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

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

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

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

  • Hash Cond: (rsh.reason_id = c_1.id)
7. 2,538.346 2,538.346 ↑ 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.012..2,538.346 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.040 0.176 ↓ 1.1 25 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 0.091 0.136 ↓ 1.1 25 1

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

  • Hash Cond: (c_1.object_type_id = c_2.id)
10. 0.028 0.028 ↓ 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.028 rows=25 loops=1)

11. 0.008 0.017 ↑ 1.0 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.009 0.009 ↑ 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.009 rows=3 loops=1)

13. 0.017 0.037 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=8) (actual time=0.036..0.037 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.020 0.020 ↑ 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.020 rows=11 loops=1)

15. 1,916.185 3,770.747 ↑ 1.0 2,349,225 1

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

  • Buckets: 524,288 Batches: 8 Memory Usage: 20,137kB
16. 1,854.562 1,854.562 ↑ 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,854.562 rows=2,349,225 loops=1)

17. 0.146 0.263 ↓ 1.0 95 1

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

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

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

19. 0.281 26.727 ↑ 9.9 377 1

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

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

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

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

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

  • Hash Cond: (leg_per.person_root_id = leg_ver.person_root_id)
22. 1.282 1.282 ↑ 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.010..1.282 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.348 18.534 ↑ 8.1 407 1

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

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

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

25. 0.604 12.621 ↓ 1.0 407 1

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

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

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

  • Hash Cond: (ind_ver.id = ofp.individual_id)
27. 1.628 1.628 ↓ 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.008..1.628 rows=2,725 loops=1)

28. 0.304 4.473 ↓ 1.0 407 1

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

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

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

  • Hash Cond: (per_1.id = ofp.id)
30. 1.601 1.601 ↓ 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.012..1.601 rows=2,725 loops=1)

31. 0.301 0.578 ↓ 1.0 407 1

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

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

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

33. 2.001 3.847 ↓ 1.0 2,725 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 160kB
34. 1.846 1.846 ↓ 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.007..1.846 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.305 4.800 ↑ 1.0 377 1

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

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

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

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

Seq Scan on fdc_person per (cost=0.00..77.22 rows=1,636 width=16) (actual time=0.011..1.306 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.280 1.928 ↑ 1.0 377 1

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

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

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

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

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

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

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

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

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

Planning time : 9.983 ms
Execution time : 23,604.781 ms