explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W5pA

Settings
# exclusive inclusive rows x rows loops node
1. 6.184 19,855.739 ↑ 1.0 1 1

Aggregate (cost=462,237.34..462,237.35 rows=1 width=8) (actual time=19,855.738..19,855.739 rows=1 loops=1)

2. 10.530 19,849.555 ↑ 1.0 10,000 1

Limit (cost=296,269.03..462,112.34 rows=10,000 width=8) (actual time=11,243.896..19,849.555 rows=10,000 loops=1)

3.          

Initplan (forLimit)

4. 0.084 0.084 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.083..0.084 rows=1 loops=1)

5. 23.901 19,838.941 ↑ 1.5 10,000 1

Nested Loop (cost=296,269.01..538,549.51 rows=14,609 width=8) (actual time=11,243.893..19,838.941 rows=10,000 loops=1)

6. 12.258 19,795.040 ↑ 1.5 10,000 1

Hash Semi Join (cost=296,268.74..534,237.21 rows=14,609 width=16) (actual time=11,243.848..19,795.040 rows=10,000 loops=1)

  • Hash Cond: (c_1.id = c_2.id)
7. 832.279 19,767.246 ↑ 1.5 10,000 1

Hash Join (cost=296,021.85..533,789.44 rows=14,609 width=32) (actual time=11,228.298..19,767.246 rows=10,000 loops=1)

  • Hash Cond: (request.org_add = lp_p.id)
8. 1,761.780 18,934.882 ↑ 1.4 1,464,796 1

Hash Join (cost=295,993.68..528,528.12 rows=1,990,490 width=24) (actual time=11,050.786..18,934.882 rows=1,464,796 loops=1)

  • Hash Cond: (request.request_type_id = c_1.id)
9. 3,620.636 17,172.871 ↑ 1.4 1,464,796 1

Hash Right Join (cost=295,990.59..523,061.88 rows=1,990,490 width=16) (actual time=11,050.546..17,172.871 rows=1,464,796 loops=1)

  • Hash Cond: (status.request_id = request.id)
10. 2,502.794 2,502.794 ↑ 1.0 2,349,229 1

Seq Scan on fdc_request_status_history status (cost=0.00..160,856.02 rows=2,373,875 width=16) (actual time=0.023..2,502.794 rows=2,349,229 loops=1)

  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 2535824
11. 2,013.691 11,049.441 ↓ 1.2 2,349,058 1

Hash (cost=259,445.46..259,445.46 rows=1,990,490 width=24) (actual time=11,049.440..11,049.441 rows=2,349,058 loops=1)

  • Buckets: 524288 (originally 524288) Batches: 8 (originally 4) Memory Usage: 28673kB
12. 1,701.007 9,035.750 ↓ 1.2 2,349,058 1

Hash Right Join (cost=232,858.69..259,445.46 rows=1,990,490 width=24) (actual time=7,337.694..9,035.750 rows=2,349,058 loops=1)

  • Hash Cond: (p.person_root_id = u.person_root_id)
13. 0.553 4.715 ↑ 1.7 392 1

Hash Left Join (cost=131.92..260.55 rows=679 width=8) (actual time=1.482..4.715 rows=392 loops=1)

  • Hash Cond: (r.person_type_id = c.id)
14. 1.321 3.912 ↑ 1.0 392 1

Hash Join (cost=115.07..196.90 rows=407 width=16) (actual time=1.210..3.912 rows=392 loops=1)

  • Hash Cond: (p.person_root_id = r.id)
15. 1.433 1.433 ↑ 1.0 1,579 1

Seq Scan on fdc_person p (cost=0.00..77.50 rows=1,644 width=16) (actual time=0.021..1.433 rows=1,579 loops=1)

  • Filter: ((NOT is_local_copy) AND (statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 1146
16. 0.285 1.158 ↓ 1.0 392 1

Hash (cost=110.18..110.18 rows=391 width=16) (actual time=1.157..1.158 rows=392 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
17. 0.873 0.873 ↓ 1.0 392 1

Index Scan using fdc_person_root_person_type_i on fdc_person_root r (cost=0.53..110.18 rows=391 width=16) (actual time=0.325..0.873 rows=392 loops=1)

  • Index Cond: (person_type_id = nsi.c_person_type_individual_id())
18. 0.014 0.250 ↑ 10.0 1 1

Hash (cost=16.72..16.72 rows=10 width=8) (actual time=0.249..0.250 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.004 0.236 ↑ 10.0 1 1

Nested Loop Left Join (cost=1.82..16.72 rows=10 width=8) (actual time=0.174..0.236 rows=1 loops=1)

20. 0.223 0.223 ↑ 1.0 1 1

Seq Scan on fdc_person_type c (cost=0.00..2.58 rows=1 width=16) (actual time=0.162..0.223 rows=1 loops=1)

  • Filter: (id = nsi.c_person_type_individual_id())
  • Rows Removed by Filter: 5
21. 0.002 0.009 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.82..14.04 rows=10 width=8) (actual time=0.008..0.009 rows=0 loops=1)

22. 0.003 0.007 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.42..8.96 rows=1 width=16) (actual time=0.006..0.007 rows=0 loops=1)

23. 0.004 0.004 ↓ 0.0 0 1

Index Scan using fdc_user_pk on fdc_user usr (cost=0.27..8.29 rows=1 width=16) (actual time=0.003..0.004 rows=0 loops=1)

  • Index Cond: (c.user_add = id)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using fdc_person_root_date_uk on fdc_person per (cost=0.14..0.66 rows=1 width=16) (never executed)

  • Index Cond: (person_root_id = usr.person_root_id)
  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
25. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.40..4.43 rows=65 width=8) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.26..2.43 rows=8 width=16) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.11..2.18 rows=1 width=24) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.83..1.56 rows=1 width=24) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.55..0.93 rows=1 width=24) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Index Scan using fdc_official_person_pk on fdc_official_person ofp (cost=0.27..0.31 rows=1 width=24) (never executed)

  • Index Cond: (id = per.id)
31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fdc_person_pk on fdc_person per_1 (cost=0.28..0.62 rows=1 width=8) (never executed)

  • Index Cond: (id = ofp.id)
  • Heap Fetches: 0
32. 0.000 0.000 ↓ 0.0 0

Index Scan using fdc_person_pk on fdc_person ind_ver (cost=0.28..0.62 rows=1 width=16) (never executed)

  • Index Cond: (id = ofp.individual_id)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using fdc_person_pk on fdc_person leg_ver (cost=0.28..0.62 rows=1 width=16) (never executed)

  • Index Cond: (id = ofp.legal_person_id)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using fdc_person_root_date_uk on fdc_person ind_per (cost=0.14..0.24 rows=1 width=16) (never executed)

  • Index Cond: (person_root_id = ind_ver.person_root_id)
  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
35. 0.000 0.000 ↓ 0.0 0

Index Scan using fdc_person_root_date_uk on fdc_person leg_per (cost=0.14..0.24 rows=1 width=8) (never executed)

  • Index Cond: (person_root_id = leg_ver.person_root_id)
  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
36. 1,864.449 7,330.028 ↓ 1.2 2,349,058 1

Hash (cost=194,238.65..194,238.65 rows=1,990,490 width=32) (actual time=7,330.027..7,330.028 rows=2,349,058 loops=1)

  • Buckets: 524288 (originally 524288) Batches: 64 (originally 8) Memory Usage: 132665kB
37. 3,319.991 5,465.579 ↓ 1.2 2,349,058 1

Hash Left Join (cost=134.41..194,238.65 rows=1,990,490 width=32) (actual time=18.200..5,465.579 rows=2,349,058 loops=1)

  • Hash Cond: (request.user_add = u.id)
38. 2,144.476 2,144.476 ↓ 1.2 2,349,058 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=17.058..2,144.476 rows=2,349,058 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()]))
39. 0.429 1.112 ↑ 1.0 377 1

Hash (cost=112.77..112.77 rows=377 width=16) (actual time=1.111..1.112 rows=377 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
40. 0.683 0.683 ↑ 1.0 377 1

Seq Scan on fdc_user u (cost=0.00..112.77 rows=377 width=16) (actual time=0.019..0.683 rows=377 loops=1)

41. 0.101 0.231 ↓ 1.0 95 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
42. 0.130 0.130 ↓ 1.0 95 1

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

43. 0.023 0.085 ↑ 1.0 20 1

Hash (cost=27.92..27.92 rows=20 width=8) (actual time=0.084..0.085 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.045 0.062 ↑ 1.0 20 1

Bitmap Heap Scan on fdc_person lp_p (cost=4.44..27.92 rows=20 width=8) (actual time=0.026..0.062 rows=20 loops=1)

  • Recheck Cond: (person_root_id = 15126)
  • Heap Blocks: exact=7
45. 0.017 0.017 ↑ 1.0 20 1

Bitmap Index Scan on fdc_person_root_i (cost=0.00..4.43 rows=20 width=0) (actual time=0.016..0.017 rows=20 loops=1)

  • Index Cond: (person_root_id = 15126)
46. 0.215 15.536 ↑ 1.3 160 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
47. 0.700 15.321 ↑ 1.3 160 1

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

  • Hash Cond: (gra.authority_id = c_3.id)
48. 5.516 11.844 ↓ 1.1 462 1

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

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

50. 0.495 0.635 ↓ 54.2 434 1

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

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

  • Index Cond: (user_id = $0)
52. 0.212 2.777 ↑ 1.8 186 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
53. 0.392 2.565 ↑ 1.8 186 1

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

  • Hash Cond: (rta.request_type_id = c_2.id)
54. 0.364 1.972 ↑ 1.8 186 1

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

  • Hash Cond: (rta.authority_id = c_3.id)
55. 0.181 0.181 ↑ 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.181 rows=186 loops=1)

56. 0.675 1.427 ↑ 1.2 609 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
57. 0.752 0.752 ↑ 1.2 609 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
59. 0.102 0.102 ↓ 1.0 95 1

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

60. 20.000 20.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.002..0.002 rows=1 loops=10,000)

  • Index Cond: (id = request.org_add)
  • Heap Fetches: 10000
Planning time : 34.154 ms
Execution time : 19,875.122 ms