explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5K6Q

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.099 17,700.935 ↑ 1.0 250 1

Limit (cost=5.43..937,936.28 rows=250 width=7,138) (actual time=1,842.051..17,700.935 rows=250 loops=1)

2. 341.237 17,700.836 ↑ 26.1 250 1

Nested Loop Left Join (cost=5.43..24,483,752.23 rows=6,526 width=7,138) (actual time=1,842.050..17,700.836 rows=250 loops=1)

3. 5.912 2,921.174 ↑ 26.1 250 1

Nested Loop Left Join (cost=5.00..1,366,291.39 rows=6,526 width=2,767) (actual time=3.716..2,921.174 rows=250 loops=1)

  • Join Filter: (termdic.id = term.term_dictionary_id)
  • Rows Removed by Join Filter: 58951
4. 15.673 2,911.762 ↑ 26.1 250 1

Nested Loop Left Join (cost=5.00..1,343,181.40 rows=6,526 width=2,696) (actual time=3.313..2,911.762 rows=250 loops=1)

  • Join Filter: (term.id = tfc.term_id)
  • Rows Removed by Join Filter: 205701
5. 6.788 2,884.089 ↑ 26.1 250 1

Nested Loop (cost=5.00..1,262,596.64 rows=6,526 width=2,696) (actual time=1.509..2,884.089 rows=250 loops=1)

  • Join Filter: (pdt.id = p.definition_id)
  • Rows Removed by Join Filter: 73993
6. 0.604 2,871.801 ↑ 26.1 250 1

Nested Loop Left Join (cost=5.00..1,233,417.70 rows=6,526 width=2,694) (actual time=1.474..2,871.801 rows=250 loops=1)

7. 5.339 2,842.697 ↑ 26.1 250 1

Nested Loop Left Join (cost=4.57..1,227,623.48 rows=6,526 width=2,686) (actual time=1.469..2,842.697 rows=250 loops=1)

  • Join Filter: (c.version_id = vers.id)
  • Rows Removed by Join Filter: 46071
8. 1.395 2,834.608 ↑ 26.1 250 1

Nested Loop Left Join (cost=4.57..1,209,407.62 rows=6,526 width=2,588) (actual time=1.166..2,834.608 rows=250 loops=1)

  • Join Filter: (c.managing_authority_id = a.id)
  • Rows Removed by Join Filter: 6747
9. 0.750 2,832.213 ↑ 26.1 250 1

Nested Loop Left Join (cost=4.57..1,206,665.35 rows=6,526 width=1,548) (actual time=0.812..2,832.213 rows=250 loops=1)

10. 0.900 2,701.713 ↑ 26.1 250 1

Nested Loop Left Join (cost=4.13..1,200,534.21 rows=6,526 width=524) (actual time=0.802..2,701.713 rows=250 loops=1)

11. 0.705 2,649.063 ↑ 26.1 250 1

Nested Loop Left Join (cost=3.71..1,197,408.14 rows=6,526 width=349) (actual time=0.789..2,649.063 rows=250 loops=1)

12. 0.861 2,548.358 ↑ 26.1 250 1

Nested Loop Left Join (cost=3.29..1,194,115.21 rows=6,526 width=319) (actual time=0.780..2,548.358 rows=250 loops=1)

13. 0.710 2,546.247 ↑ 26.1 250 1

Nested Loop Left Join (cost=2.99..1,191,969.80 rows=6,526 width=295) (actual time=0.776..2,546.247 rows=250 loops=1)

14. 0.662 2,420.287 ↑ 26.1 250 1

Nested Loop Left Join (cost=2.57..1,187,929.99 rows=6,526 width=303) (actual time=0.766..2,420.287 rows=250 loops=1)

15. 1.004 2,419.375 ↑ 26.1 250 1

Nested Loop (cost=2.13..1,184,047.84 rows=6,526 width=303) (actual time=0.764..2,419.375 rows=250 loops=1)

16. 0.842 2,283.871 ↑ 26.1 250 1

Nested Loop (cost=1.71..1,180,155.90 rows=6,526 width=295) (actual time=0.740..2,283.871 rows=250 loops=1)

17. 0.940 2,163.279 ↑ 26.1 250 1

Nested Loop Left Join (cost=1.28..1,176,884.47 rows=6,526 width=287) (actual time=0.731..2,163.279 rows=250 loops=1)

18. 0.634 2,068.339 ↑ 26.1 250 1

Nested Loop (cost=0.85..1,173,712.37 rows=6,526 width=273) (actual time=0.723..2,068.339 rows=250 loops=1)

19. 114.446 1,910.205 ↑ 26.2 250 1

Nested Loop (cost=0.43..1,169,548.95 rows=6,546 width=78) (actual time=0.714..1,910.205 rows=250 loops=1)

20. 32.053 32.053 ↑ 2.9 251,958 1

Seq Scan on geolocation_t g (cost=0.00..15,049.85 rows=736,485 width=24) (actual time=0.011..32.053 rows=251,958 loops=1)

21. 1,763.706 1,763.706 ↓ 0.0 0 251,958

Index Scan using pk_address_t on address_t ad (cost=0.43..1.56 rows=1 width=70) (actual time=0.007..0.007 rows=0 loops=251,958)

  • Index Cond: (id = g.target_principal_address_id)
  • Filter: ((street_french)::text ~~ '%ble%'::text)
  • Rows Removed by Filter: 1
22. 157.500 157.500 ↑ 1.0 1 250

Index Scan using pk_case_t on case_t c (cost=0.42..0.63 rows=1 width=203) (actual time=0.628..0.630 rows=1 loops=250)

  • Index Cond: (id = g.case_id)
  • Filter: ((NOT deleted) AND (NOT a_new_version_exists))
23. 94.000 94.000 ↑ 1.0 1 250

Index Scan using case_status_board_to_case_idx on case_status_board_t board (cost=0.42..0.48 rows=1 width=22) (actual time=0.375..0.376 rows=1 loops=250)

  • Index Cond: (c.id = case_id)
24. 119.750 119.750 ↑ 1.0 1 250

Index Scan using pk_configuration_t on configuration_t config (cost=0.42..0.49 rows=1 width=16) (actual time=0.477..0.479 rows=1 loops=250)

  • Index Cond: (id = c.configuration_id)
25. 134.500 134.500 ↑ 1.0 1 250

Index Scan using pk_phase_t on phase_t p (cost=0.43..0.59 rows=1 width=24) (actual time=0.538..0.538 rows=1 loops=250)

  • Index Cond: (id = config.current_phase_id)
26. 0.250 0.250 ↓ 0.0 0 250

Index Scan using pk_deadline_acm_t on deadline_acm_t d1 (cost=0.43..0.58 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=250)

  • Index Cond: (p.deadline_id = id)
27. 125.250 125.250 ↑ 1.0 1 250

Index Scan using geolocation_localization_context_geolocation_id_idx on geolocation_localization_context_t glc (cost=0.43..0.61 rows=1 width=8) (actual time=0.492..0.501 rows=1 loops=250)

  • Index Cond: (g.id = geolocation_id)
  • Filter: ((context_type)::text = 'TARGET'::text)
  • Rows Removed by Filter: 2
28. 1.250 1.250 ↓ 0.0 0 250

Index Scan using pk_environment_permit_request_t on environment_permit_request_t eprt (cost=0.29..0.32 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=250)

  • Index Cond: (c.id = id)
29. 100.000 100.000 ↑ 1.0 1 250

Index Scan using pk_permit_request_t on permit_request_t prt (cost=0.42..0.49 rows=1 width=38) (actual time=0.399..0.400 rows=1 loops=250)

  • Index Cond: (c.id = id)
30. 51.750 51.750 ↓ 0.0 0 250

Index Scan using pk_delivery_t on delivery_t del (cost=0.42..0.47 rows=1 width=191) (actual time=0.207..0.207 rows=0 loops=250)

  • Index Cond: (prt.delivery_id = id)
31. 129.750 129.750 ↑ 1.0 1 250

Index Scan using pk_reference_t on reference_t r (cost=0.43..0.93 rows=1 width=1,040) (actual time=0.516..0.519 rows=1 loops=250)

  • Index Cond: (c.main_reference_id = id)
32. 0.657 1.000 ↑ 1.0 28 250

Materialize (cost=0.00..1.42 rows=28 width=1,040) (actual time=0.002..0.004 rows=28 loops=250)

33. 0.343 0.343 ↑ 1.0 28 1

Seq Scan on authority_t a (cost=0.00..1.28 rows=28 width=1,040) (actual time=0.336..0.343 rows=28 loops=1)

34. 2.435 2.750 ↑ 1.0 185 250

Materialize (cost=0.00..8.79 rows=186 width=106) (actual time=0.000..0.011 rows=185 loops=250)

35. 0.315 0.315 ↑ 1.0 186 1

Seq Scan on version_t vers (cost=0.00..7.86 rows=186 width=106) (actual time=0.008..0.315 rows=186 loops=1)

36. 28.500 28.500 ↓ 0.0 0 250

Index Scan using term_for_case_pk on term_for_case_t tfc (cost=0.43..0.88 rows=1 width=24) (actual time=0.114..0.114 rows=0 loops=250)

  • Index Cond: (id = board.nearest_term_date_id)
37. 4.036 5.500 ↑ 1.0 297 250

Materialize (cost=0.00..8.47 rows=298 width=18) (actual time=0.000..0.022 rows=297 loops=250)

38. 1.464 1.464 ↑ 1.0 298 1

Seq Scan on phase_definition_t pdt (cost=0.00..6.98 rows=298 width=18) (actual time=0.008..1.464 rows=298 loops=1)

39. 10.390 12.000 ↑ 1.0 823 250

Materialize (cost=0.00..23.34 rows=823 width=16) (actual time=0.000..0.048 rows=823 loops=250)

40. 1.610 1.610 ↑ 1.0 823 1

Seq Scan on term_t term (cost=0.00..19.23 rows=823 width=16) (actual time=0.007..1.610 rows=823 loops=1)

41. 3.166 3.500 ↑ 1.0 236 250

Materialize (cost=0.00..8.54 rows=236 width=87) (actual time=0.000..0.014 rows=236 loops=250)

42. 0.334 0.334 ↑ 1.0 236 1

Seq Scan on term_dictionary_t termdic (cost=0.00..7.36 rows=236 width=87) (actual time=0.015..0.334 rows=236 loops=1)

43. 37.500 37.500 ↓ 0.0 0 250

Index Scan using pk_deadline_acm_t on deadline_acm_t dacmterm (cost=0.43..0.51 rows=1 width=16) (actual time=0.150..0.150 rows=0 loops=250)

  • Index Cond: (id = tfc.deadline_id)
44.          

SubPlan (for Nested Loop Left Join)

45. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.85..24.75 rows=1 width=0) (never executed)

46. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (case_id = c.id)
  • Filter: (is_primary AND ((nature_type)::text = 'NATURAL'::text))
47. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_intervening_person_t on intervening_person_t intervp2 (cost=0.42..8.45 rows=1 width=8) (never executed)

  • Index Cond: (id = interv2.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
48. 315.337 782.931 ↓ 5.7 353,856 1

Hash Join (cost=85,543.75..110,040.60 rows=61,754 width=8) (actual time=331.283..782.931 rows=353,856 loops=1)

  • Hash Cond: (intervp2_1.id = interv2_1.id)
49. 136.611 136.611 ↑ 1.0 590,719 1

Seq Scan on intervening_person_t intervp2_1 (cost=0.00..16,130.43 rows=592,770 width=8) (actual time=0.014..136.611 rows=590,719 loops=1)

  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 118197
50. 72.681 330.983 ↓ 2.1 391,969 1

Hash (cost=82,362.57..82,362.57 rows=182,974 width=16) (actual time=330.983..330.983 rows=391,969 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 8 (originally 4) Memory Usage: 3330kB
51. 106.221 258.302 ↓ 2.1 391,969 1

Bitmap Heap Scan on intervenor_t interv2_1 (cost=13,485.57..82,362.57 rows=182,974 width=16) (actual time=153.091..258.302 rows=391,969 loops=1)

  • Recheck Cond: ((nature_type)::text = 'NATURAL'::text)
  • Filter: is_primary
  • Rows Removed by Filter: 12543
  • Heap Blocks: exact=6875
52. 152.081 152.081 ↑ 1.0 404,512 1

Bitmap Index Scan on intervenor_type_idx (cost=0.00..13,439.83 rows=404,720 width=0) (actual time=152.081..152.081 rows=404,512 loops=1)

  • Index Cond: ((nature_type)::text = 'NATURAL'::text)
53. 2.506 323.990 ↑ 1.0 1 179

Nested Loop (cost=1.70..25.82 rows=1 width=32) (actual time=1.803..1.810 rows=1 loops=179)

  • Join Filter: (intervp.person_id = pnat.id)
54. 0.716 238.965 ↑ 1.0 1 179

Nested Loop (cost=1.28..25.30 rows=1 width=16) (actual time=1.328..1.335 rows=1 loops=179)

55. 0.511 167.365 ↑ 1.0 1 179

Nested Loop (cost=0.85..24.75 rows=1 width=8) (actual time=0.930..0.935 rows=1 loops=179)

56. 136.398 136.398 ↑ 1.0 1 179

Index Scan using intervenor_case_idx on intervenor_t interv (cost=0.43..16.30 rows=1 width=8) (actual time=0.757..0.762 rows=1 loops=179)

  • Index Cond: (case_id = c.id)
  • Filter: (is_primary AND ((nature_type)::text = 'NATURAL'::text))
  • Rows Removed by Filter: 1
57. 30.456 30.456 ↑ 1.0 1 188

Index Scan using pk_intervening_person_t on intervening_person_t intervp (cost=0.42..8.45 rows=1 width=16) (actual time=0.161..0.162 rows=1 loops=188)

  • Index Cond: (id = interv.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
58. 70.884 70.884 ↑ 1.0 1 179

Index Only Scan using pk_person_t on person_t pers (cost=0.42..0.54 rows=1 width=8) (actual time=0.396..0.396 rows=1 loops=179)

  • Index Cond: (id = intervp.person_id)
  • Heap Fetches: 179
59. 82.519 82.519 ↑ 1.0 1 179

Index Scan using pk_person_natural_t on person_natural_t pnat (cost=0.42..0.50 rows=1 width=25) (actual time=0.461..0.461 rows=1 loops=179)

  • Index Cond: (id = pers.id)
60. 0.633 87.685 ↓ 0.0 0 71

Nested Loop (cost=1.70..26.98 rows=1 width=32) (actual time=1.231..1.235 rows=0 loops=71)

  • Join Filter: (intervp_1.person_id = pleg.id)
61. 0.072 78.384 ↓ 0.0 0 71

Nested Loop (cost=1.28..25.30 rows=1 width=16) (actual time=1.101..1.104 rows=0 loops=71)

62. 0.199 69.864 ↓ 0.0 0 71

Nested Loop (cost=0.85..24.75 rows=1 width=8) (actual time=0.980..0.984 rows=0 loops=71)

63. 65.817 65.817 ↓ 0.0 0 71

Index Scan using intervenor_case_idx on intervenor_t interv_1 (cost=0.43..16.30 rows=1 width=8) (actual time=0.917..0.927 rows=0 loops=71)

  • Index Cond: (case_id = c.id)
  • Filter: (is_primary AND ((nature_type)::text = 'LEGAL'::text))
  • Rows Removed by Filter: 2
64. 3.848 3.848 ↑ 1.0 1 26

Index Scan using pk_intervening_person_t on intervening_person_t intervp_1 (cost=0.42..8.45 rows=1 width=16) (actual time=0.148..0.148 rows=1 loops=26)

  • Index Cond: (id = interv_1.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
65. 8.448 8.448 ↑ 1.0 1 22

Index Only Scan using pk_person_t on person_t pers_1 (cost=0.42..0.54 rows=1 width=8) (actual time=0.384..0.384 rows=1 loops=22)

  • Index Cond: (id = intervp_1.person_id)
  • Heap Fetches: 22
66. 8.294 8.294 ↑ 1.0 1 22

Index Scan using pk_person_legal_t on person_legal_t pleg (cost=0.42..0.47 rows=1 width=532) (actual time=0.377..0.377 rows=1 loops=22)

  • Index Cond: (id = pers_1.id)
67.          

SubPlan (for Nested Loop)

68. 0.374 0.374 ↑ 1.0 1 22

Seq Scan on juridical_form_t (cost=0.00..1.19 rows=1 width=516) (actual time=0.017..0.017 rows=1 loops=22)

  • Filter: (id = pleg.juridical_form_id)
  • Rows Removed by Filter: 14
69. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.85..24.75 rows=1 width=0) (never executed)

70. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (case_id = c.id)
  • Filter: (is_primary AND ((nature_type)::text = 'NATURAL'::text))
71. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_intervening_person_t on intervening_person_t intervp2_2 (cost=0.42..8.45 rows=1 width=8) (never executed)

  • Index Cond: (id = interv2_2.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
72. 366.391 695.384 ↓ 5.7 353,856 1

Hash Join (cost=85,543.75..110,040.60 rows=61,754 width=8) (actual time=181.812..695.384 rows=353,856 loops=1)

  • Hash Cond: (intervp2_3.id = interv2_3.id)
73. 148.011 148.011 ↑ 1.0 590,719 1

Seq Scan on intervening_person_t intervp2_3 (cost=0.00..16,130.43 rows=592,770 width=8) (actual time=0.012..148.011 rows=590,719 loops=1)

  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 118197
74. 77.718 180.982 ↓ 2.1 391,969 1

Hash (cost=82,362.57..82,362.57 rows=182,974 width=16) (actual time=180.982..180.982 rows=391,969 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 8 (originally 4) Memory Usage: 3330kB
75. 67.135 103.264 ↓ 2.1 391,969 1

Bitmap Heap Scan on intervenor_t interv2_3 (cost=13,485.57..82,362.57 rows=182,974 width=16) (actual time=37.115..103.264 rows=391,969 loops=1)

  • Recheck Cond: ((nature_type)::text = 'NATURAL'::text)
  • Filter: is_primary
  • Rows Removed by Filter: 12543
  • Heap Blocks: exact=6875
76. 36.129 36.129 ↑ 1.0 404,512 1

Bitmap Index Scan on intervenor_type_idx (cost=0.00..13,439.83 rows=404,720 width=0) (actual time=36.128..36.129 rows=404,512 loops=1)

  • Index Cond: ((nature_type)::text = 'NATURAL'::text)
77. 0.895 5.549 ↑ 1.0 1 179

Nested Loop (cost=1.70..25.82 rows=1 width=32) (actual time=0.029..0.031 rows=1 loops=179)

  • Join Filter: (intervp_2.person_id = pnat_1.id)
78. 0.358 3.938 ↑ 1.0 1 179

Nested Loop (cost=1.28..25.30 rows=1 width=16) (actual time=0.021..0.022 rows=1 loops=179)

79. 0.313 2.864 ↑ 1.0 1 179

Nested Loop (cost=0.85..24.75 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=179)

80. 1.611 1.611 ↑ 1.0 1 179

Index Scan using intervenor_case_idx on intervenor_t interv_2 (cost=0.43..16.30 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=179)

  • Index Cond: (case_id = c.id)
  • Filter: (is_primary AND ((nature_type)::text = 'NATURAL'::text))
  • Rows Removed by Filter: 1
81. 0.940 0.940 ↑ 1.0 1 188

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

  • Index Cond: (id = interv_2.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
82. 0.716 0.716 ↑ 1.0 1 179

Index Only Scan using pk_person_t on person_t pers_2 (cost=0.42..0.54 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=179)

  • Index Cond: (id = intervp_2.person_id)
  • Heap Fetches: 179
83. 0.716 0.716 ↑ 1.0 1 179

Index Scan using pk_person_natural_t on person_natural_t pnat_1 (cost=0.42..0.50 rows=1 width=25) (actual time=0.003..0.004 rows=1 loops=179)

  • Index Cond: (id = pers_2.id)
84. 0.245 1.136 ↓ 0.0 0 71

Nested Loop (cost=1.70..26.98 rows=1 width=32) (actual time=0.015..0.016 rows=0 loops=71)

  • Join Filter: (intervp_3.person_id = pleg_1.id)
85. 0.076 0.781 ↓ 0.0 0 71

Nested Loop (cost=1.28..25.30 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=71)

86. 0.038 0.639 ↓ 0.0 0 71

Nested Loop (cost=0.85..24.75 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=71)

87. 0.497 0.497 ↓ 0.0 0 71

Index Scan using intervenor_case_idx on intervenor_t interv_3 (cost=0.43..16.30 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=71)

  • Index Cond: (case_id = c.id)
  • Filter: (is_primary AND ((nature_type)::text = 'LEGAL'::text))
  • Rows Removed by Filter: 2
88. 0.104 0.104 ↑ 1.0 1 26

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

  • Index Cond: (id = interv_3.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 0
89. 0.066 0.066 ↑ 1.0 1 22

Index Only Scan using pk_person_t on person_t pers_3 (cost=0.42..0.54 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=22)

  • Index Cond: (id = intervp_3.person_id)
  • Heap Fetches: 22
90. 0.066 0.066 ↑ 1.0 1 22

Index Scan using pk_person_legal_t on person_legal_t pleg_1 (cost=0.42..0.47 rows=1 width=532) (actual time=0.003..0.003 rows=1 loops=22)

  • Index Cond: (id = pers_3.id)
91.          

SubPlan (for Nested Loop)

92. 0.044 0.044 ↑ 1.0 1 22

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

  • Filter: (id = pleg_1.juridical_form_id)
  • Rows Removed by Filter: 14
93. 0.500 75.000 ↓ 0.0 0 250

Nested Loop Left Join (cost=1.56..31.98 rows=1 width=32) (actual time=0.290..0.300 rows=0 loops=250)

94. 0.250 70.750 ↓ 0.0 0 250

Nested Loop Left Join (cost=1.13..25.12 rows=1 width=8) (actual time=0.273..0.283 rows=0 loops=250)

95. 0.612 67.000 ↓ 0.0 0 250

Nested Loop (cost=0.85..24.81 rows=1 width=8) (actual time=0.258..0.268 rows=0 loops=250)

96. 1.750 1.750 ↑ 1.0 1 250

Index Scan using intervenor_case_idx on intervenor_t i (cost=0.43..16.36 rows=1 width=8) (actual time=0.005..0.007 rows=1 loops=250)

  • Index Cond: (case_id = c.id)
  • Filter: ((((c.case_type)::text = ANY ('{URBANISM,GENERIC,ENVIRONMENT,ADMINISTRATIVE,SUBDIVISION,STATUTORY_PROVISIONING,GEN,PPAS,PAD}'::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: 2
97. 64.638 64.638 ↑ 1.0 1 171

Index Scan using pk_intervening_user_t on intervening_user_t iu (cost=0.42..8.45 rows=1 width=16) (actual time=0.366..0.378 rows=1 loops=171)

  • Index Cond: (id = i.id)
  • Filter: (on_behalf_of_authority_id = c.managing_authority_id)
  • Rows Removed by Filter: 0
98. 3.500 3.500 ↑ 1.0 1 125

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

  • Index Cond: (id = iu.user_id)
99. 3.750 3.750 ↑ 1.0 1 125

Index Scan using pk_person_natural_t on person_natural_t p_1 (cost=0.42..6.84 rows=1 width=25) (actual time=0.029..0.030 rows=1 loops=125)

  • Index Cond: (id = up.abstract_person_id)
100. 1.750 1,856.250 ↑ 1.0 1 250

Limit (cost=415.33..419.35 rows=1 width=516) (actual time=7.423..7.425 rows=1 loops=250)

101. 1.250 1,854.500 ↑ 1.0 1 250

Bitmap Heap Scan on reference_t ref_muni (cost=415.33..419.35 rows=1 width=516) (actual time=7.418..7.418 rows=1 loops=250)

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 38) AND (managing_authority_id = c.main_impacted_muni_id))
  • Heap Blocks: exact=216
102. 2.762 1,853.250 ↓ 0.0 0 250

BitmapAnd (cost=415.33..415.33 rows=1 width=0) (actual time=7.413..7.413 rows=0 loops=250)

103. 70.000 70.000 ↑ 110.0 1 250

Bitmap Index Scan on reference_case_idx (cost=0.00..5.53 rows=110 width=0) (actual time=0.280..0.280 rows=1 loops=250)

  • Index Cond: ((external_id = c.id) AND (reference_type_id = 38))
104. 1,780.488 1,780.488 ↓ 5.2 113,804 216

Bitmap Index Scan on reference_managing_authority_idx (cost=0.00..409.54 rows=22,015 width=0) (actual time=8.243..8.243 rows=113,804 loops=216)

  • Index Cond: (managing_authority_id = c.main_impacted_muni_id)
105. 1.500 4,183.250 ↓ 0.0 0 250

Limit (cost=415.33..419.35 rows=1 width=516) (actual time=16.733..16.733 rows=0 loops=250)

106. 2.750 4,181.750 ↓ 0.0 0 250

Bitmap Heap Scan on reference_t ref_region (cost=415.33..419.35 rows=1 width=516) (actual time=16.727..16.727 rows=0 loops=250)

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 1) AND (managing_authority_id = 20))
  • Heap Blocks: exact=63
107. 0.982 4,179.000 ↓ 0.0 0 250

BitmapAnd (cost=415.33..415.33 rows=1 width=0) (actual time=16.716..16.716 rows=0 loops=250)

108. 2.000 2.000 ↓ 0.0 0 250

Bitmap Index Scan on reference_case_idx (cost=0.00..5.53 rows=110 width=0) (actual time=0.008..0.008 rows=0 loops=250)

  • Index Cond: ((external_id = c.id) AND (reference_type_id = 1))
109. 4,176.018 4,176.018 ↓ 42.7 940,281 63

Bitmap Index Scan on reference_managing_authority_idx (cost=0.00..409.54 rows=22,015 width=0) (actual time=66.286..66.286 rows=940,281 loops=63)

  • Index Cond: (managing_authority_id = 20)
110. 0.750 222.500 ↓ 0.0 0 250

Limit (cost=415.33..419.35 rows=1 width=516) (actual time=0.890..0.890 rows=0 loops=250)

111. 0.500 221.750 ↓ 0.0 0 250

Bitmap Heap Scan on reference_t ref_ibge (cost=415.33..419.35 rows=1 width=516) (actual time=0.887..0.887 rows=0 loops=250)

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 28) AND (managing_authority_id = 21))
  • Heap Blocks: exact=9
112. 0.173 221.250 ↓ 0.0 0 250

BitmapAnd (cost=415.33..415.33 rows=1 width=0) (actual time=0.885..0.885 rows=0 loops=250)

113. 1.000 1.000 ↓ 0.0 0 250

Bitmap Index Scan on reference_case_idx (cost=0.00..5.53 rows=110 width=0) (actual time=0.004..0.004 rows=0 loops=250)

  • Index Cond: ((external_id = c.id) AND (reference_type_id = 28))
114. 220.077 220.077 ↓ 15.6 343,103 9

Bitmap Index Scan on reference_managing_authority_idx (cost=0.00..409.54 rows=22,015 width=0) (actual time=24.453..24.453 rows=343,103 loops=9)

  • Index Cond: (managing_authority_id = 21)
115. 0.500 1.250 ↓ 0.0 0 250

Limit (cost=415.33..419.35 rows=1 width=516) (actual time=0.005..0.005 rows=0 loops=250)

116. 0.250 0.750 ↓ 0.0 0 250

Bitmap Heap Scan on reference_t ref_spec (cost=415.33..419.35 rows=1 width=516) (actual time=0.003..0.003 rows=0 loops=250)

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 50) AND (managing_authority_id = 20))
117. 0.000 0.500 ↓ 0.0 0 250

BitmapAnd (cost=415.33..415.33 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=250)

118. 0.500 0.500 ↓ 0.0 0 250

Bitmap Index Scan on reference_case_idx (cost=0.00..5.53 rows=110 width=0) (actual time=0.002..0.002 rows=0 loops=250)

  • Index Cond: ((external_id = c.id) AND (reference_type_id = 50))
119. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on reference_managing_authority_idx (cost=0.00..409.54 rows=22,015 width=0) (never executed)

  • Index Cond: (managing_authority_id = 20)
120. 0.750 1,702.750 ↑ 1.0 1 250

Limit (cost=415.33..419.35 rows=1 width=516) (actual time=6.809..6.811 rows=1 loops=250)

121. 1.250 1,702.000 ↑ 1.0 1 250

Bitmap Heap Scan on reference_t ref_muni_1 (cost=415.33..419.35 rows=1 width=516) (actual time=6.808..6.808 rows=1 loops=250)

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 38) AND (managing_authority_id = c.main_impacted_muni_id))
  • Heap Blocks: exact=216
122. 2.274 1,700.750 ↓ 0.0 0 250

BitmapAnd (cost=415.33..415.33 rows=1 width=0) (actual time=6.803..6.803 rows=0 loops=250)

123. 0.500 0.500 ↑ 110.0 1 250

Bitmap Index Scan on reference_case_idx (cost=0.00..5.53 rows=110 width=0) (actual time=0.002..0.002 rows=1 loops=250)

  • Index Cond: ((external_id = c.id) AND (reference_type_id = 38))
124. 1,697.976 1,697.976 ↓ 5.2 113,804 216

Bitmap Index Scan on reference_managing_authority_idx (cost=0.00..409.54 rows=22,015 width=0) (actual time=7.861..7.861 rows=113,804 loops=216)

  • Index Cond: (managing_authority_id = c.main_impacted_muni_id)
125. 1.500 4,260.000 ↓ 0.0 0 250

Limit (cost=415.33..419.35 rows=1 width=516) (actual time=17.040..17.040 rows=0 loops=250)

126. 0.750 4,258.500 ↓ 0.0 0 250

Bitmap Heap Scan on reference_t ref_region_1 (cost=415.33..419.35 rows=1 width=516) (actual time=17.034..17.034 rows=0 loops=250)

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 1) AND (managing_authority_id = 20))
  • Heap Blocks: exact=63
127. 1.106 4,257.750 ↓ 0.0 0 250

BitmapAnd (cost=415.33..415.33 rows=1 width=0) (actual time=17.031..17.031 rows=0 loops=250)

128. 1.750 1.750 ↓ 0.0 0 250

Bitmap Index Scan on reference_case_idx (cost=0.00..5.53 rows=110 width=0) (actual time=0.007..0.007 rows=0 loops=250)

  • Index Cond: ((external_id = c.id) AND (reference_type_id = 1))
129. 4,254.894 4,254.894 ↓ 42.7 940,281 63

Bitmap Index Scan on reference_managing_authority_idx (cost=0.00..409.54 rows=22,015 width=0) (actual time=67.537..67.538 rows=940,281 loops=63)

  • Index Cond: (managing_authority_id = 20)
130. 0.750 202.000 ↓ 0.0 0 250

Limit (cost=415.33..419.35 rows=1 width=516) (actual time=0.808..0.808 rows=0 loops=250)

131. 0.500 201.250 ↓ 0.0 0 250

Bitmap Heap Scan on reference_t ref_ibge_1 (cost=415.33..419.35 rows=1 width=516) (actual time=0.805..0.805 rows=0 loops=250)

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 28) AND (managing_authority_id = 21))
  • Heap Blocks: exact=9
132. 0.121 200.750 ↓ 0.0 0 250

BitmapAnd (cost=415.33..415.33 rows=1 width=0) (actual time=0.803..0.803 rows=0 loops=250)

133. 1.000 1.000 ↓ 0.0 0 250

Bitmap Index Scan on reference_case_idx (cost=0.00..5.53 rows=110 width=0) (actual time=0.004..0.004 rows=0 loops=250)

  • Index Cond: ((external_id = c.id) AND (reference_type_id = 28))
134. 199.629 199.629 ↓ 15.6 343,103 9

Bitmap Index Scan on reference_managing_authority_idx (cost=0.00..409.54 rows=22,015 width=0) (actual time=22.181..22.181 rows=343,103 loops=9)

  • Index Cond: (managing_authority_id = 21)
135. 0.500 1.250 ↓ 0.0 0 250

Limit (cost=415.33..419.35 rows=1 width=516) (actual time=0.005..0.005 rows=0 loops=250)

136. 0.250 0.750 ↓ 0.0 0 250

Bitmap Heap Scan on reference_t ref_spec_1 (cost=415.33..419.35 rows=1 width=516) (actual time=0.003..0.003 rows=0 loops=250)

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 50) AND (managing_authority_id = 20))
137. 0.000 0.500 ↓ 0.0 0 250

BitmapAnd (cost=415.33..415.33 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=250)

138. 0.500 0.500 ↓ 0.0 0 250

Bitmap Index Scan on reference_case_idx (cost=0.00..5.53 rows=110 width=0) (actual time=0.002..0.002 rows=0 loops=250)

  • Index Cond: ((external_id = c.id) AND (reference_type_id = 50))
139. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on reference_managing_authority_idx (cost=0.00..409.54 rows=22,015 width=0) (never executed)

  • Index Cond: (managing_authority_id = 20)