explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AdQm

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 9.590 110,722.319 ↑ 1.0 1 1

Aggregate (cost=53,629.69..53,629.70 rows=1 width=8) (actual time=110,722.318..110,722.319 rows=1 loops=1)

2. 15.494 110,712.729 ↑ 1.0 10,000 1

Limit (cost=1,648.65..53,504.69 rows=10,000 width=8) (actual time=21,078.457..110,712.729 rows=10,000 loops=1)

3.          

Initplan (forLimit)

4. 0.068 0.068 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.066..0.068 rows=1 loops=1)

5. 18.660 110,697.167 ↑ 199.0 10,000 1

Merge Join (cost=1,648.64..10,323,539.99 rows=1,990,490 width=8) (actual time=21,078.455..110,697.167 rows=10,000 loops=1)

  • Merge Cond: (c_3.id = c_4.id)
6. 41.182 110,668.655 ↑ 199.0 10,000 1

Nested Loop Left Join (cost=1,394.78..10,278,071.11 rows=1,990,490 width=16) (actual time=21,068.623..110,668.655 rows=10,000 loops=1)

7. 50,764.761 110,397.473 ↑ 199.0 10,000 1

Nested Loop (cost=1,393.95..3,905,892.60 rows=1,990,490 width=24) (actual time=21,068.540..110,397.473 rows=10,000 loops=1)

  • Join Filter: (request.request_type_id = c_3.id)
  • Rows Removed by Join Filter: 86915146
8. 0.414 0.414 ↑ 2.4 38 1

Index Scan using fdc_request_type_pk on fdc_request_type c_3 (cost=0.14..13.54 rows=93 width=12) (actual time=0.023..0.414 rows=38 loops=1)

9. 54,602.608 59,632.298 ↓ 1.1 2,287,504 38

Materialize (cost=1,393.80..239,881.73 rows=1,990,490 width=16) (actual time=1.569..1,569.271 rows=2,287,504 loops=38)

10. 2,948.402 5,029.690 ↓ 1.2 2,349,058 1

Hash Left Join (cost=1,393.80..220,209.28 rows=1,990,490 width=16) (actual time=59.236..5,029.690 rows=2,349,058 loops=1)

  • Hash Cond: (request.user_add = u.id)
11. 2,040.262 2,040.262 ↓ 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=18.142..2,040.262 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()]))
12. 0.426 41.026 ↑ 9.9 377 1

Hash (cost=1,330.15..1,330.15 rows=3,738 width=8) (actual time=41.024..41.026 rows=377 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 47kB
13. 0.815 40.600 ↑ 9.9 377 1

Hash Right Join (cost=619.79..1,330.15 rows=3,738 width=8) (actual time=35.985..40.600 rows=377 loops=1)

  • Hash Cond: (ofp.id = per.id)
14. 1.967 32.180 ↑ 66.4 407 1

Hash Right Join (cost=384.78..956.44 rows=27,017 width=8) (actual time=28.329..32.180 rows=407 loops=1)

  • Hash Cond: (leg_per.person_root_id = leg_ver.person_root_id)
15. 1.998 1.998 ↑ 1.0 1,579 1

Seq Scan on fdc_person leg_per (cost=0.00..77.50 rows=1,644 width=16) (actual time=0.014..1.998 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.574 28.215 ↑ 8.1 407 1

Hash (cost=343.48..343.48 rows=3,304 width=16) (actual time=28.213..28.215 rows=407 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 52kB
17. 1.586 27.641 ↑ 8.1 407 1

Nested Loop Left Join (cost=160.01..343.48 rows=3,304 width=16) (actual time=12.780..27.641 rows=407 loops=1)

18. 0.914 19.136 ↓ 1.0 407 1

Hash Left Join (cost=159.87..242.47 rows=404 width=24) (actual time=12.688..19.136 rows=407 loops=1)

  • Hash Cond: (ofp.legal_person_id = leg_ver.id)
19. 3.159 12.456 ↓ 1.0 407 1

Hash Right Join (cost=75.55..157.09 rows=404 width=24) (actual time=6.866..12.456 rows=407 loops=1)

  • Hash Cond: (ind_ver.id = ofp.individual_id)
20. 2.470 2.470 ↑ 1.0 2,725 1

Seq Scan on fdc_person ind_ver (cost=0.00..50.25 rows=2,725 width=16) (actual time=0.012..2.470 rows=2,725 loops=1)

21. 0.481 6.827 ↓ 1.0 407 1

Hash (cost=70.50..70.50 rows=404 width=24) (actual time=6.825..6.827 rows=407 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
22. 2.972 6.346 ↓ 1.0 407 1

Hash Join (cost=13.09..70.50 rows=404 width=24) (actual time=0.981..6.346 rows=407 loops=1)

  • Hash Cond: (per_1.id = ofp.id)
23. 2.440 2.440 ↑ 1.0 2,725 1

Seq Scan on fdc_person per_1 (cost=0.00..50.25 rows=2,725 width=8) (actual time=0.013..2.440 rows=2,725 loops=1)

24. 0.505 0.934 ↓ 1.0 407 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
25. 0.429 0.429 ↓ 1.0 407 1

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

26. 3.063 5.766 ↑ 1.0 2,725 1

Hash (cost=50.25..50.25 rows=2,725 width=16) (actual time=5.765..5.766 rows=2,725 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 160kB
27. 2.703 2.703 ↑ 1.0 2,725 1

Seq Scan on fdc_person leg_ver (cost=0.00..50.25 rows=2,725 width=16) (actual time=0.010..2.703 rows=2,725 loops=1)

28. 6.919 6.919 ↑ 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.013..0.017 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
29. 0.428 7.605 ↑ 1.0 377 1

Hash (cost=230.30..230.30 rows=377 width=16) (actual time=7.604..7.605 rows=377 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
30. 1.890 7.177 ↑ 1.0 377 1

Hash Right Join (cost=142.93..230.30 rows=377 width=16) (actual time=3.125..7.177 rows=377 loops=1)

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

Seq Scan on fdc_person per (cost=0.00..77.50 rows=1,644 width=16) (actual time=0.018..2.217 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
32. 0.474 3.070 ↑ 1.0 377 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
33. 0.862 2.596 ↑ 1.0 377 1

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

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

  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 116
35. 0.438 1.132 ↑ 1.0 377 1

Hash (cost=112.77..112.77 rows=377 width=32) (actual time=1.130..1.132 rows=377 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
36. 0.694 0.694 ↑ 1.0 377 1

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

37. 40.000 230.000 ↑ 2.0 1 10,000

Nested Loop (cost=0.83..3.18 rows=2 width=8) (actual time=0.019..0.023 rows=1 loops=10,000)

38. 50.000 160.000 ↑ 2.0 1 10,000

Nested Loop Left Join (cost=0.70..2.87 rows=2 width=16) (actual time=0.013..0.016 rows=1 loops=10,000)

39. 80.000 80.000 ↑ 2.0 1 10,000

Index Scan using fdc_request_status_history_request_i on fdc_request_status_history rsh (cost=0.43..0.67 rows=2 width=24) (actual time=0.007..0.008 rows=1 loops=10,000)

  • Index Cond: (request_id = request.id)
  • Filter: ((statement_timestamp() >= date_from) AND (statement_timestamp() <= date_to))
  • Rows Removed by Filter: 1
40. 20.000 30.000 ↓ 0.0 0 10,000

Nested Loop (cost=0.27..1.09 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=10,000)

41. 10.000 10.000 ↓ 0.0 0 10,000

Index Scan using fdc_change_status_reason_pk on fdc_change_status_reason c_1 (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=10,000)

  • Index Cond: (id = rsh.reason_id)
42. 0.000 0.000 ↓ 0.0 0

Index Only Scan using fdc_object_type_pk on fdc_object_type c_2 (cost=0.13..0.67 rows=1 width=8) (never executed)

  • Index Cond: (id = c_1.object_type_id)
  • Heap Fetches: 0
43. 30.000 30.000 ↑ 1.0 1 10,000

Index Only Scan using fdc_request_status_pk on fdc_request_status c (cost=0.14..0.15 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=10,000)

  • Index Cond: (id = rsh.status_id)
  • Heap Fetches: 10000
44. 0.123 9.852 ↑ 4.9 38 1

Sort (cost=253.86..254.33 rows=188 width=16) (actual time=9.820..9.852 rows=38 loops=1)

  • Sort Key: rta.request_type_id
  • Sort Method: quicksort Memory: 29kB
45. 0.203 9.729 ↑ 2.0 93 1

HashAggregate (cost=244.88..246.76 rows=188 width=16) (actual time=9.671..9.729 rows=93 loops=1)

  • Group Key: rta.request_type_id
46. 0.406 9.526 ↑ 1.3 160 1

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

  • Hash Cond: (gra.authority_id = c_5.id)
47. 3.429 7.355 ↓ 1.1 462 1

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

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

49. 0.250 0.348 ↓ 54.2 434 1

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

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

  • Index Cond: (user_id = $0)
51. 0.130 1.765 ↑ 1.8 186 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
52. 0.238 1.635 ↑ 1.8 186 1

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

  • Hash Cond: (rta.request_type_id = c_4.id)
53. 0.255 1.254 ↑ 1.8 186 1

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

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

55. 0.412 0.878 ↑ 1.2 609 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
56. 0.466 0.466 ↑ 1.2 609 1

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

  • Filter: (NOT is_archived)
  • Rows Removed by Filter: 8
57. 0.068 0.143 ↓ 1.0 95 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
58. 0.075 0.075 ↓ 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.075 rows=95 loops=1)

Planning time : 37.741 ms
Execution time : 110,739.543 ms