explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ocm5

Settings
# exclusive inclusive rows x rows loops node
1. 0.960 1,166.612 ↓ 15.8 63 1

Nested Loop Left Join (cost=165,475.58..166,879.08 rows=4 width=394) (actual time=1,104.286..1,166.612 rows=63 loops=1)

2. 0.255 1,128.489 ↓ 15.8 63 1

Nested Loop Left Join (cost=165,475.15..165,551.99 rows=4 width=368) (actual time=1,103.473..1,128.489 rows=63 loops=1)

  • Filter: (u.parent_unit_id IS NULL)
  • Rows Removed by Filter: 1953
3. 0.067 1,127.163 ↓ 63.0 63 1

Nested Loop Left Join (cost=165,474.88..165,550.58 rows=1 width=360) (actual time=1,103.428..1,127.163 rows=63 loops=1)

4. 0.044 1,126.766 ↓ 33.0 33 1

Nested Loop Left Join (cost=165,474.45..165,550.01 rows=1 width=352) (actual time=1,103.400..1,126.766 rows=33 loops=1)

5. 0.027 1,126.590 ↓ 33.0 33 1

Nested Loop Left Join (cost=165,474.02..165,549.40 rows=1 width=352) (actual time=1,103.391..1,126.590 rows=33 loops=1)

6. 0.062 1,126.200 ↓ 33.0 33 1

Nested Loop Left Join (cost=165,473.60..165,548.79 rows=1 width=344) (actual time=1,103.356..1,126.200 rows=33 loops=1)

7. 0.035 1,125.841 ↓ 33.0 33 1

Nested Loop Left Join (cost=165,473.17..165,548.25 rows=1 width=336) (actual time=1,103.306..1,125.841 rows=33 loops=1)

8. 0.038 1,125.542 ↓ 33.0 33 1

Nested Loop Left Join (cost=165,472.75..165,547.74 rows=1 width=343) (actual time=1,103.290..1,125.542 rows=33 loops=1)

9. 0.023 1,125.471 ↓ 33.0 33 1

Nested Loop Left Join (cost=165,472.46..165,547.43 rows=1 width=338) (actual time=1,103.286..1,125.471 rows=33 loops=1)

10. 0.036 1,125.415 ↓ 33.0 33 1

Nested Loop Left Join (cost=165,472.04..165,546.25 rows=1 width=338) (actual time=1,103.283..1,125.415 rows=33 loops=1)

11. 0.063 1,125.214 ↓ 33.0 33 1

Nested Loop Left Join (cost=165,471.76..165,545.94 rows=1 width=333) (actual time=1,103.270..1,125.214 rows=33 loops=1)

12. 0.051 1,124.920 ↓ 33.0 33 1

Nested Loop (cost=165,471.34..165,537.49 rows=1 width=325) (actual time=1,103.256..1,124.920 rows=33 loops=1)

  • Join Filter: (mt.permit_request_id = caa.case_id)
13. 0.065 1,124.506 ↓ 33.0 33 1

Nested Loop (cost=165,470.91..165,536.90 rows=1 width=333) (actual time=1,103.233..1,124.506 rows=33 loops=1)

  • Join Filter: (mt.permit_request_id = pr.id)
14. 0.073 1,124.144 ↓ 33.0 33 1

Nested Loop (cost=165,470.48..165,536.39 rows=1 width=317) (actual time=1,103.208..1,124.144 rows=33 loops=1)

15. 0.039 1,123.741 ↓ 33.0 33 1

Nested Loop Left Join (cost=165,470.06..165,527.94 rows=1 width=172) (actual time=1,103.169..1,123.741 rows=33 loops=1)

16. 0.044 1,123.669 ↓ 33.0 33 1

Nested Loop Left Join (cost=165,469.77..165,527.62 rows=1 width=172) (actual time=1,103.164..1,123.669 rows=33 loops=1)

17. 11.947 1,123.559 ↓ 33.0 33 1

Subquery Scan on mt (cost=165,469.36..165,519.18 rows=1 width=148) (actual time=1,103.155..1,123.559 rows=33 loops=1)

  • Filter: ((NOT mt.deleted) AND (mt.meeting_id = 18589))
  • Rows Removed by Filter: 113658
18. 88.068 1,111.612 ↓ 74.2 113,691 1

WindowAgg (cost=165,469.36..165,500.02 rows=1,533 width=149) (actual time=999.171..1,111.612 rows=113,691 loops=1)

19. 123.424 1,023.544 ↓ 74.2 113,691 1

Sort (cost=165,469.36..165,473.19 rows=1,533 width=149) (actual time=999.151..1,023.544 rows=113,691 loops=1)

  • Sort Key: top.permit_request_id, top.start_time
  • Sort Method: quicksort Memory: 32191kB
20. 137.556 900.120 ↓ 74.2 113,691 1

Hash Left Join (cost=6,923.55..165,388.24 rows=1,533 width=149) (actual time=144.776..900.120 rows=113,691 loops=1)

  • Hash Cond: (top.meeting_id = meeting.id)
  • Filter: (((inq.* IS NULL) OR ((inq.start_date)::date <= (meeting.planned_date)::date)) AND ((inq.* IS NULL) OR (SubPlan 22)))
  • Rows Removed by Filter: 4931
21. 92.292 330.731 ↓ 13.1 118,622 1

Hash Left Join (cost=6,619.65..14,025.52 rows=9,059 width=283) (actual time=141.669..330.731 rows=118,622 loops=1)

  • Hash Cond: (top.permit_request_id = pinq.request_id)
  • Filter: ((inq.* IS NULL) OR (inq.managing_authority_id = ccmeeting.managing_authority_id) OR (inq.managing_authority_id = 20))
  • Rows Removed by Filter: 82
22. 72.974 100.413 ↑ 1.0 114,287 1

Hash Left Join (cost=337.54..5,321.67 rows=114,700 width=125) (actual time=3.188..100.413 rows=114,287 loops=1)

  • Hash Cond: (top.meeting_id = ccmeeting.id)
23. 24.344 24.344 ↑ 1.0 114,287 1

Seq Scan on meeting_topic_t top (cost=0.00..3,407.00 rows=114,700 width=117) (actual time=0.009..24.344 rows=114,287 loops=1)

24. 1.490 3.095 ↑ 1.0 9,402 1

Hash (cost=220.02..220.02 rows=9,402 width=16) (actual time=3.095..3.095 rows=9,402 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 569kB
25. 1.605 1.605 ↑ 1.0 9,402 1

Seq Scan on cc_meeting_t ccmeeting (cost=0.00..220.02 rows=9,402 width=16) (actual time=0.006..1.605 rows=9,402 loops=1)

26. 32.205 138.026 ↑ 1.0 73,317 1

Hash (cost=5,352.11..5,352.11 rows=74,400 width=174) (actual time=138.026..138.026 rows=73,317 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 16464kB
27. 43.497 105.821 ↑ 1.0 73,317 1

Hash Join (cost=3,021.56..5,352.11 rows=74,400 width=174) (actual time=54.426..105.821 rows=73,317 loops=1)

  • Hash Cond: (pinq.inquiry_id = inq.id)
28. 8.393 8.393 ↑ 1.0 73,317 1

Seq Scan on permit_request_inquiries_t pinq (cost=0.00..1,304.67 rows=75,167 width=16) (actual time=0.007..8.393 rows=73,317 loops=1)

29. 29.552 53.931 ↑ 1.0 73,200 1

Hash (cost=2,092.36..2,092.36 rows=74,336 width=166) (actual time=53.931..53.931 rows=73,200 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 15868kB
30. 24.379 24.379 ↑ 1.0 73,200 1

Seq Scan on inquiry_t inq (cost=0.00..2,092.36 rows=74,336 width=166) (actual time=0.017..24.379 rows=73,200 loops=1)

31. 1.534 3.001 ↑ 1.0 9,535 1

Hash (cost=184.51..184.51 rows=9,551 width=16) (actual time=3.001..3.001 rows=9,535 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 575kB
32. 1.467 1.467 ↑ 1.0 9,535 1

Seq Scan on meeting_t meeting (cost=0.00..184.51 rows=9,551 width=16) (actual time=0.009..1.467 rows=9,535 loops=1)

33.          

SubPlan (forHash Left Join)

34. 0.000 428.832 ↑ 1.0 1 71,472

Aggregate (cost=16.65..16.66 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=71,472)

35. 128.110 428.832 ↑ 1.0 1 71,472

Nested Loop (cost=0.58..16.64 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=71,472)

36. 142.944 142.944 ↑ 1.0 1 71,472

Index Scan using permit_request_inquiries_case_idx on permit_request_inquiries_t pi (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=71,472)

  • Index Cond: (request_id = top.permit_request_id)
37. 157.778 157.778 ↑ 1.0 1 78,889

Index Scan using pk_inquiry_t on inquiry_t it (cost=0.29..8.32 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=78,889)

  • Index Cond: (id = pi.inquiry_id)
  • Filter: (((managing_authority_id = ccmeeting.managing_authority_id) OR (managing_authority_id = 20)) AND ((start_date)::date <= (meeting.planned_date)::date))
  • Rows Removed by Filter: 0
38. 0.066 0.066 ↓ 0.0 0 33

Index Scan using pk_meeting_topic_t on meeting_topic_t prev_top (cost=0.42..8.44 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=33)

  • Index Cond: (mt.prev_top_id = id)
39. 0.033 0.033 ↓ 0.0 0 33

Index Scan using pk_meeting_t on meeting_t prevmeeting (cost=0.29..0.31 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=33)

  • Index Cond: (prev_top.meeting_id = id)
40. 0.330 0.330 ↑ 1.0 1 33

Index Scan using pk_case_t on case_t c (cost=0.42..8.44 rows=1 width=145) (actual time=0.010..0.010 rows=1 loops=33)

  • Index Cond: (id = mt.permit_request_id)
41. 0.297 0.297 ↑ 1.0 1 33

Index Scan using pk_permit_request_t on permit_request_t pr (cost=0.42..0.50 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=33)

  • Index Cond: (id = c.id)
42. 0.363 0.363 ↑ 1.0 1 33

Index Only Scan using pk_case_actor_authority on case_actor_authority_t caa (cost=0.43..0.58 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=33)

  • Index Cond: ((case_id = c.id) AND (authority_abstract_id = 4))
  • Heap Fetches: 33
43. 0.231 0.231 ↑ 1.0 1 33

Index Scan using pk_judgement_t on judgement_t j (cost=0.42..8.44 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=33)

  • Index Cond: (id = mt.given_judgement_id)
44. 0.165 0.165 ↑ 1.0 1 33

Index Scan using pk_judgement_result_t on judgement_result_t jr (cost=0.28..0.30 rows=1 width=21) (actual time=0.004..0.005 rows=1 loops=33)

  • Index Cond: (id = j.judgement_result_id)
45. 0.033 0.033 ↓ 0.0 0 33

Index Scan using pk_judgement_t on judgement_t jp (cost=0.42..1.16 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=33)

  • Index Cond: (id = prev_top.given_judgement_id)
46. 0.033 0.033 ↓ 0.0 0 33

Index Scan using pk_judgement_result_t on judgement_result_t jrp (cost=0.28..0.30 rows=1 width=21) (actual time=0.001..0.001 rows=0 loops=33)

  • Index Cond: (id = jp.judgement_result_id)
47. 0.264 0.264 ↑ 1.0 1 33

Index Scan using pk_analysis_t on analysis_t ant (cost=0.42..0.50 rows=1 width=9) (actual time=0.007..0.008 rows=1 loops=33)

  • Index Cond: (pr.analysis_id = id)
48. 0.297 0.297 ↑ 1.0 1 33

Index Scan using geolocation__case_idx on geolocation_t g (cost=0.42..0.53 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=33)

  • Index Cond: (c.id = case_id)
49. 0.363 0.363 ↑ 1.0 1 33

Index Scan using geolocation_address_pk on geolocation_address_context_t gac_norm (cost=0.43..0.60 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=33)

  • Index Cond: (g.id = geolocation_id)
  • Filter: ((context_type)::text = 'TARGET'::text)
  • Rows Removed by Filter: 2
50. 0.132 0.132 ↑ 1.0 1 33

Index Scan using geolocation_address_pk on geolocation_address_context_t gac_desc (cost=0.43..0.60 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=33)

  • Index Cond: (g.id = geolocation_id)
  • Filter: ((context_type)::text = 'DESCRIPTIVE_TARGET'::text)
  • Rows Removed by Filter: 1
51. 0.330 0.330 ↑ 1.5 2 33

Index Scan using case_x_case_parent_idx on case_x_case_t cxc (cost=0.42..0.55 rows=3 width=16) (actual time=0.008..0.010 rows=2 loops=33)

  • Index Cond: (parent_id = c.id)
52. 1.071 1.071 ↑ 1.3 32 63

Index Scan using unit_authority_idx on unit_t u (cost=0.28..0.99 rows=41 width=24) (actual time=0.004..0.017 rows=32 loops=63)

  • Index Cond: (managing_authority_id = c.main_impacted_muni_id)
53. 0.378 0.378 ↑ 1.0 1 63

Index Scan using pk_address_t on address_t a (cost=0.43..0.93 rows=1 width=42) (actual time=0.006..0.006 rows=1 loops=63)

  • Index Cond: (id = COALESCE(gac_norm.address_id, gac_desc.address_id))
54.          

SubPlan (forNested Loop Left Join)

55. 1.043 18.207 ↑ 1.0 1 63

Nested Loop (cost=0.43..20.92 rows=1 width=16) (actual time=0.071..0.289 rows=1 loops=63)

  • Join Filter: (ref.reference_type_id = reft.id)
  • Rows Removed by Join Filter: 23
56. 1.764 1.764 ↓ 24.0 24 63

Index Scan using reference_case_idx on reference_t ref (cost=0.43..19.02 rows=1 width=24) (actual time=0.011..0.028 rows=24 loops=63)

  • Index Cond: (external_id = c.id)
  • Filter: (managing_authority_id = c.main_impacted_muni_id)
  • Rows Removed by Filter: 8
57. 15.400 15.400 ↑ 1.0 1 1,540

Seq Scan on reference_type_t reft (cost=0.00..1.89 rows=1 width=8) (actual time=0.006..0.010 rows=1 loops=1,540)

  • Filter: ((reference_type)::text = 'MUNIC'::text)
  • Rows Removed by Filter: 70
58. 0.430 5.355 ↑ 1.0 1 63

Nested Loop (cost=0.43..20.92 rows=1 width=16) (actual time=0.037..0.085 rows=1 loops=63)

  • Join Filter: (ref_1.reference_type_id = reft_1.id)
  • Rows Removed by Join Filter: 5
59. 0.945 0.945 ↓ 6.0 6 63

Index Scan using reference_case_idx on reference_t ref_1 (cost=0.43..19.02 rows=1 width=24) (actual time=0.006..0.015 rows=6 loops=63)

  • Index Cond: (external_id = c.id)
  • Filter: (managing_authority_id = 20)
  • Rows Removed by Filter: 26
60. 3.980 3.980 ↑ 1.0 1 398

Seq Scan on reference_type_t reft_1 (cost=0.00..1.89 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=398)

  • Filter: ((reference_type)::text = 'AATL'::text)
  • Rows Removed by Filter: 70
61. 0.103 1.386 ↓ 0.0 0 63

Nested Loop (cost=0.43..20.92 rows=1 width=16) (actual time=0.016..0.022 rows=0 loops=63)

  • Join Filter: (ref_2.reference_type_id = reft_2.id)
  • Rows Removed by Join Filter: 1
62. 0.819 0.819 ↑ 1.0 1 63

Index Scan using reference_case_idx on reference_t ref_2 (cost=0.43..19.02 rows=1 width=24) (actual time=0.013..0.013 rows=1 loops=63)

  • Index Cond: (external_id = c.id)
  • Filter: (managing_authority_id = 21)
  • Rows Removed by Filter: 32
63. 0.464 0.464 ↑ 1.0 1 58

Seq Scan on reference_type_t reft_2 (cost=0.00..1.89 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=58)

  • Filter: ((reference_type)::text = 'ENVIRON'::text)
  • Rows Removed by Filter: 70
64. 0.063 2.016 ↓ 0.0 0 63

Nested Loop (cost=1.70..21.52 rows=1 width=0) (actual time=0.032..0.032 rows=0 loops=63)

  • Join Filter: (ip.person_id = pn.id)
65. 0.126 1.638 ↑ 1.0 1 63

Nested Loop (cost=1.28..21.04 rows=1 width=16) (actual time=0.021..0.026 rows=1 loops=63)

66. 0.047 1.134 ↑ 1.0 1 63

Nested Loop (cost=0.85..20.49 rows=1 width=8) (actual time=0.014..0.018 rows=1 loops=63)

67. 0.567 0.567 ↓ 2.0 2 63

Index Scan using intervenor_case_idx on intervenor_t i (cost=0.43..12.04 rows=1 width=8) (actual time=0.006..0.009 rows=2 loops=63)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 3
68. 0.520 0.520 ↑ 1.0 1 104

Index Scan using pk_intervening_person_t on intervening_person_t ip (cost=0.42..8.45 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=104)

  • Index Cond: (id = i.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
69. 0.378 0.378 ↑ 1.0 1 63

Index Only Scan using pk_person_t on person_t p (cost=0.42..0.53 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=63)

  • Index Cond: (id = ip.person_id)
  • Heap Fetches: 63
70. 0.315 0.315 ↓ 0.0 0 63

Index Only Scan using pk_person_natural_t on person_natural_t pn (cost=0.42..0.47 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=63)

  • Index Cond: (id = p.id)
  • Heap Fetches: 23
71. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=78,864.27..145,772.61 rows=230,406 width=8) (never executed)

  • Hash Cond: (i_1.id = ip_1.id)
72. 0.000 0.000 ↓ 0.0 0

Seq Scan on intervenor_t i_1 (cost=0.00..61,694.68 rows=775,892 width=16) (never executed)

  • Filter: is_primary
73. 0.000 0.000 ↓ 0.0 0

Hash (cost=72,496.91..72,496.91 rows=509,389 width=8) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=50,397.87..72,496.91 rows=509,389 width=8) (never executed)

  • Hash Cond: (ip_1.person_id = p_1.id)
75. 0.000 0.000 ↓ 0.0 0

Seq Scan on intervening_person_t ip_1 (cost=0.00..16,082.77 rows=589,656 width=16) (never executed)

  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
76. 0.000 0.000 ↓ 0.0 0

Hash (cost=43,950.50..43,950.50 rows=515,789 width=16) (never executed)

77. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=16,997.25..43,950.50 rows=515,789 width=16) (never executed)

  • Hash Cond: (p_1.id = pn_1.id)
78. 0.000 0.000 ↓ 0.0 0

Seq Scan on person_t p_1 (cost=0.00..18,797.99 rows=799,299 width=8) (never executed)

79. 0.000 0.000 ↓ 0.0 0

Hash (cost=10,549.89..10,549.89 rows=515,789 width=8) (never executed)

80. 0.000 0.000 ↓ 0.0 0

Seq Scan on person_natural_t pn_1 (cost=0.00..10,549.89 rows=515,789 width=8) (never executed)

81. 0.069 0.552 ↑ 1.0 1 23

Nested Loop (cost=1.70..21.52 rows=1 width=17) (actual time=0.015..0.024 rows=1 loops=23)

82. 0.023 0.437 ↑ 1.0 1 23

Nested Loop (cost=1.28..21.04 rows=1 width=16) (actual time=0.010..0.019 rows=1 loops=23)

83. 0.045 0.345 ↑ 1.0 1 23

Nested Loop (cost=0.85..20.49 rows=1 width=8) (actual time=0.007..0.015 rows=1 loops=23)

84. 0.184 0.184 ↓ 3.0 3 23

Index Scan using intervenor_case_idx on intervenor_t i_2 (cost=0.43..12.04 rows=1 width=8) (actual time=0.003..0.008 rows=3 loops=23)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 6
85. 0.116 0.116 ↓ 0.0 0 58

Index Scan using pk_intervening_person_t on intervening_person_t ip_2 (cost=0.42..8.45 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=58)

  • Index Cond: (id = i_2.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
86. 0.069 0.069 ↑ 1.0 1 23

Index Only Scan using pk_person_t on person_t p_2 (cost=0.42..0.53 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=23)

  • Index Cond: (id = ip_2.person_id)
  • Heap Fetches: 23
87. 0.046 0.046 ↑ 1.0 1 23

Index Scan using pk_person_natural_t on person_natural_t pn_2 (cost=0.42..0.47 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=23)

  • Index Cond: (id = p_2.id)
88. 0.240 1.200 ↑ 1.0 1 40

Nested Loop (cost=1.70..22.70 rows=1 width=26) (actual time=0.024..0.030 rows=1 loops=40)

89. 0.080 0.600 ↑ 1.0 1 40

Nested Loop (cost=1.28..21.04 rows=1 width=16) (actual time=0.009..0.015 rows=1 loops=40)

90. 0.084 0.440 ↑ 1.0 1 40

Nested Loop (cost=0.85..20.49 rows=1 width=8) (actual time=0.006..0.011 rows=1 loops=40)

91. 0.200 0.200 ↓ 2.0 2 40

Index Scan using intervenor_case_idx on intervenor_t i2 (cost=0.43..12.04 rows=1 width=8) (actual time=0.003..0.005 rows=2 loops=40)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 5
92. 0.156 0.156 ↑ 1.0 1 78

Index Scan using pk_intervening_person_t on intervening_person_t ip2 (cost=0.42..8.45 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=78)

  • Index Cond: (id = i2.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
93. 0.080 0.080 ↑ 1.0 1 40

Index Only Scan using pk_person_t on person_t p2 (cost=0.42..0.53 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=40)

  • Index Cond: (id = ip2.person_id)
  • Heap Fetches: 40
94. 0.240 0.240 ↑ 1.0 1 40

Index Scan using pk_person_legal_t on person_legal_t pl (cost=0.42..0.47 rows=1 width=34) (actual time=0.005..0.006 rows=1 loops=40)

  • Index Cond: (id = p2.id)
95.          

SubPlan (forNested Loop)

96. 0.120 0.120 ↑ 1.0 1 40

Seq Scan on juridical_form_t jf (cost=0.00..1.19 rows=1 width=516) (actual time=0.003..0.003 rows=1 loops=40)

  • Filter: (id = pl.juridical_form_id)
  • Rows Removed by Filter: 14
97. 0.126 1.008 ↓ 0.0 0 63

Nested Loop (cost=1.70..21.52 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=63)

  • Join Filter: (ip_3.person_id = pn_3.id)
98. 0.063 0.756 ↑ 1.0 1 63

Nested Loop (cost=1.28..21.04 rows=1 width=16) (actual time=0.009..0.012 rows=1 loops=63)

99. 0.107 0.567 ↑ 1.0 1 63

Nested Loop (cost=0.85..20.49 rows=1 width=8) (actual time=0.006..0.009 rows=1 loops=63)

100. 0.252 0.252 ↓ 2.0 2 63

Index Scan using intervenor_case_idx on intervenor_t i_3 (cost=0.43..12.04 rows=1 width=8) (actual time=0.003..0.004 rows=2 loops=63)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 3
101. 0.208 0.208 ↑ 1.0 1 104

Index Scan using pk_intervening_person_t on intervening_person_t ip_3 (cost=0.42..8.45 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=104)

  • Index Cond: (id = i_3.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
102. 0.126 0.126 ↑ 1.0 1 63

Index Only Scan using pk_person_t on person_t p_3 (cost=0.42..0.53 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=63)

  • Index Cond: (id = ip_3.person_id)
  • Heap Fetches: 63
103. 0.126 0.126 ↓ 0.0 0 63

Index Only Scan using pk_person_natural_t on person_natural_t pn_3 (cost=0.42..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=63)

  • Index Cond: (id = p_3.id)
  • Heap Fetches: 23
104. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=78,864.27..145,772.61 rows=230,406 width=8) (never executed)

  • Hash Cond: (i_4.id = ip_4.id)
105. 0.000 0.000 ↓ 0.0 0

Seq Scan on intervenor_t i_4 (cost=0.00..61,694.68 rows=775,892 width=16) (never executed)

  • Filter: is_primary
106. 0.000 0.000 ↓ 0.0 0

Hash (cost=72,496.91..72,496.91 rows=509,389 width=8) (never executed)

107. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=50,397.87..72,496.91 rows=509,389 width=8) (never executed)

  • Hash Cond: (ip_4.person_id = p_4.id)
108. 0.000 0.000 ↓ 0.0 0

Seq Scan on intervening_person_t ip_4 (cost=0.00..16,082.77 rows=589,656 width=16) (never executed)

  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
109. 0.000 0.000 ↓ 0.0 0

Hash (cost=43,950.50..43,950.50 rows=515,789 width=16) (never executed)

110. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=16,997.25..43,950.50 rows=515,789 width=16) (never executed)

  • Hash Cond: (p_4.id = pn_4.id)
111. 0.000 0.000 ↓ 0.0 0

Seq Scan on person_t p_4 (cost=0.00..18,797.99 rows=799,299 width=8) (never executed)

112. 0.000 0.000 ↓ 0.0 0

Hash (cost=10,549.89..10,549.89 rows=515,789 width=8) (never executed)

113. 0.000 0.000 ↓ 0.0 0

Seq Scan on person_natural_t pn_4 (cost=0.00..10,549.89 rows=515,789 width=8) (never executed)

114. 0.069 0.460 ↑ 1.0 1 23

Nested Loop (cost=1.70..21.52 rows=1 width=17) (actual time=0.013..0.020 rows=1 loops=23)

115. 0.023 0.345 ↑ 1.0 1 23

Nested Loop (cost=1.28..21.04 rows=1 width=16) (actual time=0.009..0.015 rows=1 loops=23)

116. 0.068 0.276 ↑ 1.0 1 23

Nested Loop (cost=0.85..20.49 rows=1 width=8) (actual time=0.006..0.012 rows=1 loops=23)

117. 0.092 0.092 ↓ 3.0 3 23

Index Scan using intervenor_case_idx on intervenor_t i_5 (cost=0.43..12.04 rows=1 width=8) (actual time=0.002..0.004 rows=3 loops=23)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 6
118. 0.116 0.116 ↓ 0.0 0 58

Index Scan using pk_intervening_person_t on intervening_person_t ip_5 (cost=0.42..8.45 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=58)

  • Index Cond: (id = i_5.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
119. 0.046 0.046 ↑ 1.0 1 23

Index Only Scan using pk_person_t on person_t p_5 (cost=0.42..0.53 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=23)

  • Index Cond: (id = ip_5.person_id)
  • Heap Fetches: 23
120. 0.046 0.046 ↑ 1.0 1 23

Index Scan using pk_person_natural_t on person_natural_t pn_5 (cost=0.42..0.47 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=23)

  • Index Cond: (id = p_5.id)
121. 0.120 0.920 ↑ 1.0 1 40

Nested Loop (cost=1.70..22.70 rows=1 width=25) (actual time=0.018..0.023 rows=1 loops=40)

122. 0.040 0.560 ↑ 1.0 1 40

Nested Loop (cost=1.28..21.04 rows=1 width=16) (actual time=0.009..0.014 rows=1 loops=40)

123. 0.084 0.440 ↑ 1.0 1 40

Nested Loop (cost=0.85..20.49 rows=1 width=8) (actual time=0.006..0.011 rows=1 loops=40)

124. 0.200 0.200 ↓ 2.0 2 40

Index Scan using intervenor_case_idx on intervenor_t i2_1 (cost=0.43..12.04 rows=1 width=8) (actual time=0.003..0.005 rows=2 loops=40)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 5
125. 0.156 0.156 ↑ 1.0 1 78

Index Scan using pk_intervening_person_t on intervening_person_t ip2_1 (cost=0.42..8.45 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=78)

  • Index Cond: (id = i2_1.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
126. 0.080 0.080 ↑ 1.0 1 40

Index Only Scan using pk_person_t on person_t p2_1 (cost=0.42..0.53 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=40)

  • Index Cond: (id = ip2_1.person_id)
  • Heap Fetches: 40
127. 0.120 0.120 ↑ 1.0 1 40

Index Scan using pk_person_legal_t on person_legal_t pl_1 (cost=0.42..0.47 rows=1 width=33) (actual time=0.002..0.003 rows=1 loops=40)

  • Index Cond: (id = p2_1.id)
128.          

SubPlan (forNested Loop)

129. 0.120 0.120 ↑ 1.0 1 40

Seq Scan on juridical_form_t jf_1 (cost=0.00..1.19 rows=1 width=516) (actual time=0.002..0.003 rows=1 loops=40)

  • Filter: (id = pl_1.juridical_form_id)
  • Rows Removed by Filter: 14
130. 0.063 1.323 ↓ 0.0 0 63

Nested Loop (cost=1.70..21.52 rows=1 width=0) (actual time=0.021..0.021 rows=0 loops=63)

  • Join Filter: (ip_6.person_id = pn_6.id)
131. 0.063 1.134 ↑ 1.0 1 63

Nested Loop (cost=1.28..21.04 rows=1 width=16) (actual time=0.009..0.018 rows=1 loops=63)

132. 0.107 0.882 ↑ 1.0 1 63

Nested Loop (cost=0.85..20.49 rows=1 width=8) (actual time=0.006..0.014 rows=1 loops=63)

133. 0.567 0.567 ↓ 2.0 2 63

Index Scan using intervenor_case_idx on intervenor_t i_6 (cost=0.43..12.04 rows=1 width=8) (actual time=0.002..0.009 rows=2 loops=63)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 3
134. 0.208 0.208 ↑ 1.0 1 104

Index Scan using pk_intervening_person_t on intervening_person_t ip_6 (cost=0.42..8.45 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=104)

  • Index Cond: (id = i_6.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
135. 0.189 0.189 ↑ 1.0 1 63

Index Only Scan using pk_person_t on person_t p_6 (cost=0.42..0.53 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=63)

  • Index Cond: (id = ip_6.person_id)
  • Heap Fetches: 63
136. 0.126 0.126 ↓ 0.0 0 63

Index Only Scan using pk_person_natural_t on person_natural_t pn_6 (cost=0.42..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=63)

  • Index Cond: (id = p_6.id)
  • Heap Fetches: 23
137. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=78,864.27..145,772.61 rows=230,406 width=8) (never executed)

  • Hash Cond: (i_7.id = ip_7.id)
138. 0.000 0.000 ↓ 0.0 0

Seq Scan on intervenor_t i_7 (cost=0.00..61,694.68 rows=775,892 width=16) (never executed)

  • Filter: is_primary
139. 0.000 0.000 ↓ 0.0 0

Hash (cost=72,496.91..72,496.91 rows=509,389 width=8) (never executed)

140. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=50,397.87..72,496.91 rows=509,389 width=8) (never executed)

  • Hash Cond: (ip_7.person_id = p_7.id)
141. 0.000 0.000 ↓ 0.0 0

Seq Scan on intervening_person_t ip_7 (cost=0.00..16,082.77 rows=589,656 width=16) (never executed)

  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
142. 0.000 0.000 ↓ 0.0 0

Hash (cost=43,950.50..43,950.50 rows=515,789 width=16) (never executed)

143. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=16,997.25..43,950.50 rows=515,789 width=16) (never executed)

  • Hash Cond: (p_7.id = pn_7.id)
144. 0.000 0.000 ↓ 0.0 0

Seq Scan on person_t p_7 (cost=0.00..18,797.99 rows=799,299 width=8) (never executed)

145. 0.000 0.000 ↓ 0.0 0

Hash (cost=10,549.89..10,549.89 rows=515,789 width=8) (never executed)

146. 0.000 0.000 ↓ 0.0 0

Seq Scan on person_natural_t pn_7 (cost=0.00..10,549.89 rows=515,789 width=8) (never executed)

147. 0.069 0.460 ↑ 1.0 1 23

Nested Loop (cost=1.70..21.52 rows=1 width=17) (actual time=0.014..0.020 rows=1 loops=23)

148. 0.046 0.345 ↑ 1.0 1 23

Nested Loop (cost=1.28..21.04 rows=1 width=16) (actual time=0.010..0.015 rows=1 loops=23)

149. 0.022 0.253 ↑ 1.0 1 23

Nested Loop (cost=0.85..20.49 rows=1 width=8) (actual time=0.006..0.011 rows=1 loops=23)

150. 0.115 0.115 ↓ 3.0 3 23

Index Scan using intervenor_case_idx on intervenor_t i_8 (cost=0.43..12.04 rows=1 width=8) (actual time=0.003..0.005 rows=3 loops=23)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 6
151. 0.116 0.116 ↓ 0.0 0 58

Index Scan using pk_intervening_person_t on intervening_person_t ip_8 (cost=0.42..8.45 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=58)

  • Index Cond: (id = i_8.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
152. 0.046 0.046 ↑ 1.0 1 23

Index Only Scan using pk_person_t on person_t p_8 (cost=0.42..0.53 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=23)

  • Index Cond: (id = ip_8.person_id)
  • Heap Fetches: 23
153. 0.046 0.046 ↑ 1.0 1 23

Index Scan using pk_person_natural_t on person_natural_t pn_8 (cost=0.42..0.47 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=23)

  • Index Cond: (id = p_8.id)
154. 0.160 1.000 ↑ 1.0 1 40

Nested Loop (cost=1.70..22.70 rows=1 width=25) (actual time=0.019..0.025 rows=1 loops=40)

155. 0.040 0.640 ↑ 1.0 1 40

Nested Loop (cost=1.28..21.04 rows=1 width=16) (actual time=0.010..0.016 rows=1 loops=40)

156. 0.046 0.480 ↑ 1.0 1 40

Nested Loop (cost=0.85..20.49 rows=1 width=8) (actual time=0.007..0.012 rows=1 loops=40)

157. 0.200 0.200 ↓ 2.0 2 40

Index Scan using intervenor_case_idx on intervenor_t i2_2 (cost=0.43..12.04 rows=1 width=8) (actual time=0.003..0.005 rows=2 loops=40)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 5
158. 0.234 0.234 ↑ 1.0 1 78

Index Scan using pk_intervening_person_t on intervening_person_t ip2_2 (cost=0.42..8.45 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=78)

  • Index Cond: (id = i2_2.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
159. 0.120 0.120 ↑ 1.0 1 40

Index Only Scan using pk_person_t on person_t p2_2 (cost=0.42..0.53 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=40)

  • Index Cond: (id = ip2_2.person_id)
  • Heap Fetches: 40
160. 0.120 0.120 ↑ 1.0 1 40

Index Scan using pk_person_legal_t on person_legal_t pl_2 (cost=0.42..0.47 rows=1 width=33) (actual time=0.002..0.003 rows=1 loops=40)

  • Index Cond: (id = p2_2.id)
161.          

SubPlan (forNested Loop)

162. 0.080 0.080 ↑ 1.0 1 40

Seq Scan on juridical_form_t jf_2 (cost=0.00..1.19 rows=1 width=516) (actual time=0.002..0.002 rows=1 loops=40)

  • Filter: (id = pl_2.juridical_form_id)
  • Rows Removed by Filter: 14
163. 0.126 1.827 ↑ 1.0 1 63

Nested Loop Left Join (cost=1.56..26.88 rows=1 width=17) (actual time=0.026..0.029 rows=1 loops=63)

164. 0.063 1.449 ↑ 1.0 1 63

Nested Loop Left Join (cost=1.13..20.85 rows=1 width=8) (actual time=0.021..0.023 rows=1 loops=63)

165. 0.063 1.197 ↑ 1.0 1 63

Nested Loop (cost=0.85..20.54 rows=1 width=8) (actual time=0.016..0.019 rows=1 loops=63)

166. 0.504 0.504 ↓ 2.0 2 63

Index Scan using intervenor_case_idx on intervenor_t i_9 (cost=0.43..12.08 rows=1 width=8) (actual time=0.006..0.008 rows=2 loops=63)

  • Index Cond: (case_id = c.id)
  • Filter: ((((c.case_type)::text = ANY ('{URBANISM,ENVIRONMENT,ADMINISTRATIVE,SUBDIVISION,STATUTORY_PROVISIONING,GEN,PPAS}'::text[])) AND ((intervenor_role)::text = 'MANAGER_EXPERT'::text)) OR (((c.case_type)::text = ANY ('{REPORTING,INFRINGEMENT}'::text[])) AND ((intervenor_role)::text = 'INFRACTION_REPORTING_CONTROLLER_PU'::text)) OR (((c.case_type)::text = 'PEB'::text) AND ((intervenor_role)::text = 'MANAGER_PEB'::text)))
  • Rows Removed by Filter: 6
167. 0.630 0.630 ↓ 0.0 0 126

Index Scan using pk_intervening_user_t on intervening_user_t iu (cost=0.42..8.45 rows=1 width=16) (actual time=0.004..0.005 rows=0 loops=126)

  • Index Cond: (id = i_9.id)
  • Filter: (on_behalf_of_authority_id = 4)
  • Rows Removed by Filter: 0
168. 0.189 0.189 ↑ 1.0 1 63

Index Scan using pk_user_person_t on user_person_t up (cost=0.28..0.30 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=63)

  • Index Cond: (id = iu.user_id)
169. 0.252 0.252 ↑ 1.0 1 63

Index Scan using pk_person_natural_t on person_natural_t p_9 (cost=0.42..6.01 rows=1 width=25) (actual time=0.004..0.004 rows=1 loops=63)

  • Index Cond: (id = up.abstract_person_id)
170. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.56..26.88 rows=1 width=17) (never executed)

171. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.13..20.85 rows=1 width=8) (never executed)

172. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.85..20.54 rows=1 width=8) (never executed)

173. 0.000 0.000 ↓ 0.0 0

Index Scan using intervenor_case_idx on intervenor_t i_10 (cost=0.43..12.08 rows=1 width=8) (never executed)

  • Index Cond: (case_id = c.id)
  • Filter: ((((c.case_type)::text = ANY ('{URBANISM,ENVIRONMENT,ADMINISTRATIVE,SUBDIVISION,STATUTORY_PROVISIONING,GEN,PPAS}'::text[])) AND ((intervenor_role)::text = 'MANAGER_EXPERT'::text)) OR (((c.case_type)::text = ANY ('{REPORTING,INFRINGEMENT}'::text[])) AND ((intervenor_role)::text = 'INFRACTION_REPORTING_CONTROLLER_PU'::text)) OR (((c.case_type)::text = 'PEB'::text) AND ((intervenor_role)::text = 'MANAGER_PEB'::text)))
174. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_intervening_user_t on intervening_user_t iu_1 (cost=0.42..8.45 rows=1 width=16) (never executed)

  • Index Cond: (id = i_10.id)
  • Filter: (on_behalf_of_authority_id = c.managing_authority_id)
175. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_user_person_t on user_person_t up_1 (cost=0.28..0.30 rows=1 width=16) (never executed)

  • Index Cond: (id = iu_1.user_id)
176. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_person_natural_t on person_natural_t p_10 (cost=0.42..6.01 rows=1 width=25) (never executed)

  • Index Cond: (id = up_1.abstract_person_id)
177. 0.126 1.071 ↑ 1.0 1 63

Nested Loop (cost=1.00..17.07 rows=1 width=10) (actual time=0.016..0.017 rows=1 loops=63)

178. 0.063 0.819 ↑ 1.0 1 63

Nested Loop (cost=0.85..16.90 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=63)

179. 0.378 0.378 ↑ 1.0 1 63

Index Scan using pk_configuration_t on configuration_t ct (cost=0.42..8.44 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=63)

  • Index Cond: (c.configuration_id = id)
180. 0.378 0.378 ↑ 1.0 1 63

Index Scan using pk_phase_t on phase_t pht (cost=0.43..8.45 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=63)

  • Index Cond: (id = ct.current_phase_id)
181. 0.126 0.126 ↑ 1.0 1 63

Index Scan using pk_phase_definition_t on phase_definition_t pdt (cost=0.15..0.17 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=63)

  • Index Cond: (id = pht.definition_id)
Planning time : 635.314 ms