explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k2kk

Settings
# exclusive inclusive rows x rows loops node
1. 1.091 1,194.625 ↓ 15.8 63 1

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

2. 0.278 1,160.231 ↓ 15.8 63 1

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

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

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

4. 0.028 1,158.581 ↓ 33.0 33 1

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

5. 0.055 1,158.421 ↓ 33.0 33 1

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

6. 0.047 1,158.036 ↓ 33.0 33 1

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

7. 0.059 1,157.725 ↓ 33.0 33 1

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

8. 0.018 1,157.435 ↓ 33.0 33 1

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

9. 0.059 1,157.384 ↓ 33.0 33 1

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

10. 0.033 1,157.325 ↓ 33.0 33 1

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

11. 0.065 1,157.127 ↓ 33.0 33 1

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

12. 0.085 1,156.831 ↓ 33.0 33 1

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

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

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

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

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

15. 0.036 1,155.656 ↓ 33.0 33 1

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

16. 0.053 1,155.587 ↓ 33.0 33 1

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

17. 11.642 1,155.468 ↓ 33.0 33 1

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

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

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

19. 140.146 1,055.630 ↓ 74.2 113,691 1

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

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

Hash Left Join (cost=6,923.55..165,388.24 rows=1,533 width=149) (actual time=147.230..915.484 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. 94.100 336.121 ↓ 13.1 118,622 1

Hash Left Join (cost=6,619.65..14,025.52 rows=9,059 width=283) (actual time=143.995..336.121 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. 74.762 101.869 ↑ 1.0 114,287 1

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

  • Hash Cond: (top.meeting_id = ccmeeting.id)
23. 23.828 23.828 ↑ 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.010..23.828 rows=114,287 loops=1)

24. 1.635 3.279 ↑ 1.0 9,402 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 569kB
25. 1.644 1.644 ↑ 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.005..1.644 rows=9,402 loops=1)

26. 34.608 140.152 ↑ 1.0 73,317 1

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

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

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

  • Hash Cond: (pinq.inquiry_id = inq.id)
28. 9.039 9.039 ↑ 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..9.039 rows=73,317 loops=1)

29. 27.351 49.225 ↑ 1.0 73,200 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 15868kB
30. 21.874 21.874 ↑ 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.020..21.874 rows=73,200 loops=1)

31. 1.620 3.149 ↑ 1.0 9,535 1

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

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

Seq Scan on meeting_t meeting (cost=0.00..184.51 rows=9,551 width=16) (actual time=0.007..1.529 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.264 0.264 ↑ 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.008..0.008 rows=1 loops=33)

  • Index Cond: (id = c.id)
42. 0.330 0.330 ↑ 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.010 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.000 0.000 ↓ 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.000..0.000 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.231 0.231 ↑ 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.007 rows=1 loops=33)

  • Index Cond: (pr.analysis_id = id)
48. 0.264 0.264 ↑ 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.007..0.008 rows=1 loops=33)

  • Index Cond: (c.id = case_id)
49. 0.330 0.330 ↑ 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.010 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.297 0.297 ↑ 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.007..0.009 rows=2 loops=33)

  • Index Cond: (parent_id = c.id)
52. 1.008 1.008 ↑ 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.003..0.016 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.005..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.008 16.317 ↑ 1.0 1 63

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

  • Join Filter: (ref.reference_type_id = reft.id)
  • Rows Removed by Join Filter: 23
56. 1.449 1.449 ↓ 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.008..0.023 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. 13.860 13.860 ↑ 1.0 1 1,540

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

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

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

  • Join Filter: (ref_1.reference_type_id = reft_1.id)
  • Rows Removed by Join Filter: 5
59. 0.819 0.819 ↓ 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.005..0.013 rows=6 loops=63)

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

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

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

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

  • Join Filter: (ref_2.reference_type_id = reft_2.id)
  • Rows Removed by Join Filter: 1
62. 0.693 0.693 ↑ 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.011..0.011 rows=1 loops=63)

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

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

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

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

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

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

66. 0.088 1.071 ↑ 1.0 1 63

Nested Loop (cost=0.85..20.49 rows=1 width=8) (actual time=0.013..0.017 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.416 0.416 ↑ 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.004 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.252 0.252 ↓ 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.004..0.004 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.046 0.529 ↑ 1.0 1 23

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

82. 0.046 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.046 0.046 ↑ 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.002 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.280 1.120 ↑ 1.0 1 40

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

89. 0.040 0.520 ↑ 1.0 1 40

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

90. 0.084 0.400 ↑ 1.0 1 40

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

91. 0.160 0.160 ↓ 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.002..0.004 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.200 0.200 ↑ 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.005 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.002..0.003 rows=1 loops=40)

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

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

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

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

99. 0.044 0.504 ↑ 1.0 1 63

Nested Loop (cost=0.85..20.49 rows=1 width=8) (actual time=0.006..0.008 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.002..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.046 0.414 ↑ 1.0 1 23

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

115. 0.023 0.322 ↑ 1.0 1 23

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

116. 0.045 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)

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.200 0.880 ↑ 1.0 1 40

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

122. 0.040 0.520 ↑ 1.0 1 40

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

123. 0.044 0.400 ↑ 1.0 1 40

Nested Loop (cost=0.85..20.49 rows=1 width=8) (actual time=0.006..0.010 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.002..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.080 0.080 ↑ 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.002 rows=1 loops=40)

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

SubPlan (forNested Loop)

129. 0.080 0.080 ↑ 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.002 rows=1 loops=40)

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

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

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

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

132. 0.107 0.504 ↑ 1.0 1 63

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

133. 0.189 0.189 ↓ 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.003 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.126 0.126 ↑ 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.002 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.046 0.437 ↑ 1.0 1 23

Nested Loop (cost=1.70..21.52 rows=1 width=17) (actual time=0.013..0.019 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.045 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.092 0.092 ↓ 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.002..0.004 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 0.880 ↑ 1.0 1 40

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

155. 0.040 0.520 ↑ 1.0 1 40

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

156. 0.044 0.400 ↑ 1.0 1 40

Nested Loop (cost=0.85..20.49 rows=1 width=8) (actual time=0.007..0.010 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.156 0.156 ↑ 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.002..0.002 rows=1 loops=78)

  • Index Cond: (id = i2_2.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
159. 0.080 0.080 ↑ 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.002 rows=1 loops=40)

  • Index Cond: (id = ip2_2.person_id)
  • Heap Fetches: 40
160. 0.080 0.080 ↑ 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.002 rows=1 loops=40)

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

SubPlan (forNested Loop)

162. 0.120 0.120 ↑ 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.003 rows=1 loops=40)

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

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

164. 0.126 1.386 ↑ 1.0 1 63

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

165. 0.126 1.071 ↑ 1.0 1 63

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

166. 0.441 0.441 ↓ 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.005..0.007 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.504 0.504 ↓ 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.004 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.003..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.063 1.008 ↑ 1.0 1 63

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

178. 0.126 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.315 0.315 ↑ 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.005..0.005 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.005..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 : 581.766 ms