explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fu7y

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 2,340.994 ↑ 1.0 20 1

Limit (cost=282,920.52..285,109.27 rows=20 width=213) (actual time=2,328.593..2,340.994 rows=20 loops=1)

2. 0.018 2,340.968 ↑ 205,660.0 20 1

Result (cost=282,920.52..450,421,317.29 rows=4,113,200 width=213) (actual time=2,328.592..2,340.968 rows=20 loops=1)

3. 0.089 2,327.790 ↑ 205,660.0 20 1

Sort (cost=282,920.52..293,203.52 rows=4,113,200 width=205) (actual time=2,327.788..2,327.790 rows=20 loops=1)

  • Sort Key: (lower(operation_registration.validity)) DESC
  • Sort Method: top-N heapsort Memory: 33kB
4. 51.632 2,327.701 ↑ 20,362.4 202 1

Merge Join (cost=99,672.68..173,469.75 rows=4,113,200 width=205) (actual time=2,326.969..2,327.701 rows=202 loops=1)

  • Merge Cond: ("*SELECT* 1".division_code = "*SELECT* 1_1".code)
5. 0.283 2.116 ↑ 5.6 202 1

Sort (cost=199.28..202.12 rows=1,136 width=195) (actual time=2.093..2.116 rows=202 loops=1)

  • Sort Key: "*SELECT* 1".division_code
  • Sort Method: quicksort Memory: 83kB
6. 0.269 1.833 ↑ 5.6 202 1

Hash Join (cost=23.21..141.63 rows=1,136 width=195) (actual time=0.725..1.833 rows=202 loops=1)

  • Hash Cond: ("*SELECT* 1".id = operation_registration.site_address_id)
7. 0.133 0.920 ↑ 1.1 1,071 1

Append (cost=0.01..101.44 rows=1,125 width=91) (actual time=0.021..0.920 rows=1,071 loops=1)

8. 0.163 0.761 ↑ 1.0 1,071 1

Subquery Scan on *SELECT* 1 (cost=0.01..25.97 rows=1,113 width=91) (actual time=0.020..0.761 rows=1,071 loops=1)

9. 0.224 0.598 ↑ 1.0 1,071 1

Result (cost=0.01..14.84 rows=1,113 width=155) (actual time=0.019..0.598 rows=1,071 loops=1)

  • One-Time Filter: (current_setting('request.jwt.claim.role'::text, true) = 'anonymous'::text)
10. 0.374 0.374 ↑ 1.0 1,071 1

Seq Scan on address_element (cost=0.01..14.84 rows=1,113 width=91) (actual time=0.015..0.374 rows=1,071 loops=1)

11. 0.000 0.011 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=40.30..40.47 rows=11 width=91) (actual time=0.011..0.011 rows=0 loops=1)

12. 0.000 0.011 ↓ 0.0 0 1

Group (cost=40.30..40.36 rows=11 width=155) (actual time=0.011..0.011 rows=0 loops=1)

  • Group Key: address_element_1.id
13. 0.009 0.011 ↓ 0.0 0 1

Sort (cost=40.30..40.33 rows=11 width=91) (actual time=0.010..0.011 rows=0 loops=1)

  • Sort Key: address_element_1.id
  • Sort Method: quicksort Memory: 25kB
14. 0.002 0.002 ↓ 0.0 0 1

Result (cost=0.01..40.11 rows=11 width=91) (actual time=0.002..0.002 rows=0 loops=1)

  • One-Time Filter: (current_setting('request.jwt.claim.role'::text, true) = 'house_ops_user'::text)
15. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.01..40.11 rows=11 width=91) (never executed)

  • Join Filter: (address_element_1.division_code <@ ANY (response_area_user.response_areas))
16. 0.000 0.000 ↓ 0.0 0

Seq Scan on response_area_user (cost=0.00..0.24 rows=1 width=93) (never executed)

  • Filter: ((role <> 'admin'::text) AND (id = (current_setting('request.jwt.claim.user_id'::text, true))::uuid))
17. 0.000 0.000 ↓ 0.0 0

Seq Scan on address_element address_element_1 (cost=0.00..14.83 rows=1,113 width=91) (never executed)

18. 0.001 0.015 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=29.36..29.38 rows=1 width=91) (actual time=0.014..0.015 rows=0 loops=1)

19. 0.000 0.014 ↓ 0.0 0 1

Group (cost=29.36..29.37 rows=1 width=155) (actual time=0.014..0.014 rows=0 loops=1)

  • Group Key: address_element_2.id
20. 0.013 0.014 ↓ 0.0 0 1

Sort (cost=29.36..29.37 rows=1 width=91) (actual time=0.014..0.014 rows=0 loops=1)

  • Sort Key: address_element_2.id
  • Sort Method: quicksort Memory: 25kB
21. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.15..29.35 rows=1 width=91) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: (current_setting('request.jwt.claim.role'::text, true) = 'house_ops_user'::text)
22. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.15..29.35 rows=1 width=91) (never executed)

  • Join Filter: (address_element_2.division_code <@ response_area.division)
23. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.15..0.60 rows=1 width=32) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Seq Scan on response_area_user response_area_user_1 (cost=0.00..0.24 rows=1 width=16) (never executed)

  • Filter: ((role = 'admin'::text) AND (id = (current_setting('request.jwt.claim.user_id'::text, true))::uuid))
25. 0.000 0.000 ↓ 0.0 0

Index Scan using response_area_pkey on response_area (cost=0.15..0.37 rows=1 width=48) (never executed)

  • Index Cond: (id = response_area_user_1.response_area_id)
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on address_element address_element_2 (cost=0.00..14.83 rows=1,113 width=91) (never executed)

27. 0.105 0.644 ↑ 1.0 202 1

Hash (cost=20.68..20.68 rows=202 width=136) (actual time=0.644..0.644 rows=202 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
28. 0.094 0.539 ↑ 1.0 202 1

Hash Join (cost=14.18..20.68 rows=202 width=136) (actual time=0.353..0.539 rows=202 loops=1)

  • Hash Cond: (operation_registration.operator_id = operator.id)
29. 0.116 0.116 ↑ 1.0 202 1

Seq Scan on operation_registration (cost=0.00..3.73 rows=202 width=109) (actual time=0.011..0.116 rows=202 loops=1)

  • Filter: (now() <@ validity)
30. 0.024 0.329 ↑ 1.0 52 1

Hash (cost=13.51..13.51 rows=53 width=75) (actual time=0.329..0.329 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
31. 0.066 0.305 ↑ 1.0 52 1

Hash Join (cost=2.74..13.51 rows=53 width=75) (actual time=0.114..0.305 rows=52 loops=1)

  • Hash Cond: (operator.id = operator_of_natural_person.id)
32. 0.033 0.213 ↑ 1.0 52 1

Hash Join (cost=1.44..12.07 rows=53 width=47) (actual time=0.077..0.213 rows=52 loops=1)

  • Hash Cond: (legal_entity.id = operator.legal_entity_id)
33. 0.041 0.156 ↑ 1.0 52 1

Nested Loop (cost=0.15..10.63 rows=52 width=47) (actual time=0.041..0.156 rows=52 loops=1)

34. 0.011 0.011 ↑ 1.0 52 1

Seq Scan on legal_entity (cost=0.00..0.72 rows=52 width=84) (actual time=0.003..0.011 rows=52 loops=1)

35. 0.104 0.104 ↑ 1.0 1 52

Index Only Scan using legal_entity_category_path_key on legal_entity_category (cost=0.15..0.19 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=52)

  • Index Cond: (path = legal_entity.category)
  • Heap Fetches: 52
36. 0.013 0.024 ↑ 1.0 52 1

Hash (cost=0.63..0.63 rows=53 width=32) (actual time=0.024..0.024 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
37. 0.011 0.011 ↑ 1.0 52 1

Seq Scan on operator (cost=0.00..0.63 rows=53 width=32) (actual time=0.003..0.011 rows=52 loops=1)

38. 0.014 0.026 ↑ 1.0 52 1

Hash (cost=0.63..0.63 rows=53 width=28) (actual time=0.026..0.026 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
39. 0.012 0.012 ↑ 1.0 52 1

Seq Scan on operator_of_natural_person (cost=0.00..0.63 rows=53 width=28) (actual time=0.004..0.012 rows=52 loops=1)

40. 30.660 2,273.953 ↑ 3.5 209,638 1

Materialize (cost=99,473.40..103,094.18 rows=724,155 width=97) (actual time=2,142.700..2,273.953 rows=209,638 loops=1)

41. 1,765.641 2,243.293 ↑ 3.5 209,475 1

Sort (cost=99,473.40..101,283.79 rows=724,155 width=97) (actual time=2,142.696..2,243.293 rows=209,475 loops=1)

  • Sort Key: "*SELECT* 1_1".code
  • Sort Method: external merge Disk: 75400kB
42. 74.207 477.652 ↑ 1.0 713,204 1

Append (cost=0.01..26,728.62 rows=724,155 width=97) (actual time=0.018..477.652 rows=713,204 loops=1)

43. 89.236 403.397 ↓ 1.0 713,204 1

Subquery Scan on *SELECT* 1_1 (cost=0.01..19,730.37 rows=708,698 width=97) (actual time=0.018..403.397 rows=713,204 loops=1)

44. 88.116 314.161 ↓ 1.0 713,204 1

Result (cost=0.01..12,643.39 rows=708,698 width=129) (actual time=0.017..314.161 rows=713,204 loops=1)

  • One-Time Filter: (current_setting('request.jwt.claim.role'::text, true) = 'anonymous'::text)
45. 226.045 226.045 ↓ 1.0 713,204 1

Seq Scan on division (cost=0.01..12,643.39 rows=708,698 width=97) (actual time=0.016..226.045 rows=713,204 loops=1)

46. 0.001 0.036 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=3,010.87..3,221.47 rows=14,040 width=97) (actual time=0.035..0.036 rows=0 loops=1)

47. 0.001 0.035 ↓ 0.0 0 1

Group (cost=3,010.87..3,081.07 rows=14,040 width=129) (actual time=0.035..0.035 rows=0 loops=1)

  • Group Key: division_1.code
48. 0.027 0.034 ↓ 0.0 0 1

Sort (cost=3,010.87..3,045.97 rows=14,040 width=97) (actual time=0.034..0.034 rows=0 loops=1)

  • Sort Key: division_1.code
  • Sort Method: quicksort Memory: 25kB
49. 0.007 0.007 ↓ 0.0 0 1

Result (cost=157.12..2,043.71 rows=14,040 width=97) (actual time=0.007..0.007 rows=0 loops=1)

  • One-Time Filter: (current_setting('request.jwt.claim.role'::text, true) = 'house_ops_user'::text)
50. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=157.12..2,043.71 rows=14,040 width=97) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Seq Scan on response_area_user response_area_user_2 (cost=0.00..0.24 rows=1 width=93) (never executed)

  • Filter: ((role <> 'admin'::text) AND (id = (current_setting('request.jwt.claim.user_id'::text, true))::uuid))
52. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on division division_1 (cost=157.12..1,903.07 rows=14,040 width=97) (never executed)

  • Recheck Cond: ((code <@ ANY (response_area_user_2.response_areas)) OR (code @> ANY (response_area_user_2.response_areas)))
53. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=157.12..157.12 rows=14,110 width=0) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on code_gist_idx (cost=0.00..75.05 rows=7,055 width=0) (never executed)

  • Index Cond: (code <@ ANY (response_area_user_2.response_areas))
55. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on code_gist_idx (cost=0.00..75.05 rows=7,055 width=0) (never executed)

  • Index Cond: (code @> ANY (response_area_user_2.response_areas))
56. 0.001 0.012 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3_1 (cost=134.75..156.00 rows=1,417 width=97) (actual time=0.012..0.012 rows=0 loops=1)

57. 0.000 0.011 ↓ 0.0 0 1

Group (cost=134.75..141.83 rows=1,417 width=129) (actual time=0.011..0.011 rows=0 loops=1)

  • Group Key: division_2.code
58. 0.009 0.011 ↓ 0.0 0 1

Sort (cost=134.75..138.29 rows=1,417 width=97) (actual time=0.011..0.011 rows=0 loops=1)

  • Sort Key: division_2.code
  • Sort Method: quicksort Memory: 25kB
59. 0.002 0.002 ↓ 0.0 0 1

Result (cost=15.10..60.58 rows=1,417 width=97) (actual time=0.002..0.002 rows=0 loops=1)

  • One-Time Filter: (current_setting('request.jwt.claim.role'::text, true) = 'house_ops_user'::text)
60. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=15.10..60.58 rows=1,417 width=97) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.15..0.60 rows=1 width=32) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Seq Scan on response_area_user response_area_user_3 (cost=0.00..0.24 rows=1 width=16) (never executed)

  • Filter: ((role = 'admin'::text) AND (id = (current_setting('request.jwt.claim.user_id'::text, true))::uuid))
63. 0.000 0.000 ↓ 0.0 0

Index Scan using response_area_pkey on response_area response_area_1 (cost=0.15..0.37 rows=1 width=48) (never executed)

  • Index Cond: (id = response_area_user_3.response_area_id)
64. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on division division_2 (cost=14.94..45.80 rows=1,417 width=97) (never executed)

  • Recheck Cond: ((code <@ response_area_1.division) OR (code @> response_area_1.division))
65. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=14.94..14.94 rows=1,417 width=0) (never executed)

66. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on code_gist_idx (cost=0.00..7.12 rows=709 width=0) (never executed)

  • Index Cond: (code <@ response_area_1.division)
67. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on code_gist_idx (cost=0.00..7.12 rows=709 width=0) (never executed)

  • Index Cond: (code @> response_area_1.division)
68.          

SubPlan (forResult)

69. 0.080 13.160 ↑ 1.0 1 20

Aggregate (cost=109.41..109.42 rows=1 width=8) (actual time=0.658..0.658 rows=1 loops=20)

70. 2.100 13.080 ↑ 3.0 2 20

Hash Join (cost=4.98..109.40 rows=6 width=0) (actual time=0.610..0.654 rows=2 loops=20)

  • Hash Cond: ("*SELECT* 1_2".id = house_registration.house_address_id)
71. 2.140 10.300 ↑ 1.1 1,071 20

Append (cost=0.01..101.44 rows=1,125 width=16) (actual time=0.003..0.515 rows=1,071 loops=20)

72. 2.540 8.100 ↑ 1.0 1,071 20

Subquery Scan on *SELECT* 1_2 (cost=0.01..25.97 rows=1,113 width=16) (actual time=0.003..0.405 rows=1,071 loops=20)

73. 3.120 5.560 ↑ 1.0 1,071 20

Result (cost=0.01..14.84 rows=1,113 width=144) (actual time=0.003..0.278 rows=1,071 loops=20)

  • One-Time Filter: (current_setting('request.jwt.claim.role'::text, true) = 'anonymous'::text)
74. 2.440 2.440 ↑ 1.0 1,071 20

Seq Scan on address_element address_element_3 (cost=0.01..14.84 rows=1,113 width=16) (actual time=0.002..0.122 rows=1,071 loops=20)

75. 0.000 0.040 ↓ 0.0 0 20

Subquery Scan on *SELECT* 2_2 (cost=40.30..40.47 rows=11 width=16) (actual time=0.002..0.002 rows=0 loops=20)

76. 0.000 0.040 ↓ 0.0 0 20

Group (cost=40.30..40.36 rows=11 width=144) (actual time=0.002..0.002 rows=0 loops=20)

  • Group Key: address_element_4.id
77. 0.020 0.040 ↓ 0.0 0 20

Sort (cost=40.30..40.33 rows=11 width=16) (actual time=0.002..0.002 rows=0 loops=20)

  • Sort Key: address_element_4.id
  • Sort Method: quicksort Memory: 25kB
78. 0.020 0.020 ↓ 0.0 0 20

Result (cost=0.01..40.11 rows=11 width=16) (actual time=0.001..0.001 rows=0 loops=20)

  • One-Time Filter: (current_setting('request.jwt.claim.role'::text, true) = 'house_ops_user'::text)
79. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.01..40.11 rows=11 width=16) (never executed)

  • Join Filter: (address_element_4.division_code <@ ANY (response_area_user_4.response_areas))
80. 0.000 0.000 ↓ 0.0 0

Seq Scan on response_area_user response_area_user_4 (cost=0.00..0.24 rows=1 width=93) (never executed)

  • Filter: ((role <> 'admin'::text) AND (id = (current_setting('request.jwt.claim.user_id'::text, true))::uuid))
81. 0.000 0.000 ↓ 0.0 0

Seq Scan on address_element address_element_4 (cost=0.00..14.83 rows=1,113 width=61) (never executed)

82. 0.000 0.020 ↓ 0.0 0 20

Subquery Scan on *SELECT* 3_2 (cost=29.36..29.38 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=20)

83. 0.000 0.020 ↓ 0.0 0 20

Group (cost=29.36..29.37 rows=1 width=144) (actual time=0.001..0.001 rows=0 loops=20)

  • Group Key: address_element_5.id
84. 0.020 0.020 ↓ 0.0 0 20

Sort (cost=29.36..29.37 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=20)

  • Sort Key: address_element_5.id
  • Sort Method: quicksort Memory: 25kB
85. 0.000 0.000 ↓ 0.0 0 20

Result (cost=0.15..29.35 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=20)

  • One-Time Filter: (current_setting('request.jwt.claim.role'::text, true) = 'house_ops_user'::text)
86. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.15..29.35 rows=1 width=16) (never executed)

  • Join Filter: (address_element_5.division_code <@ response_area_2.division)
87. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.15..0.60 rows=1 width=32) (never executed)

88. 0.000 0.000 ↓ 0.0 0

Seq Scan on response_area_user response_area_user_5 (cost=0.00..0.24 rows=1 width=16) (never executed)

  • Filter: ((role = 'admin'::text) AND (id = (current_setting('request.jwt.claim.user_id'::text, true))::uuid))
89. 0.000 0.000 ↓ 0.0 0

Index Scan using response_area_pkey on response_area response_area_2 (cost=0.15..0.37 rows=1 width=48) (never executed)

  • Index Cond: (id = response_area_user_5.response_area_id)
90. 0.000 0.000 ↓ 0.0 0

Seq Scan on address_element address_element_5 (cost=0.00..14.83 rows=1,113 width=61) (never executed)

91. 0.020 0.680 ↑ 1.0 2 20

Hash (cost=4.95..4.95 rows=2 width=16) (actual time=0.034..0.034 rows=2 loops=20)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
92. 0.660 0.660 ↑ 1.0 2 20

Seq Scan on house_registration (cost=0.00..4.95 rows=2 width=16) (actual time=0.030..0.033 rows=2 loops=20)

  • Filter: (operation_registration_id = operation_registration.id)
  • Rows Removed by Filter: 354
Planning time : 3.717 ms
Execution time : 2,353.462 ms