explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cP9f : Optimization for: Optimization for: plan #H9Wc; plan #pBY0

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 6.889 21,215.374 ↑ 1.0 1 1

Aggregate (cost=409,458.28..409,458.29 rows=1 width=8) (actual time=21,215.373..21,215.374 rows=1 loops=1)

2. 11.128 21,208.485 ↑ 1.0 10,000 1

Limit (cost=236,473.19..409,333.28 rows=10,000 width=8) (actual time=8,244.024..21,208.485 rows=10,000 loops=1)

3.          

Initplan (for Limit)

4. 0.058 0.058 ↑ 1.0 1 1

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

5. 18.186 21,197.299 ↑ 1.5 10,000 1

Nested Loop (cost=236,473.18..490,318.22 rows=14,685 width=8) (actual time=8,244.022..21,197.299 rows=10,000 loops=1)

6. 13.489 21,149.113 ↑ 1.5 10,000 1

Hash Left Join (cost=236,472.90..485,983.49 rows=14,685 width=16) (actual time=8,243.982..21,149.113 rows=10,000 loops=1)

  • Hash Cond: (request.user_add = u.id)
7. 13.140 21,109.741 ↑ 1.5 10,000 1

Hash Semi Join (cost=235,102.40..484,392.13 rows=14,685 width=24) (actual time=8,218.035..21,109.741 rows=10,000 loops=1)

  • Hash Cond: (c.id = c_4.id)
8. 940.038 21,081.451 ↑ 1.5 10,000 1

Hash Join (cost=234,855.51..483,943.33 rows=14,685 width=40) (actual time=8,202.873..21,081.451 rows=10,000 loops=1)

  • Hash Cond: (request.org_add = lp_p.id)
9. 3,753.164 20,140.841 ↑ 1.4 1,464,796 1

Hash Right Join (cost=234,798.38..478,652.99 rows=1,990,490 width=32) (actual time=7,865.073..20,140.841 rows=1,464,796 loops=1)

  • Hash Cond: (rsh.request_id = request.id)
10. 3,073.770 8,525.849 ↑ 1.0 2,349,226 1

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

  • Hash Cond: (rsh.status_id = c_1.id)
11. 2,879.416 5,452.046 ↑ 1.0 2,349,226 1

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.052 0.090 ↓ 1.1 25 1

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

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

16. 0.007 0.017 ↑ 1.0 3 1

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

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

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

18. 0.013 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
19. 0.020 0.020 ↑ 1.0 11 1

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

20. 2,108.615 7,861.828 ↓ 1.2 2,349,055 1

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

  • Buckets: 524,288 Batches: 8 Memory Usage: 22,425kB
21. 3,629.955 5,753.213 ↓ 1.2 2,349,055 1

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

  • Hash Cond: (request.request_type_id = c.id)
22. 2,123.036 2,123.036 ↓ 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=16.731..2,123.036 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()]))
23. 0.107 0.222 ↓ 1.0 95 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
24. 0.115 0.115 ↓ 1.0 95 1

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

25. 0.054 0.572 ↑ 1.0 20 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.518 0.518 ↑ 1.0 20 1

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

  • Filter: (person_root_id = 15,126)
  • Rows Removed by Filter: 2,705
27. 0.191 15.150 ↑ 1.3 160 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
28. 0.601 14.959 ↑ 1.3 160 1

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

  • Hash Cond: (gra.authority_id = c_5.id)
29. 5.403 11.621 ↓ 1.1 462 1

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

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

31. 0.427 0.523 ↓ 54.2 434 1

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

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

  • Index Cond: (user_id = $0)
33. 0.227 2.737 ↑ 1.8 186 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
34. 0.411 2.510 ↑ 1.8 186 1

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

  • Hash Cond: (rta.request_type_id = c_4.id)
35. 0.368 1.886 ↑ 1.8 186 1

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

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

37. 0.619 1.339 ↑ 1.2 609 1

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

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

  • Filter: (NOT is_archived)
  • Rows Removed by Filter: 8
39. 0.103 0.213 ↓ 1.0 95 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
40. 0.110 0.110 ↓ 1.0 95 1

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

41. 0.297 25.883 ↑ 9.9 377 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 47kB
42. 0.541 25.586 ↑ 9.9 377 1

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

  • Hash Cond: (ofp.id = per.id)
43. 1.279 20.402 ↑ 65.8 407 1

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

  • Hash Cond: (leg_per.person_root_id = leg_ver.person_root_id)
44. 1.281 1.281 ↑ 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.024..1.281 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
45. 0.357 17.842 ↑ 8.1 407 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 52kB
46. 0.857 17.485 ↑ 8.1 407 1

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

47. 0.644 12.151 ↓ 1.0 407 1

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

  • Hash Cond: (ofp.legal_person_id = leg_ver.id)
48. 1.952 7.863 ↓ 1.0 407 1

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

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

50. 0.315 4.375 ↓ 1.0 407 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
51. 1.866 4.060 ↓ 1.0 407 1

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

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

53. 0.293 0.592 ↓ 1.0 407 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
54. 0.299 0.299 ↓ 1.0 407 1

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

55. 1.935 3.644 ↓ 1.0 2,725 1

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

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

57. 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
58. 0.277 4.643 ↑ 1.0 377 1

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

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

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

  • Hash Cond: (per.person_root_id = u.person_root_id)
60. 1.287 1.287 ↑ 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.287 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
61. 0.264 1.893 ↑ 1.0 377 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
62. 0.496 1.629 ↑ 1.0 377 1

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

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

  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 116
64. 0.285 0.767 ↑ 1.0 377 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
65. 0.482 0.482 ↑ 1.0 377 1

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

66. 30.000 30.000 ↑ 1.0 1 10,000

Index Only Scan using fdc_legal_person_pk on fdc_legal_person org_list (cost=0.28..0.30 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=10,000)

  • Index Cond: (id = request.org_add)
  • Heap Fetches: 10,000
Planning time : 33.628 ms
Execution time : 21,235.837 ms