explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZtXd

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 3,726.314 ↑ 1.0 25 1

Limit (cost=7.00..11,196.21 rows=25 width=1,846) (actual time=3,634.707..3,726.314 rows=25 loops=1)

2. 273.295 3,726.306 ↑ 4,946.6 25 1

Nested Loop (cost=7.00..55,348,596.07 rows=123,665 width=1,846) (actual time=3,634.705..3,726.306 rows=25 loops=1)

3. 0.040 93.808 ↑ 4,946.6 25 1

Nested Loop Left Join (cost=7.00..11,900,365.45 rows=123,665 width=1,811) (actual time=5.589..93.808 rows=25 loops=1)

4. 0.032 93.718 ↑ 4,946.6 25 1

Nested Loop Left Join (cost=6.56..11,836,285.93 rows=123,665 width=1,811) (actual time=5.587..93.718 rows=25 loops=1)

5. 0.042 93.661 ↑ 4,946.6 25 1

Nested Loop Left Join (cost=6.42..11,810,746.40 rows=123,665 width=1,740) (actual time=5.586..93.661 rows=25 loops=1)

6. 0.013 93.594 ↑ 4,946.6 25 1

Nested Loop Left Join (cost=6.14..11,773,335.91 rows=123,665 width=1,740) (actual time=5.583..93.594 rows=25 loops=1)

7. 0.040 93.506 ↑ 4,946.6 25 1

Nested Loop Left Join (cost=5.71..11,663,550.13 rows=123,665 width=1,732) (actual time=5.582..93.506 rows=25 loops=1)

8. 0.077 93.416 ↑ 4,946.6 25 1

Nested Loop Left Join (cost=5.57..11,642,195.82 rows=123,665 width=1,634) (actual time=5.574..93.416 rows=25 loops=1)

  • Join Filter: (c.managing_authority_id = a.id)
  • Rows Removed by Join Filter: 675
9. 0.039 93.264 ↑ 4,946.6 25 1

Nested Loop Left Join (cost=5.57..11,590,255.17 rows=123,665 width=594) (actual time=5.554..93.264 rows=25 loops=1)

10. 0.041 93.125 ↑ 4,946.6 25 1

Nested Loop (cost=5.13..11,243,338.91 rows=123,665 width=561) (actual time=5.545..93.125 rows=25 loops=1)

11. 0.033 93.034 ↑ 4,946.6 25 1

Nested Loop (cost=4.99..11,221,695.95 rows=123,665 width=559) (actual time=5.539..93.034 rows=25 loops=1)

12. 0.044 92.851 ↑ 5,764.7 25 1

Nested Loop Left Join (cost=4.56..11,070,699.69 rows=144,117 width=519) (actual time=5.528..92.851 rows=25 loops=1)

13. 0.020 92.732 ↑ 5,764.7 25 1

Nested Loop Left Join (cost=4.13..11,001,664.45 rows=144,117 width=344) (actual time=5.526..92.732 rows=25 loops=1)

14. 0.039 92.587 ↑ 5,764.7 25 1

Nested Loop Left Join (cost=3.71..10,899,070.50 rows=144,117 width=314) (actual time=5.516..92.587 rows=25 loops=1)

15. 0.031 92.498 ↑ 5,764.7 25 1

Nested Loop Left Join (cost=3.42..10,846,676.91 rows=144,117 width=290) (actual time=5.508..92.498 rows=25 loops=1)

16. 0.046 92.342 ↑ 5,764.7 25 1

Nested Loop Left Join (cost=2.99..10,757,391.81 rows=144,117 width=298) (actual time=5.497..92.342 rows=25 loops=1)

17. 0.030 92.171 ↑ 5,764.7 25 1

Nested Loop Left Join (cost=2.56..10,623,127.30 rows=144,117 width=245) (actual time=5.483..92.171 rows=25 loops=1)

18. 0.031 92.041 ↑ 5,764.7 25 1

Nested Loop Left Join (cost=2.13..10,527,758.66 rows=144,117 width=229) (actual time=5.472..92.041 rows=25 loops=1)

19. 0.028 92.010 ↑ 5,764.7 25 1

Nested Loop (cost=1.70..10,442,057.76 rows=144,117 width=229) (actual time=5.470..92.010 rows=25 loops=1)

20. 0.016 91.857 ↑ 5,764.7 25 1

Nested Loop Left Join (cost=1.27..10,356,114.19 rows=144,117 width=221) (actual time=5.457..91.857 rows=25 loops=1)

21. 0.030 91.716 ↑ 5,764.7 25 1

Nested Loop (cost=0.85..10,266,808.33 rows=144,117 width=211) (actual time=5.445..91.716 rows=25 loops=1)

22. 89.417 91.561 ↑ 6,267.9 25 1

Index Scan using case_unique_business_id_idx on case_t c (cost=0.42..10,157,854.93 rows=156,697 width=203) (actual time=5.435..91.561 rows=25 loops=1)

  • Index Cond: ((case_type)::text = 'URBANISM'::text)
  • Filter: ((NOT a_new_version_exists) AND ((reception_date IS NULL) OR ((reception_date > '2010-01-01 00:00:00'::timestamp without time zone) AND (reception_date < '2020-01-10 00:00:00'::timestamp without time zone))) AND (upper(((SubPlan 20))::text) ~~ '%P%'::text))
  • Rows Removed by Filter: 168,687
23.          

SubPlan (for Index Scan)

24. 0.000 2.144 ↑ 1.0 1 536

Limit (cost=0.43..22.62 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=536)

25. 2.144 2.144 ↑ 1.0 1 536

Index Scan using reference_case_idx on reference_t ref_region_2 (cost=0.43..22.62 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=536)

  • Index Cond: (external_id = c.id)
  • Filter: ((managing_authority_id = 20) AND (reference_type_id = 1))
  • Rows Removed by Filter: 2
26. 0.125 0.125 ↑ 1.0 1 25

Index Scan using pk_configuration_t on configuration_t config (cost=0.42..0.69 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=25)

  • Index Cond: (id = c.configuration_id)
27. 0.125 0.125 ↑ 1.0 1 25

Index Scan using case_status_board_to_case_idx on case_status_board_t board (cost=0.42..0.61 rows=1 width=18) (actual time=0.004..0.005 rows=1 loops=25)

  • Index Cond: (c.id = case_id)
28. 0.125 0.125 ↑ 1.0 1 25

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

  • Index Cond: (id = config.current_phase_id)
29. 0.000 0.000 ↓ 0.0 0 25

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

  • Index Cond: (p.deadline_id = id)
30. 0.100 0.100 ↑ 1.0 1 25

Index Scan using geolocation__case_idx on geolocation_t g (cost=0.42..0.65 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=25)

  • Index Cond: (case_id = c.id)
31. 0.125 0.125 ↑ 1.0 1 25

Index Scan using pk_address_t on address_t ad (cost=0.43..0.92 rows=1 width=69) (actual time=0.005..0.005 rows=1 loops=25)

  • Index Cond: (id = g.target_principal_address_id)
32. 0.125 0.125 ↓ 0.0 0 25

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.005..0.005 rows=0 loops=25)

  • Index Cond: (g.id = geolocation_id)
  • Filter: ((context_type)::text = 'TARGET'::text)
  • Rows Removed by Filter: 1
33. 0.050 0.050 ↓ 0.0 0 25

Index Scan using pk_environment_permit_request_t on environment_permit_request_t eprt (cost=0.29..0.35 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=25)

  • Index Cond: (c.id = id)
34. 0.125 0.125 ↑ 1.0 1 25

Index Scan using pk_permit_request_t on permit_request_t prt (cost=0.42..0.70 rows=1 width=38) (actual time=0.004..0.005 rows=1 loops=25)

  • Index Cond: (c.id = id)
35. 0.075 0.075 ↑ 1.0 1 25

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

  • Index Cond: (prt.delivery_id = id)
36. 0.150 0.150 ↑ 1.0 1 25

Index Scan using case_actor_authority_case_idx on case_actor_authority_t actors1_ (cost=0.43..1.04 rows=1 width=40) (actual time=0.005..0.006 rows=1 loops=25)

  • Index Cond: (case_id = c.id)
  • Filter: (authority_abstract_id = 20)
  • Rows Removed by Filter: 2
37. 0.050 0.050 ↑ 1.0 1 25

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

  • Index Cond: (id = p.definition_id)
38. 0.100 0.100 ↑ 1.0 1 25

Index Scan using pk_reference_t on reference_t r (cost=0.43..2.80 rows=1 width=49) (actual time=0.004..0.004 rows=1 loops=25)

  • Index Cond: (c.main_reference_id = id)
39. 0.067 0.075 ↑ 1.0 28 25

Materialize (cost=0.00..1.42 rows=28 width=1,040) (actual time=0.000..0.003 rows=28 loops=25)

40. 0.008 0.008 ↑ 1.0 28 1

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

41. 0.050 0.050 ↑ 1.0 1 25

Index Scan using cobat_version_t_pkey on version_t vers (cost=0.14..0.16 rows=1 width=106) (actual time=0.002..0.002 rows=1 loops=25)

  • Index Cond: (c.version_id = id)
42. 0.075 0.075 ↓ 0.0 0 25

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.002..0.003 rows=0 loops=25)

  • Index Cond: (id = board.nearest_term_date_id)
43. 0.025 0.025 ↓ 0.0 0 25

Index Scan using term_t_pkey on term_t term (cost=0.28..0.29 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=25)

  • Index Cond: (id = tfc.term_id)
44. 0.025 0.025 ↓ 0.0 0 25

Index Scan using term_dictionary_t_pkey on term_dictionary_t termdic (cost=0.14..0.20 rows=1 width=87) (actual time=0.001..0.001 rows=0 loops=25)

  • Index Cond: (id = term.term_dictionary_id)
45. 0.050 0.050 ↓ 0.0 0 25

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

  • Index Cond: (id = tfc.deadline_id)
46. 0.013 0.025 ↑ 1.0 1 25

Materialize (cost=0.00..1.85 rows=1 width=35) (actual time=0.000..0.001 rows=1 loops=25)

47. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on authority_abstract_t abstractau2_ (cost=0.00..1.85 rows=1 width=35) (actual time=0.006..0.012 rows=1 loops=1)

  • Filter: (id = 20)
  • Rows Removed by Filter: 67
48.          

SubPlan (for Nested Loop)

49. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.70..25.65 rows=1 width=0) (never executed)

  • Join Filter: (intervp.person_id = pnat.id)
50. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.28..25.16 rows=1 width=16) (never executed)

51. 0.000 0.000 ↓ 0.0 0

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

52. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
53. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = interv.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
54. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_person_t on person_t pers (cost=0.42..0.53 rows=1 width=8) (never executed)

  • Index Cond: (id = intervp.person_id)
  • Heap Fetches: 0
55. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_person_natural_t on person_natural_t pnat (cost=0.42..0.47 rows=1 width=8) (never executed)

  • Index Cond: (id = pers.id)
  • Heap Fetches: 0
56. 279.666 1,717.520 ↓ 1.5 353,601 1

Hash Join (cost=78,928.66..129,489.56 rows=229,105 width=8) (actual time=1,187.102..1,717.520 rows=353,601 loops=1)

  • Hash Cond: (interv_1.id = intervp_1.id)
57. 252.466 252.466 ↑ 1.0 787,827 1

Seq Scan on intervenor_t interv_1 (cost=0.00..45,313.39 rows=788,389 width=16) (actual time=0.008..252.466 rows=787,827 loops=1)

  • Filter: is_primary
  • Rows Removed by Filter: 975,912
58. 84.020 1,185.388 ↑ 1.4 356,795 1

Hash (cost=72,521.90..72,521.90 rows=512,541 width=8) (actual time=1,185.388..1,185.388 rows=356,795 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 18,034kB
59. 233.348 1,101.368 ↑ 1.4 356,795 1

Hash Join (cost=50,404.12..72,521.90 rows=512,541 width=8) (actual time=748.058..1,101.368 rows=356,795 loops=1)

  • Hash Cond: (intervp_1.person_id = pers_1.id)
60. 121.503 121.503 ↑ 1.0 589,964 1

Seq Scan on intervening_person_t intervp_1 (cost=0.00..16,106.96 rows=589,964 width=16) (actual time=0.013..121.503 rows=589,964 loops=1)

  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 117,953
61. 131.725 746.517 ↑ 1.0 515,177 1

Hash (cost=43,964.41..43,964.41 rows=515,177 width=16) (actual time=746.517..746.517 rows=515,177 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 28,245kB
62. 350.640 614.792 ↑ 1.0 515,177 1

Hash Join (cost=16,987.48..43,964.41 rows=515,177 width=16) (actual time=167.804..614.792 rows=515,177 loops=1)

  • Hash Cond: (pers_1.id = pnat_1.id)
63. 97.881 97.881 ↑ 1.0 800,157 1

Seq Scan on person_t pers_1 (cost=0.00..18,824.57 rows=800,157 width=8) (actual time=0.006..97.881 rows=800,157 loops=1)

64. 96.382 166.271 ↑ 1.0 515,177 1

Hash (cost=10,547.77..10,547.77 rows=515,177 width=8) (actual time=166.271..166.271 rows=515,177 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,221kB
65. 69.889 69.889 ↑ 1.0 515,177 1

Seq Scan on person_natural_t pnat_1 (cost=0.00..10,547.77 rows=515,177 width=8) (actual time=0.007..69.889 rows=515,177 loops=1)

66. 0.017 0.060 ↑ 1.0 1 1

Nested Loop (cost=1.70..25.66 rows=1 width=17) (actual time=0.058..0.060 rows=1 loops=1)

  • Join Filter: (intervp_2.person_id = pnat_2.id)
67. 0.005 0.035 ↑ 1.0 1 1

Nested Loop (cost=1.28..25.16 rows=1 width=16) (actual time=0.033..0.035 rows=1 loops=1)

68. 0.003 0.025 ↑ 1.0 1 1

Nested Loop (cost=0.85..24.62 rows=1 width=8) (actual time=0.024..0.025 rows=1 loops=1)

69. 0.011 0.011 ↑ 1.0 1 1

Index Scan using intervenor_case_idx on intervenor_t interv_2 (cost=0.43..16.17 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 1
70. 0.011 0.011 ↑ 1.0 1 1

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.011..0.011 rows=1 loops=1)

  • Index Cond: (id = interv_2.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
71. 0.005 0.005 ↑ 1.0 1 1

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

  • Index Cond: (id = intervp_2.person_id)
  • Heap Fetches: 1
72. 0.008 0.008 ↑ 1.0 1 1

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

  • Index Cond: (id = pers_2.id)
73. 0.168 0.816 ↑ 1.0 1 24

Nested Loop (cost=1.70..26.84 rows=1 width=25) (actual time=0.032..0.034 rows=1 loops=24)

  • Join Filter: (intervp_3.person_id = pleg.id)
74. 0.048 0.480 ↑ 1.0 1 24

Nested Loop (cost=1.28..25.16 rows=1 width=16) (actual time=0.019..0.020 rows=1 loops=24)

75. 0.043 0.312 ↑ 1.0 1 24

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

76. 0.144 0.144 ↑ 1.0 1 24

Index Scan using intervenor_case_idx on intervenor_t interv_3 (cost=0.43..16.17 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=24)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 1
77. 0.125 0.125 ↑ 1.0 1 25

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.005..0.005 rows=1 loops=25)

  • Index Cond: (id = interv_3.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
78. 0.120 0.120 ↑ 1.0 1 24

Index Only Scan using pk_person_t on person_t pers_3 (cost=0.42..0.53 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=24)

  • Index Cond: (id = intervp_3.person_id)
  • Heap Fetches: 24
79. 0.096 0.096 ↑ 1.0 1 24

Index Scan using pk_person_legal_t on person_legal_t pleg (cost=0.42..0.47 rows=1 width=33) (actual time=0.004..0.004 rows=1 loops=24)

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

SubPlan (for Nested Loop)

81. 0.072 0.072 ↓ 0.0 0 24

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

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

Nested Loop (cost=1.70..25.65 rows=1 width=0) (never executed)

  • Join Filter: (intervp_4.person_id = pnat_3.id)
83. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.28..25.16 rows=1 width=16) (never executed)

84. 0.000 0.000 ↓ 0.0 0

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

85. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
86. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = interv_4.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
87. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_person_t on person_t pers_4 (cost=0.42..0.53 rows=1 width=8) (never executed)

  • Index Cond: (id = intervp_4.person_id)
  • Heap Fetches: 0
88. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_person_natural_t on person_natural_t pnat_3 (cost=0.42..0.47 rows=1 width=8) (never executed)

  • Index Cond: (id = pers_4.id)
  • Heap Fetches: 0
89. 262.385 1,638.145 ↓ 1.5 353,601 1

Hash Join (cost=78,928.66..129,489.56 rows=229,105 width=8) (actual time=1,140.831..1,638.145 rows=353,601 loops=1)

  • Hash Cond: (interv_5.id = intervp_5.id)
90. 236.480 236.480 ↑ 1.0 787,827 1

Seq Scan on intervenor_t interv_5 (cost=0.00..45,313.39 rows=788,389 width=16) (actual time=0.004..236.480 rows=787,827 loops=1)

  • Filter: is_primary
  • Rows Removed by Filter: 975,912
91. 81.772 1,139.280 ↑ 1.4 356,795 1

Hash (cost=72,521.90..72,521.90 rows=512,541 width=8) (actual time=1,139.280..1,139.280 rows=356,795 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 18,034kB
92. 227.548 1,057.508 ↑ 1.4 356,795 1

Hash Join (cost=50,404.12..72,521.90 rows=512,541 width=8) (actual time=713.253..1,057.508 rows=356,795 loops=1)

  • Hash Cond: (intervp_5.person_id = pers_5.id)
93. 118.200 118.200 ↑ 1.0 589,964 1

Seq Scan on intervening_person_t intervp_5 (cost=0.00..16,106.96 rows=589,964 width=16) (actual time=0.014..118.200 rows=589,964 loops=1)

  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 117,953
94. 124.989 711.760 ↑ 1.0 515,177 1

Hash (cost=43,964.41..43,964.41 rows=515,177 width=16) (actual time=711.760..711.760 rows=515,177 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 28,245kB
95. 332.571 586.771 ↑ 1.0 515,177 1

Hash Join (cost=16,987.48..43,964.41 rows=515,177 width=16) (actual time=166.676..586.771 rows=515,177 loops=1)

  • Hash Cond: (pers_5.id = pnat_4.id)
96. 89.015 89.015 ↑ 1.0 800,157 1

Seq Scan on person_t pers_5 (cost=0.00..18,824.57 rows=800,157 width=8) (actual time=0.006..89.015 rows=800,157 loops=1)

97. 96.358 165.185 ↑ 1.0 515,177 1

Hash (cost=10,547.77..10,547.77 rows=515,177 width=8) (actual time=165.185..165.185 rows=515,177 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,221kB
98. 68.827 68.827 ↑ 1.0 515,177 1

Seq Scan on person_natural_t pnat_4 (cost=0.00..10,547.77 rows=515,177 width=8) (actual time=0.008..68.827 rows=515,177 loops=1)

99. 0.010 0.032 ↑ 1.0 1 1

Nested Loop (cost=1.70..25.66 rows=1 width=17) (actual time=0.031..0.032 rows=1 loops=1)

  • Join Filter: (intervp_6.person_id = pnat_5.id)
100. 0.002 0.019 ↑ 1.0 1 1

Nested Loop (cost=1.28..25.16 rows=1 width=16) (actual time=0.018..0.019 rows=1 loops=1)

101. 0.003 0.013 ↑ 1.0 1 1

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

102. 0.004 0.004 ↑ 1.0 1 1

Index Scan using intervenor_case_idx on intervenor_t interv_6 (cost=0.43..16.17 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 1
103. 0.006 0.006 ↑ 1.0 1 1

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

  • Index Cond: (id = interv_6.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
104. 0.004 0.004 ↑ 1.0 1 1

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

  • Index Cond: (id = intervp_6.person_id)
  • Heap Fetches: 1
105. 0.003 0.003 ↑ 1.0 1 1

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

  • Index Cond: (id = pers_6.id)
106. 0.096 0.480 ↑ 1.0 1 24

Nested Loop (cost=1.70..26.84 rows=1 width=25) (actual time=0.019..0.020 rows=1 loops=24)

  • Join Filter: (intervp_7.person_id = pleg_1.id)
107. 0.048 0.288 ↑ 1.0 1 24

Nested Loop (cost=1.28..25.16 rows=1 width=16) (actual time=0.011..0.012 rows=1 loops=24)

108. 0.021 0.192 ↑ 1.0 1 24

Nested Loop (cost=0.85..24.62 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=24)

109. 0.096 0.096 ↑ 1.0 1 24

Index Scan using intervenor_case_idx on intervenor_t interv_7 (cost=0.43..16.17 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=24)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 1
110. 0.075 0.075 ↑ 1.0 1 25

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

  • Index Cond: (id = interv_7.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
111. 0.048 0.048 ↑ 1.0 1 24

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

  • Index Cond: (id = intervp_7.person_id)
  • Heap Fetches: 24
112. 0.048 0.048 ↑ 1.0 1 24

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

  • Index Cond: (id = pers_7.id)
113.          

SubPlan (for Nested Loop)

114. 0.048 0.048 ↓ 0.0 0 24

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=0 loops=24)

  • Filter: (id = pleg_1.juridical_form_id)
  • Rows Removed by Filter: 15
115. 0.050 0.525 ↑ 1.0 1 25

Nested Loop Left Join (cost=1.56..30.99 rows=1 width=17) (actual time=0.018..0.021 rows=1 loops=25)

116. 0.025 0.375 ↑ 1.0 1 25

Nested Loop Left Join (cost=1.13..25.00 rows=1 width=8) (actual time=0.012..0.015 rows=1 loops=25)

117. 0.038 0.275 ↑ 1.0 1 25

Nested Loop (cost=0.85..24.69 rows=1 width=8) (actual time=0.008..0.011 rows=1 loops=25)

118. 0.125 0.125 ↑ 1.0 1 25

Index Scan using intervenor_case_idx on intervenor_t i (cost=0.43..16.24 rows=1 width=8) (actual time=0.003..0.005 rows=1 loops=25)

  • 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: 1
119. 0.112 0.112 ↑ 1.0 1 28

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=1 loops=28)

  • Index Cond: (id = i.id)
  • Filter: (on_behalf_of_authority_id = c.managing_authority_id)
  • Rows Removed by Filter: 0
120. 0.075 0.075 ↑ 1.0 1 25

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

  • Index Cond: (id = iu.user_id)
121. 0.100 0.100 ↑ 1.0 1 25

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

  • Index Cond: (id = up.abstract_person_id)
122. 0.025 0.400 ↓ 0.0 0 25

Limit (cost=0.43..22.62 rows=1 width=16) (actual time=0.016..0.016 rows=0 loops=25)

123. 0.375 0.375 ↓ 0.0 0 25

Index Scan using reference_case_idx on reference_t ref_muni (cost=0.43..22.62 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=25)

  • Index Cond: (external_id = c.id)
  • Filter: ((managing_authority_id = c.main_impacted_muni_id) AND (reference_type_id = 38))
  • Rows Removed by Filter: 10
124. 0.025 0.100 ↑ 1.0 1 25

Limit (cost=0.43..22.62 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=25)

125. 0.075 0.075 ↑ 1.0 1 25

Index Scan using reference_case_idx on reference_t ref_region (cost=0.43..22.62 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=25)

  • Index Cond: (external_id = c.id)
  • Filter: ((managing_authority_id = 20) AND (reference_type_id = 1))
  • Rows Removed by Filter: 1
126. 0.000 0.125 ↓ 0.0 0 25

Limit (cost=0.43..22.62 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=25)

127. 0.125 0.125 ↓ 0.0 0 25

Index Scan using reference_case_idx on reference_t ref_ibge (cost=0.43..22.62 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=25)

  • Index Cond: (external_id = c.id)
  • Filter: ((managing_authority_id = 21) AND (reference_type_id = 28))
  • Rows Removed by Filter: 12
128. 0.050 0.400 ↓ 0.0 0 25

Limit (cost=10.14..14.15 rows=1 width=16) (actual time=0.016..0.016 rows=0 loops=25)

129. 0.025 0.350 ↓ 0.0 0 25

Bitmap Heap Scan on reference_t ref_spec (cost=10.14..14.15 rows=1 width=16) (actual time=0.014..0.014 rows=0 loops=25)

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 50))
  • Filter: (managing_authority_id = 20)
130. 0.025 0.325 ↓ 0.0 0 25

BitmapAnd (cost=10.14..10.14 rows=1 width=0) (actual time=0.013..0.013 rows=0 loops=25)

131. 0.075 0.075 ↓ 1.7 12 25

Bitmap Index Scan on reference_case_idx (cost=0.00..4.49 rows=7 width=0) (actual time=0.003..0.003 rows=12 loops=25)

  • Index Cond: (external_id = c.id)
132. 0.225 0.225 ↑ 3.0 43 25

Bitmap Index Scan on reference_type_idx (cost=0.00..5.40 rows=129 width=0) (actual time=0.009..0.009 rows=43 loops=25)

  • Index Cond: (reference_type_id = 50)
133. 0.025 0.125 ↓ 0.0 0 25

Limit (cost=0.43..22.62 rows=1 width=25) (actual time=0.005..0.005 rows=0 loops=25)

134. 0.100 0.100 ↓ 0.0 0 25

Index Scan using reference_case_idx on reference_t ref_muni_1 (cost=0.43..22.62 rows=1 width=25) (actual time=0.004..0.004 rows=0 loops=25)

  • Index Cond: (external_id = c.id)
  • Filter: ((managing_authority_id = c.main_impacted_muni_id) AND (reference_type_id = 38))
  • Rows Removed by Filter: 10
135. 0.000 0.075 ↑ 1.0 1 25

Limit (cost=0.43..22.62 rows=1 width=25) (actual time=0.003..0.003 rows=1 loops=25)

136. 0.075 0.075 ↑ 1.0 1 25

Index Scan using reference_case_idx on reference_t ref_region_1 (cost=0.43..22.62 rows=1 width=25) (actual time=0.003..0.003 rows=1 loops=25)

  • Index Cond: (external_id = c.id)
  • Filter: ((managing_authority_id = 20) AND (reference_type_id = 1))
  • Rows Removed by Filter: 1
137. 0.025 0.125 ↓ 0.0 0 25

Limit (cost=0.43..22.62 rows=1 width=25) (actual time=0.005..0.005 rows=0 loops=25)

138. 0.100 0.100 ↓ 0.0 0 25

Index Scan using reference_case_idx on reference_t ref_ibge_1 (cost=0.43..22.62 rows=1 width=25) (actual time=0.004..0.004 rows=0 loops=25)

  • Index Cond: (external_id = c.id)
  • Filter: ((managing_authority_id = 21) AND (reference_type_id = 28))
  • Rows Removed by Filter: 12
139. 0.025 0.250 ↓ 0.0 0 25

Limit (cost=10.14..14.15 rows=1 width=25) (actual time=0.010..0.010 rows=0 loops=25)

140. 0.000 0.225 ↓ 0.0 0 25

Bitmap Heap Scan on reference_t ref_spec_1 (cost=10.14..14.15 rows=1 width=25) (actual time=0.009..0.009 rows=0 loops=25)

  • Recheck Cond: ((external_id = c.id) AND (reference_type_id = 50))
  • Filter: (managing_authority_id = 20)
141. 0.025 0.225 ↓ 0.0 0 25

BitmapAnd (cost=10.14..10.14 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=25)

142. 0.050 0.050 ↓ 1.7 12 25

Bitmap Index Scan on reference_case_idx (cost=0.00..4.49 rows=7 width=0) (actual time=0.002..0.002 rows=12 loops=25)

  • Index Cond: (external_id = c.id)
143. 0.150 0.150 ↑ 3.0 43 25

Bitmap Index Scan on reference_type_idx (cost=0.00..5.40 rows=129 width=0) (actual time=0.006..0.006 rows=43 loops=25)

  • Index Cond: (reference_type_id = 50)