explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O6Kf

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 5,478.762 ↑ 1.0 20 1

Limit (cost=0.15..2,255.07 rows=20 width=213) (actual time=3.437..5,478.762 rows=20 loops=1)

2. 2,585.802 5,478.730 ↑ 205,660.0 20 1

Nested Loop (cost=0.15..463,746,170.42 rows=4,113,200 width=213) (actual time=3.436..5,478.730 rows=20 loops=1)

  • Join Filter: ("*SELECT* 1".division_code = "*SELECT* 1_1".code)
  • Rows Removed by Join Filter: 13551711
3. 2.107 5.288 ↑ 56.8 20 1

Nested Loop (cost=0.15..5,664.45 rows=1,136 width=195) (actual time=1.357..5.288 rows=20 loops=1)

  • Join Filter: (operation_registration.site_address_id = "*SELECT* 1".id)
  • Rows Removed by Join Filter: 21284
4. 0.025 0.741 ↑ 10.1 20 1

Nested Loop (cost=0.14..2,151.45 rows=202 width=136) (actual time=0.082..0.741 rows=20 loops=1)

  • Join Filter: (legal_entity.category = legal_entity_category.path)
  • Rows Removed by Join Filter: 20
5. 0.083 0.696 ↑ 10.1 20 1

Nested Loop (cost=0.14..477.07 rows=202 width=173) (actual time=0.073..0.696 rows=20 loops=1)

  • Join Filter: (operator.legal_entity_id = legal_entity.id)
  • Rows Removed by Join Filter: 394
6. 0.101 0.533 ↑ 10.1 20 1

Nested Loop (cost=0.14..321.67 rows=202 width=121) (actual time=0.067..0.533 rows=20 loops=1)

  • Join Filter: (operator.id = operator_of_natural_person.id)
  • Rows Removed by Join Filter: 866
7. 0.122 0.352 ↑ 10.1 20 1

Nested Loop (cost=0.14..163.38 rows=202 width=141) (actual time=0.045..0.352 rows=20 loops=1)

  • Join Filter: (operation_registration.operator_id = operator.id)
  • Rows Removed by Join Filter: 866
8. 0.150 0.150 ↑ 10.1 20 1

Index Scan Backward using operation_registration_lower_idx on operation_registration (cost=0.14..5.08 rows=202 width=109) (actual time=0.020..0.150 rows=20 loops=1)

  • Filter: (now() <@ validity)
9. 0.071 0.080 ↑ 1.2 44 20

Materialize (cost=0.00..0.90 rows=53 width=32) (actual time=0.001..0.004 rows=44 loops=20)

10. 0.009 0.009 ↑ 1.1 47 1

Seq Scan on operator (cost=0.00..0.63 rows=53 width=32) (actual time=0.004..0.009 rows=47 loops=1)

11. 0.072 0.080 ↑ 1.2 44 20

Materialize (cost=0.00..0.90 rows=53 width=28) (actual time=0.000..0.004 rows=44 loops=20)

12. 0.008 0.008 ↑ 1.1 47 1

Seq Scan on operator_of_natural_person (cost=0.00..0.63 rows=53 width=28) (actual time=0.003..0.008 rows=47 loops=1)

13. 0.035 0.080 ↑ 2.5 21 20

Materialize (cost=0.00..0.98 rows=52 width=84) (actual time=0.000..0.004 rows=21 loops=20)

14. 0.045 0.045 ↑ 1.0 52 1

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

15. 0.013 0.020 ↑ 275.0 2 20

Materialize (cost=0.00..9.25 rows=550 width=32) (actual time=0.001..0.001 rows=2 loops=20)

16. 0.007 0.007 ↑ 275.0 2 1

Seq Scan on legal_entity_category (cost=0.00..6.50 rows=550 width=32) (actual time=0.006..0.007 rows=2 loops=1)

17. 1.539 2.440 ↑ 1.1 1,065 20

Materialize (cost=0.01..107.06 rows=1,125 width=91) (actual time=0.001..0.122 rows=1,065 loops=20)

18. 0.129 0.901 ↑ 1.1 1,071 1

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

19. 0.177 0.762 ↑ 1.0 1,071 1

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

20. 0.236 0.585 ↑ 1.0 1,071 1

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

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

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

22. 0.001 0.006 ↓ 0.0 0 1

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

23. 0.000 0.005 ↓ 0.0 0 1

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

  • Group Key: address_element_1.id
24. 0.004 0.005 ↓ 0.0 0 1

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

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

Result (cost=0.01..40.11 rows=11 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)
26. 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))
27. 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))
28. 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)

29. 0.000 0.004 ↓ 0.0 0 1

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

30. 0.000 0.004 ↓ 0.0 0 1

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

  • Group Key: address_element_2.id
31. 0.003 0.004 ↓ 0.0 0 1

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

  • Sort Key: address_element_2.id
  • Sort Method: quicksort Memory: 25kB
32. 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)
33. 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)
34. 0.000 0.000 ↓ 0.0 0

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

35. 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))
36. 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)
37. 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)

38. 2,364.482 2,871.860 ↑ 1.1 677,587 20

Materialize (cost=0.01..31,480.89 rows=724,155 width=97) (actual time=0.004..143.593 rows=677,587 loops=20)

39. 73.719 507.378 ↑ 1.0 713,204 1

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

40. 92.006 433.636 ↓ 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.014..433.636 rows=713,204 loops=1)

41. 89.785 341.630 ↓ 1.0 713,204 1

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

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

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

43. 0.000 0.019 ↓ 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.019..0.019 rows=0 loops=1)

44. 0.001 0.019 ↓ 0.0 0 1

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

  • Group Key: division_1.code
45. 0.012 0.018 ↓ 0.0 0 1

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

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

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

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

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

48. 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))
49. 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)))
50. 0.000 0.000 ↓ 0.0 0

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

51. 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))
52. 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))
53. 0.000 0.004 ↓ 0.0 0 1

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

54. 0.001 0.004 ↓ 0.0 0 1

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

  • Group Key: division_2.code
55. 0.002 0.003 ↓ 0.0 0 1

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

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

Result (cost=15.10..60.58 rows=1,417 width=97) (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)
57. 0.000 0.000 ↓ 0.0 0

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

58. 0.000 0.000 ↓ 0.0 0

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

59. 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))
60. 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)
61. 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))
62. 0.000 0.000 ↓ 0.0 0

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

63. 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)
64. 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)
65.          

SubPlan (forNested Loop)

66. 0.280 15.780 ↑ 1.0 1 20

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

67. 2.560 15.500 ↑ 3.0 2 20

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

  • Hash Cond: ("*SELECT* 1_2".id = house_registration.house_address_id)
68. 2.300 11.960 ↑ 1.1 1,071 20

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

69. 2.680 9.480 ↑ 1.0 1,071 20

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

70. 3.940 6.800 ↑ 1.0 1,071 20

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

  • One-Time Filter: (current_setting('request.jwt.claim.role'::text, true) = 'anonymous'::text)
71. 2.860 2.860 ↑ 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.003..0.143 rows=1,071 loops=20)

72. 0.000 0.120 ↓ 0.0 0 20

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

73. 0.020 0.120 ↓ 0.0 0 20

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

  • Group Key: address_element_4.id
74. 0.080 0.100 ↓ 0.0 0 20

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

  • Sort Key: address_element_4.id
  • Sort Method: quicksort Memory: 25kB
75. 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)
76. 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))
77. 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))
78. 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)

79. 0.020 0.060 ↓ 0.0 0 20

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

80. 0.000 0.040 ↓ 0.0 0 20

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

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

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

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

Result (cost=0.15..29.35 rows=1 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)
83. 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)
84. 0.000 0.000 ↓ 0.0 0

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

85. 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))
86. 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)
87. 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)

88. 0.080 0.980 ↑ 1.0 2 20

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
89. 0.900 0.900 ↑ 1.0 2 20

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

  • Filter: (operation_registration_id = operation_registration.id)
  • Rows Removed by Filter: 354
Planning time : 3.668 ms
Execution time : 5,491.442 ms