explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ffFF : Optimization for: Optimization for: plan #zyIa; plan #2hm

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2,627.987 29,173.633 ↑ 1.0 1 1

Aggregate (cost=517,453.21..517,453.22 rows=1 width=8) (actual time=29,173.631..29,173.633 rows=1 loops=1)

2.          

Initplan (for Aggregate)

3. 0.058 0.058 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.056..0.058 rows=1 loops=1)

4. 2,786.592 26,545.588 ↓ 1.2 2,349,055 1

Hash Left Join (cost=230,992.43..512,477.16 rows=1,990,415 width=8) (actual time=4,646.810..26,545.588 rows=2,349,055 loops=1)

  • Hash Cond: (request.user_add = u.id)
5. 3,005.161 23,732.504 ↓ 1.2 2,349,055 1

Hash Semi Join (cost=229,621.93..481,171.84 rows=1,990,415 width=16) (actual time=4,620.306..23,732.504 rows=2,349,055 loops=1)

  • Hash Cond: (c_3.id = c_4.id)
6. 3,116.922 20,712.363 ↓ 1.2 2,349,055 1

Hash Join (cost=229,375.04..453,556.75 rows=1,990,415 width=32) (actual time=4,605.315..20,712.363 rows=2,349,055 loops=1)

  • Hash Cond: (request.request_type_id = c_3.id)
7. 4,776.635 17,595.212 ↓ 1.2 2,349,055 1

Hash Right Join (cost=229,371.95..448,090.72 rows=1,990,415 width=24) (actual time=4,605.072..17,595.212 rows=2,349,055 loops=1)

  • Hash Cond: (rsh.request_id = request.id)
8. 2,957.549 8,214.682 ↑ 1.0 2,349,226 1

Hash Join (cost=3.90..178,394.24 rows=2,373,875 width=8) (actual time=0.164..8,214.682 rows=2,349,226 loops=1)

  • Hash Cond: (rsh.status_id = c.id)
9. 2,768.905 5,257.100 ↑ 1.0 2,349,226 1

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

  • Hash Cond: (rsh.reason_id = c_1.id)
10. 2,488.104 2,488.104 ↑ 1.0 2,349,226 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,488.104 rows=2,349,226 loops=1)

  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 2,535,821
11. 0.018 0.091 ↓ 1.1 25 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.038 0.073 ↓ 1.1 25 1

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

  • Hash Cond: (c_1.object_type_id = c_2.id)
13. 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)

14. 0.004 0.013 ↑ 1.0 3 1

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

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

16. 0.010 0.033 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=8) (actual time=0.032..0.033 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.023 0.023 ↑ 1.0 11 1

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

18. 2,042.633 4,603.895 ↓ 1.2 2,349,055 1

Hash (cost=188,936.86..188,936.86 rows=1,990,415 width=40) (actual time=4,603.894..4,603.895 rows=2,349,055 loops=1)

  • Buckets: 524,288 Batches: 8 Memory Usage: 20,136kB
19. 2,561.262 2,561.262 ↓ 1.2 2,349,055 1

Index Scan using fdc_request_rtype_i on fdc_request request (cost=16.43..188,936.86 rows=1,990,415 width=40) (actual time=14.116..2,561.262 rows=2,349,055 loops=1)

  • Index Cond: (request_type_id = ANY (ARRAY[nsi.c_request_type_000_id(), nsi.c_request_type_001_id(), nsi.c_request_type_002_id(), nsi.c_request_type_003_id(), nsi.c_request_type_004_id(), nsi.c_request_type_005_id(), nsi.c_request_type_006_id(), nsi.c_request_type_007_id(), nsi.c_request_type_008_id(), nsi.c_request_type_009_id(), nsi.c_request_type_010_id(), nsi.c_request_type_011_id(), nsi.c_request_type_022_id(), nsi.c_request_type_024_id(), nsi.c_request_type_025_id(), nsi.c_request_type_026_id(), nsi.c_request_type_036_id(), nsi.c_request_type_037_id(), nsi.c_request_type_038_id(), nsi.c_request_type_039_id(), nsi.c_request_type_040_id(), nsi.c_request_type_041_id(), nsi.c_request_type_042_id(), nsi.c_request_type_043_id(), nsi.c_request_type_044_id(), nsi.c_request_type_045_id(), nsi.c_request_type_046_id(), nsi.c_request_type_047_id(), nsi.c_request_type_048_id(), nsi.c_request_type_052_id(), nsi.c_request_type_053_id(), nsi.c_request_type_054_id(), nsi.c_request_type_055_id(), nsi.c_request_type_056_id(), nsi.c_request_type_100_id(), nsi.c_request_type_101_id(), nsi.c_request_type_111_id(), nsi.c_request_type_113_id(), nsi.c_request_type_114_id(), nsi.c_request_type_115_id(), nsi.c_request_type_116_id(), nsi.c_request_type_117_id(), nsi.c_request_type_118_id(), nsi.c_request_type_119_id(), nsi.c_request_type_120_id(), nsi.c_request_type_121_id(), nsi.c_request_type_122_id(), nsi.c_request_type_123_id(), nsi.c_request_type_124_id(), nsi.c_request_type_125_id(), nsi.c_request_type_127_id(), nsi.c_request_type_131_id(), nsi.c_request_type_136_id(), nsi.c_request_type_137_id(), nsi.c_request_type_138_id(), nsi.c_request_type_139_id(), nsi.c_request_type_141_id(), nsi.c_request_type_142_id(), nsi.c_request_type_143_id(), nsi.c_request_type_152_id(), nsi.c_request_type_140_id(), nsi.c_request_type_900_id(), nsi.c_request_type_901_id(), nsi.c_request_type_074_id()]))
20. 0.124 0.229 ↓ 1.0 95 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
21. 0.105 0.105 ↓ 1.0 95 1

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

22. 0.167 14.980 ↑ 1.3 160 1

Hash (cost=244.38..244.38 rows=201 width=16) (actual time=14.979..14.980 rows=160 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
23. 0.613 14.813 ↑ 1.3 160 1

Hash Join (cost=54.83..244.38 rows=201 width=16) (actual time=3.477..14.813 rows=160 loops=1)

  • Hash Cond: (gra.authority_id = c_5.id)
24. 5.453 11.533 ↓ 1.1 462 1

Merge Join (cost=17.03..202.99 rows=421 width=8) (actual time=0.762..11.533 rows=462 loops=1)

  • Merge Cond: (gra.group_id = grm.group_id)
25. 5.577 5.577 ↑ 6.4 4,874 1

Index Scan using fdc_group_authority_gr_i on fdc_group_authority gra (cost=0.29..1,264.27 rows=31,394 width=16) (actual time=0.016..5.577 rows=4,874 loops=1)

26. 0.408 0.503 ↓ 54.2 434 1

Sort (cost=16.74..16.76 rows=8 width=8) (actual time=0.109..0.503 rows=434 loops=1)

  • Sort Key: grm.group_id
  • Sort Method: quicksort Memory: 25kB
27. 0.095 0.095 ↑ 4.0 2 1

Index Scan using fdc_group_member_u_i on fdc_group_member grm (cost=0.28..16.62 rows=8 width=8) (actual time=0.089..0.095 rows=2 loops=1)

  • Index Cond: (user_id = $0)
28. 0.203 2.667 ↑ 1.8 186 1

Hash (cost=33.50..33.50 rows=344 width=32) (actual time=2.666..2.667 rows=186 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
29. 0.366 2.464 ↑ 1.8 186 1

Hash Join (cost=26.18..33.50 rows=344 width=32) (actual time=1.550..2.464 rows=186 loops=1)

  • Hash Cond: (rta.request_type_id = c_4.id)
30. 0.397 1.895 ↑ 1.8 186 1

Hash Join (cost=23.09..29.48 rows=344 width=24) (actual time=1.341..1.895 rows=186 loops=1)

  • Hash Cond: (rta.authority_id = c_5.id)
31. 0.180 0.180 ↑ 1.9 186 1

Seq Scan on fdc_request_type_authority rta (cost=0.00..5.48 rows=348 width=16) (actual time=0.014..0.180 rows=186 loops=1)

32. 0.616 1.318 ↑ 1.2 609 1

Hash (cost=14.20..14.20 rows=711 width=16) (actual time=1.317..1.318 rows=609 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 37kB
33. 0.702 0.702 ↑ 1.2 609 1

Seq Scan on fdc_authority c_5 (cost=0.00..14.20 rows=711 width=16) (actual time=0.013..0.702 rows=609 loops=1)

  • Filter: (NOT is_archived)
  • Rows Removed by Filter: 8
34. 0.099 0.203 ↓ 1.0 95 1

Hash (cost=1.93..1.93 rows=93 width=8) (actual time=0.202..0.203 rows=95 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
35. 0.104 0.104 ↓ 1.0 95 1

Seq Scan on fdc_request_type c_4 (cost=0.00..1.93 rows=93 width=8) (actual time=0.016..0.104 rows=95 loops=1)

36. 0.279 26.492 ↑ 9.9 377 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 47kB
37. 0.512 26.213 ↑ 9.9 377 1

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

  • Hash Cond: (ofp.id = per.id)
38. 1.228 21.006 ↑ 65.8 407 1

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

  • Hash Cond: (leg_per.person_root_id = leg_ver.person_root_id)
39. 1.311 1.311 ↑ 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.311 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
40. 0.329 18.467 ↑ 8.1 407 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 52kB
41. 1.108 18.138 ↑ 8.1 407 1

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

42. 0.565 12.553 ↓ 1.0 407 1

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

  • Hash Cond: (ofp.legal_person_id = leg_ver.id)
43. 2.064 8.258 ↓ 1.0 407 1

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

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

45. 0.328 4.584 ↓ 1.0 407 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
46. 2.011 4.256 ↓ 1.0 407 1

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

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

48. 0.298 0.586 ↓ 1.0 407 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
49. 0.288 0.288 ↓ 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.288 rows=407 loops=1)

50. 1.974 3.730 ↓ 1.0 2,725 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 160kB
51. 1.756 1.756 ↓ 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.756 rows=2,725 loops=1)

52. 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
53. 0.264 4.695 ↑ 1.0 377 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
54. 1.215 4.431 ↑ 1.0 377 1

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

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

Seq Scan on fdc_person per (cost=0.00..77.22 rows=1,636 width=16) (actual time=0.029..1.383 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
56. 0.279 1.833 ↑ 1.0 377 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
57. 0.494 1.554 ↑ 1.0 377 1

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

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

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

  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 116
59. 0.261 0.697 ↑ 1.0 377 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
60. 0.436 0.436 ↑ 1.0 377 1

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