explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pBY0 : Optimization for: plan #H9Wc

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 11.336 22,169.260 ↑ 1.0 1 1

Aggregate (cost=486,189.23..486,189.24 rows=1 width=8) (actual time=22,169.259..22,169.260 rows=1 loops=1)

2.          

Initplan (for Aggregate)

3. 0.054 0.054 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.052..0.054 rows=1 loops=1)

4. 23.452 22,157.870 ↓ 1.4 19,944 1

Hash Join (cost=236,603.28..486,152.50 rows=14,685 width=8) (actual time=8,467.597..22,157.870 rows=19,944 loops=1)

  • Hash Cond: (request.org_add = org_list.id)
5. 24.089 22,130.125 ↓ 1.4 19,944 1

Hash Left Join (cost=236,472.90..485,983.49 rows=14,685 width=24) (actual time=8,463.289..22,130.125 rows=19,944 loops=1)

  • Hash Cond: (request.user_add = u.id)
6. 24.373 22,077.255 ↓ 1.4 19,944 1

Hash Semi Join (cost=235,102.40..484,392.13 rows=14,685 width=32) (actual time=8,434.449..22,077.255 rows=19,944 loops=1)

  • Hash Cond: (c.id = c_4.id)
7. 1,347.953 22,037.899 ↓ 1.4 19,944 1

Hash Join (cost=234,855.51..483,943.33 rows=14,685 width=48) (actual time=8,419.454..22,037.899 rows=19,944 loops=1)

  • Hash Cond: (request.org_add = lp_p.id)
8. 4,630.131 20,689.464 ↓ 1.2 2,349,055 1

Hash Right Join (cost=234,798.38..478,652.99 rows=1,990,490 width=40) (actual time=8,042.122..20,689.464 rows=2,349,055 loops=1)

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

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

  • Hash Cond: (rsh.status_id = c_1.id)
10. 2,727.656 5,128.312 ↑ 1.0 2,349,226 1

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

  • Hash Cond: (rsh.reason_id = c_2.id)
11. 2,400.527 2,400.527 ↑ 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.011..2,400.527 rows=2,349,226 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.056 0.098 ↓ 1.1 25 1

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

  • Hash Cond: (c_2.object_type_id = c_3.id)
14. 0.023 0.023 ↓ 1.1 25 1

Seq Scan on fdc_change_status_reason c_2 (cost=0.00..1.23 rows=23 width=16) (actual time=0.008..0.023 rows=25 loops=1)

15. 0.010 0.019 ↑ 1.0 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.009 0.009 ↑ 1.0 3 1

Seq Scan on fdc_object_type c_3 (cost=0.00..1.03 rows=3 width=8) (actual time=0.006..0.009 rows=3 loops=1)

17. 0.023 0.045 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=8) (actual time=0.044..0.045 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.022 0.022 ↑ 1.0 11 1

Seq Scan on fdc_request_status c_1 (cost=0.00..1.11 rows=11 width=8) (actual time=0.014..0.022 rows=11 loops=1)

19. 2,122.798 8,040.002 ↓ 1.2 2,349,055 1

Hash (cost=194,362.35..194,362.35 rows=1,990,490 width=40) (actual time=8,040.001..8,040.002 rows=2,349,055 loops=1)

  • Buckets: 524,288 Batches: 8 Memory Usage: 22,425kB
20. 3,745.921 5,917.204 ↓ 1.2 2,349,055 1

Hash Join (cost=20.02..194,362.35 rows=1,990,490 width=40) (actual time=15.521..5,917.204 rows=2,349,055 loops=1)

  • Hash Cond: (request.request_type_id = c.id)
21. 2,171.075 2,171.075 ↓ 1.2 2,349,055 1

Index Scan using fdc_request_rtype_i on fdc_request request (cost=16.93..188,896.11 rows=1,990,490 width=32) (actual time=15.294..2,171.075 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_012_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_153_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()]))
22. 0.099 0.208 ↓ 1.0 95 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
23. 0.109 0.109 ↓ 1.0 95 1

Seq Scan on fdc_request_type c (cost=0.00..1.93 rows=93 width=8) (actual time=0.021..0.109 rows=95 loops=1)

24. 0.025 0.482 ↑ 1.0 20 1

Hash (cost=56.89..56.89 rows=20 width=8) (actual time=0.482..0.482 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
25. 0.457 0.457 ↑ 1.0 20 1

Seq Scan on fdc_person lp_p (cost=0.00..56.89 rows=20 width=8) (actual time=0.027..0.457 rows=20 loops=1)

  • Filter: (person_root_id = 15,126)
  • Rows Removed by Filter: 2,705
26. 0.170 14.983 ↑ 1.3 160 1

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

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

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

  • Hash Cond: (gra.authority_id = c_5.id)
28. 5.463 11.522 ↓ 1.1 462 1

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

  • Merge Cond: (gra.group_id = grm.group_id)
29. 5.527 5.527 ↑ 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.527 rows=4,874 loops=1)

30. 0.444 0.532 ↓ 54.2 434 1

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

  • Sort Key: grm.group_id
  • Sort Method: quicksort Memory: 25kB
31. 0.088 0.088 ↑ 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.072..0.088 rows=2 loops=1)

  • Index Cond: (user_id = $0)
32. 0.206 2.702 ↑ 1.8 186 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
33. 0.373 2.496 ↑ 1.8 186 1

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

  • Hash Cond: (rta.request_type_id = c_4.id)
34. 0.376 1.894 ↑ 1.8 186 1

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

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

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

36. 0.628 1.317 ↑ 1.2 609 1

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

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

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

  • Filter: (NOT is_archived)
  • Rows Removed by Filter: 8
38. 0.129 0.229 ↓ 1.0 95 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
39. 0.100 0.100 ↓ 1.0 95 1

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

40. 0.283 28.781 ↑ 9.9 377 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 47kB
41. 0.542 28.498 ↑ 9.9 377 1

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

  • Hash Cond: (ofp.id = per.id)
42. 1.328 23.011 ↑ 65.8 407 1

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

  • Hash Cond: (leg_per.person_root_id = leg_ver.person_root_id)
43. 1.385 1.385 ↑ 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.385 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
44. 0.429 20.298 ↑ 8.1 407 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 52kB
45. 1.290 19.869 ↑ 8.1 407 1

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

46. 0.717 13.288 ↓ 1.0 407 1

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

  • Hash Cond: (ofp.legal_person_id = leg_ver.id)
47. 2.270 8.634 ↓ 1.0 407 1

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

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

49. 0.306 4.557 ↓ 1.0 407 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
50. 2.003 4.251 ↓ 1.0 407 1

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

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

52. 0.309 0.616 ↓ 1.0 407 1

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

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

54. 2.073 3.937 ↓ 1.0 2,725 1

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

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

56. 5.291 5.291 ↑ 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.010..0.013 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
57. 0.294 4.945 ↑ 1.0 377 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
58. 1.295 4.651 ↑ 1.0 377 1

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

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

Seq Scan on fdc_person per (cost=0.00..77.22 rows=1,636 width=16) (actual time=0.013..1.396 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
60. 0.278 1.960 ↑ 1.0 377 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
61. 0.547 1.682 ↑ 1.0 377 1

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

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

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

  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 116
63. 0.307 0.765 ↑ 1.0 377 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
64. 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.030..0.458 rows=377 loops=1)

65. 2.072 4.293 ↑ 1.0 1,913 1

Hash (cost=106.28..106.28 rows=1,928 width=8) (actual time=4.293..4.293 rows=1,913 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 91kB
66. 2.221 2.221 ↑ 1.0 1,913 1

Seq Scan on fdc_legal_person org_list (cost=0.00..106.28 rows=1,928 width=8) (actual time=0.028..2.221 rows=1,913 loops=1)

Planning time : 28.982 ms
Execution time : 22,169.980 ms