explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H9Wc

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 11.394 21,091.149 ↑ 1.0 1 1

Aggregate (cost=486,189.23..486,189.24 rows=1 width=8) (actual time=21,091.147..21,091.149 rows=1 loops=1)

2.          

Initplan (for Aggregate)

3. 0.055 0.055 ↑ 1.0 1 1

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

4. 23.124 21,079.700 ↓ 1.4 19,944 1

Hash Join (cost=236,603.28..486,152.50 rows=14,685 width=8) (actual time=7,423.562..21,079.700 rows=19,944 loops=1)

  • Hash Cond: (request.org_add = org_list.id)
5. 23.944 21,052.388 ↓ 1.4 19,944 1

Hash Left Join (cost=236,472.90..485,983.49 rows=14,685 width=24) (actual time=7,419.360..21,052.388 rows=19,944 loops=1)

  • Hash Cond: (request.user_add = u.id)
6. 23.870 21,002.997 ↓ 1.4 19,944 1

Hash Semi Join (cost=235,102.40..484,392.13 rows=14,685 width=32) (actual time=7,393.857..21,002.997 rows=19,944 loops=1)

  • Hash Cond: (c.id = c_4.id)
7. 1,319.293 20,964.688 ↓ 1.4 19,944 1

Hash Join (cost=234,855.51..483,943.33 rows=14,685 width=48) (actual time=7,379.404..20,964.688 rows=19,944 loops=1)

  • Hash Cond: (request.org_add = lp_p.id)
8. 4,603.086 19,644.927 ↓ 1.2 2,349,055 1

Hash Right Join (cost=234,798.38..478,652.99 rows=1,990,490 width=40) (actual time=7,047.201..19,644.927 rows=2,349,055 loops=1)

  • Hash Cond: (rsh.request_id = request.id)
9. 2,896.548 7,996.330 ↑ 1.0 2,349,226 1

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

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

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

  • Hash Cond: (rsh.reason_id = c_2.id)
11. 2,399.738 2,399.738 ↑ 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,399.738 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.021 0.108 ↓ 1.1 25 1

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

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

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

  • Hash Cond: (c_2.object_type_id = c_3.id)
14. 0.022 0.022 ↓ 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.022 rows=25 loops=1)

15. 0.010 0.018 ↑ 1.0 3 1

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

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

17. 0.013 0.038 ↑ 1.0 11 1

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

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

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

19. 1,893.172 7,045.511 ↓ 1.2 2,349,055 1

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

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

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

  • Hash Cond: (request.request_type_id = c.id)
21. 1,870.787 1,870.787 ↓ 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=40) (actual time=15.520..1,870.787 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.103 0.208 ↓ 1.0 95 1

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

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

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

24. 0.026 0.468 ↑ 1.0 20 1

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

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

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

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

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

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

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

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

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

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

30. 0.395 0.475 ↓ 54.2 434 1

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

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

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

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

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

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

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

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

  • Hash Cond: (rta.authority_id = c_5.id)
35. 0.176 0.176 ↑ 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.176 rows=186 loops=1)

36. 0.622 1.328 ↑ 1.2 609 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 37kB
37. 0.706 0.706 ↑ 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.706 rows=609 loops=1)

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

Hash (cost=1.93..1.93 rows=93 width=8) (actual time=0.197..0.198 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.255 25.447 ↑ 9.9 377 1

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

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

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

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

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

  • Hash Cond: (leg_per.person_root_id = leg_ver.person_root_id)
43. 1.215 1.215 ↑ 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.215 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.334 17.621 ↑ 8.1 407 1

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

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

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

46. 0.579 11.946 ↓ 1.0 407 1

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

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

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

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

49. 0.296 4.265 ↓ 1.0 407 1

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

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

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

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

52. 0.289 0.572 ↓ 1.0 407 1

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

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

54. 1.910 3.601 ↓ 1.0 2,725 1

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

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

56. 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.008..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
57. 0.268 4.634 ↑ 1.0 377 1

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

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

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

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

Seq Scan on fdc_person per (cost=0.00..77.22 rows=1,636 width=16) (actual time=0.014..1.329 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.264 1.868 ↑ 1.0 377 1

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

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

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

  • Hash Cond: (up.user_id = u.id)
62. 0.332 0.332 ↓ 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.332 rows=377 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
64. 0.449 0.449 ↑ 1.0 377 1

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

65. 2.051 4.188 ↑ 1.0 1,913 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 91kB
66. 2.137 2.137 ↑ 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.029..2.137 rows=1,913 loops=1)

Planning time : 31.583 ms
Execution time : 21,091.919 ms