explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aUhC

Settings
# exclusive inclusive rows x rows loops node
1. 0.097 25,116.753 ↑ 1.0 250 1

Limit (cost=5.43..937,936.28 rows=250 width=7,138) (actual time=1,765.450..25,116.753 rows=250 loops=1)

2. 309.088 25,116.656 ↑ 26.1 250 1

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

3. 5.869 3,174.992 ↑ 26.1 250 1

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

  • Join Filter: (termdic.id = term.term_dictionary_id)
  • Rows Removed by Join Filter: 58959
4. 15.053 3,165.873 ↑ 26.1 250 1

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

  • Join Filter: (term.id = tfc.term_id)
  • Rows Removed by Join Filter: 205709
5. 6.658 3,139.070 ↑ 26.1 250 1

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

  • Join Filter: (pdt.id = p.definition_id)
  • Rows Removed by Join Filter: 74119
6. 0.609 3,127.912 ↑ 26.1 250 1

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

7. 5.009 3,102.803 ↑ 26.1 250 1

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

  • Join Filter: (c.version_id = vers.id)
  • Rows Removed by Join Filter: 46210
8. 1.205 3,095.044 ↑ 26.1 250 1

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

  • Join Filter: (c.managing_authority_id = a.id)
  • Rows Removed by Join Filter: 6737
9. 0.858 3,092.589 ↑ 26.1 250 1

Nested Loop Left Join (cost=4.57..1,206,665.35 rows=6,526 width=1,548) (actual time=1.421..3,092.589 rows=250 loops=1)

10. 0.710 2,983.481 ↑ 26.1 250 1

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

11. 0.575 2,946.021 ↑ 26.1 250 1

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

12. 0.598 2,887.696 ↑ 26.1 250 1

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

13. 0.608 2,861.598 ↑ 26.1 250 1

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

14. 0.606 2,770.990 ↑ 26.1 250 1

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

15. 0.910 2,770.134 ↑ 26.1 250 1

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

16. 0.912 2,659.974 ↑ 26.1 250 1

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

17. 1.159 2,565.210 ↑ 22.0 297 1

Nested Loop Left Join (cost=1.28..1,176,884.47 rows=6,526 width=287) (actual time=1.382..2,565.210 rows=297 loops=1)

18. 0.704 2,492.177 ↑ 22.0 297 1

Nested Loop (cost=0.85..1,173,712.37 rows=6,526 width=273) (actual time=1.377..2,492.177 rows=297 loops=1)

19. 0.000 2,337.705 ↑ 22.0 298 1

Nested Loop (cost=0.43..1,169,548.95 rows=6,546 width=78) (actual time=1.371..2,337.705 rows=298 loops=1)

20. 38.623 38.623 ↑ 2.5 290,564 1

Seq Scan on geolocation_t g (cost=0.00..15,049.85 rows=736,485 width=24) (actual time=0.008..38.623 rows=290,564 loops=1)

21. 2,324.512 2,324.512 ↓ 0.0 0 290,564

Index Scan using pk_address_t on address_t ad (cost=0.43..1.56 rows=1 width=70) (actual time=0.008..0.008 rows=0 loops=290,564)

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

Index Scan using pk_case_t on case_t c (cost=0.42..0.63 rows=1 width=203) (actual time=0.514..0.516 rows=1 loops=298)

  • Index Cond: (id = g.case_id)
  • Filter: ((NOT deleted) AND (NOT a_new_version_exists))
  • Rows Removed by Filter: 0
23. 71.874 71.874 ↑ 1.0 1 297

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.242..0.242 rows=1 loops=297)

  • Index Cond: (c.id = case_id)
24. 93.852 93.852 ↑ 1.0 1 297

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

  • Index Cond: (id = c.configuration_id)
25. 109.250 109.250 ↑ 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.436..0.437 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. 90.000 90.000 ↑ 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.349..0.360 rows=1 loops=250)

  • Index Cond: (g.id = geolocation_id)
  • Filter: ((context_type)::text = 'TARGET'::text)
  • Rows Removed by Filter: 2
28. 25.500 25.500 ↓ 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.102..0.102 rows=0 loops=250)

  • Index Cond: (c.id = id)
29. 57.750 57.750 ↑ 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.230..0.231 rows=1 loops=250)

  • Index Cond: (c.id = id)
30. 36.750 36.750 ↑ 1.0 1 250

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

  • Index Cond: (prt.delivery_id = id)
31. 108.250 108.250 ↑ 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.432..0.433 rows=1 loops=250)

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

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

33. 0.458 0.458 ↑ 1.0 28 1

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

34. 2.435 2.750 ↑ 1.0 186 250

Materialize (cost=0.00..8.79 rows=186 width=106) (actual time=0.000..0.011 rows=186 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. 24.500 24.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.094..0.098 rows=0 loops=250)

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

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

38. 0.742 0.742 ↑ 1.0 298 1

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

39. 10.153 11.750 ↑ 1.0 823 250

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

40. 1.597 1.597 ↑ 1.0 823 1

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

41. 2.975 3.250 ↑ 1.0 236 250

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

42. 0.275 0.275 ↑ 1.0 236 1

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

43. 29.500 29.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.118..0.118 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. 303.888 702.476 ↓ 5.7 353,856 1

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

  • Hash Cond: (intervp2_1.id = interv2_1.id)
49. 129.666 129.666 ↑ 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.010..129.666 rows=590,719 loops=1)

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

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

  • Buckets: 131072 (originally 131072) Batches: 8 (originally 4) Memory Usage: 3330kB
51. 127.069 199.820 ↓ 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=74.237..199.820 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. 72.751 72.751 ↑ 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=72.751..72.751 rows=404,512 loops=1)

  • Index Cond: ((nature_type)::text = 'NATURAL'::text)
53. 0.975 159.375 ↑ 1.0 1 75

Nested Loop (cost=1.70..25.82 rows=1 width=32) (actual time=2.114..2.125 rows=1 loops=75)

  • Join Filter: (intervp.person_id = pnat.id)
54. 0.225 128.175 ↑ 1.0 1 75

Nested Loop (cost=1.28..25.30 rows=1 width=16) (actual time=1.701..1.709 rows=1 loops=75)

55. 0.258 98.625 ↑ 1.0 1 75

Nested Loop (cost=0.85..24.75 rows=1 width=8) (actual time=1.308..1.315 rows=1 loops=75)

56. 83.925 83.925 ↑ 1.0 1 75

Index Scan using intervenor_case_idx on intervenor_t interv (cost=0.43..16.30 rows=1 width=8) (actual time=1.112..1.119 rows=1 loops=75)

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

Index Scan using pk_intervening_person_t on intervening_person_t intervp (cost=0.42..8.45 rows=1 width=16) (actual time=0.173..0.174 rows=1 loops=83)

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

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

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

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

  • Index Cond: (id = pers.id)
60. 1.575 208.425 ↓ 0.0 0 175

Nested Loop (cost=1.70..26.98 rows=1 width=32) (actual time=1.158..1.191 rows=0 loops=175)

  • Join Filter: (intervp_1.person_id = pleg.id)
61. 0.317 178.150 ↓ 0.0 0 175

Nested Loop (cost=1.28..25.30 rows=1 width=16) (actual time=0.985..1.018 rows=0 loops=175)

62. 0.497 149.625 ↓ 0.0 0 175

Nested Loop (cost=0.85..24.75 rows=1 width=8) (actual time=0.822..0.855 rows=0 loops=175)

63. 135.100 135.100 ↓ 0.0 0 175

Index Scan using intervenor_case_idx on intervenor_t interv_1 (cost=0.43..16.30 rows=1 width=8) (actual time=0.745..0.772 rows=0 loops=175)

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

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.155..0.167 rows=1 loops=84)

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

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

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

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

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

SubPlan (for Nested Loop)

68. 0.410 0.410 ↑ 1.0 1 82

Seq Scan on juridical_form_t (cost=0.00..1.19 rows=1 width=516) (actual time=0.004..0.005 rows=1 loops=82)

  • 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. 293.191 607.050 ↓ 5.7 353,856 1

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

  • Hash Cond: (intervp2_3.id = interv2_3.id)
73. 130.153 130.153 ↑ 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.008..130.153 rows=590,719 loops=1)

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

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

  • Buckets: 131072 (originally 131072) Batches: 8 (originally 4) Memory Usage: 3330kB
75. 71.917 109.073 ↓ 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=38.181..109.073 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. 37.156 37.156 ↑ 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=37.156..37.156 rows=404,512 loops=1)

  • Index Cond: ((nature_type)::text = 'NATURAL'::text)
77. 0.375 2.250 ↑ 1.0 1 75

Nested Loop (cost=1.70..25.82 rows=1 width=32) (actual time=0.028..0.030 rows=1 loops=75)

  • Join Filter: (intervp_2.person_id = pnat_1.id)
78. 0.150 1.650 ↑ 1.0 1 75

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

79. 0.193 1.200 ↑ 1.0 1 75

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

80. 0.675 0.675 ↑ 1.0 1 75

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

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

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.004..0.004 rows=1 loops=83)

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

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=75)

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

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.003 rows=1 loops=75)

  • Index Cond: (id = pers_2.id)
84. 0.733 3.500 ↓ 0.0 0 175

Nested Loop (cost=1.70..26.98 rows=1 width=32) (actual time=0.019..0.020 rows=0 loops=175)

  • Join Filter: (intervp_3.person_id = pleg_1.id)
85. 0.197 2.275 ↓ 0.0 0 175

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

86. 0.189 1.750 ↓ 0.0 0 175

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

87. 1.225 1.225 ↓ 0.0 0 175

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=175)

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

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=84)

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

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.004 rows=1 loops=82)

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

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=82)

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

SubPlan (for Nested Loop)

92. 0.246 0.246 ↑ 1.0 1 82

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

  • Filter: (id = pleg_1.juridical_form_id)
  • Rows Removed by Filter: 14
93. 0.986 111.500 ↑ 1.0 1 250

Nested Loop Left Join (cost=1.56..31.98 rows=1 width=32) (actual time=0.429..0.446 rows=1 loops=250)

94. 0.562 103.000 ↑ 1.0 1 250

Nested Loop Left Join (cost=1.13..25.12 rows=1 width=8) (actual time=0.396..0.412 rows=1 loops=250)

95. 0.530 96.250 ↑ 1.0 1 250

Nested Loop (cost=0.85..24.81 rows=1 width=8) (actual time=0.369..0.385 rows=1 loops=250)

96. 2.000 2.000 ↑ 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.008 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. 93.720 93.720 ↑ 1.0 1 264

Index Scan using pk_intervening_user_t on intervening_user_t iu (cost=0.42..8.45 rows=1 width=16) (actual time=0.352..0.355 rows=1 loops=264)

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

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=221)

  • Index Cond: (id = iu.user_id)
99. 7.514 7.514 ↑ 1.0 1 221

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.034..0.034 rows=1 loops=221)

  • Index Cond: (id = up.abstract_person_id)
100. 1.500 2,060.250 ↑ 1.0 1 250

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

101. 1.750 2,058.750 ↑ 1.0 1 250

Bitmap Heap Scan on reference_t ref_muni (cost=415.33..419.35 rows=1 width=516) (actual time=8.235..8.235 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=212
102. 2.628 2,057.000 ↓ 0.0 0 250

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

103. 55.000 55.000 ↑ 110.0 1 250

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

  • Index Cond: ((external_id = c.id) AND (reference_type_id = 38))
104. 1,999.372 1,999.372 ↓ 6.0 132,109 212

Bitmap Index Scan on reference_managing_authority_idx (cost=0.00..409.54 rows=22,015 width=0) (actual time=9.431..9.431 rows=132,109 loops=212)

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

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

106. 14.000 6,877.250 ↓ 0.0 0 250

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

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 1) AND (managing_authority_id = 20))
  • Heap Blocks: exact=108
107. 1.596 6,863.250 ↓ 0.0 0 250

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

108. 2.250 2.250 ↓ 0.0 0 250

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

  • Index Cond: ((external_id = c.id) AND (reference_type_id = 1))
109. 6,859.404 6,859.404 ↓ 42.7 940,281 108

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

  • Index Cond: (managing_authority_id = 20)
110. 1.250 1,070.500 ↓ 0.0 0 250

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

111. 0.500 1,069.250 ↓ 0.0 0 250

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

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 28) AND (managing_authority_id = 21))
  • Heap Blocks: exact=54
112. 0.676 1,068.750 ↓ 0.0 0 250

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

113. 1.250 1.250 ↓ 0.0 0 250

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

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

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

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

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

116. 0.250 1.000 ↓ 0.0 0 250

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

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

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

118. 0.750 0.750 ↓ 0.0 0 250

Bitmap Index Scan on reference_case_idx (cost=0.00..5.53 rows=110 width=0) (actual time=0.003..0.003 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,923.000 ↑ 1.0 1 250

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

121. 0.750 1,922.250 ↑ 1.0 1 250

Bitmap Heap Scan on reference_t ref_muni_1 (cost=415.33..419.35 rows=1 width=516) (actual time=7.689..7.689 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=212
122. 2.188 1,921.500 ↓ 0.0 0 250

BitmapAnd (cost=415.33..415.33 rows=1 width=0) (actual time=7.686..7.686 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,918.812 1,918.812 ↓ 6.0 132,109 212

Bitmap Index Scan on reference_managing_authority_idx (cost=0.00..409.54 rows=22,015 width=0) (actual time=9.051..9.051 rows=132,109 loops=212)

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

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

126. 1.000 6,858.750 ↓ 0.0 0 250

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

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 1) AND (managing_authority_id = 20))
  • Heap Blocks: exact=108
127. 1.456 6,857.750 ↓ 0.0 0 250

BitmapAnd (cost=415.33..415.33 rows=1 width=0) (actual time=27.431..27.431 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. 6,854.544 6,854.544 ↓ 42.7 940,281 108

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

  • Index Cond: (managing_authority_id = 20)
130. 1.000 1,012.750 ↓ 0.0 0 250

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

131. 0.500 1,011.750 ↓ 0.0 0 250

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

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 28) AND (managing_authority_id = 21))
  • Heap Blocks: exact=54
132. 0.740 1,011.250 ↓ 0.0 0 250

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

133. 1.250 1.250 ↓ 0.0 0 250

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

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

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

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

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

136. 0.250 1.000 ↓ 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.004..0.004 rows=0 loops=250)

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

BitmapAnd (cost=415.33..415.33 rows=1 width=0) (actual time=0.003..0.003 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)