explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tjde

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 3,917.064 ↑ 1.0 25 1

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

2. 364.822 3,917.060 ↑ 4,946.6 25 1

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

3. 0.040 91.347 ↑ 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.625..91.347 rows=25 loops=1)

4. 0.028 91.257 ↑ 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.621..91.257 rows=25 loops=1)

5. 0.012 91.204 ↑ 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.619..91.204 rows=25 loops=1)

6. 0.021 91.142 ↑ 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.616..91.142 rows=25 loops=1)

7. 0.043 91.046 ↑ 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.614..91.046 rows=25 loops=1)

8. 0.061 90.953 ↑ 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.607..90.953 rows=25 loops=1)

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

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

10. 0.038 90.686 ↑ 4,946.6 25 1

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

11. 0.029 90.598 ↑ 4,946.6 25 1

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

12. 0.021 90.419 ↑ 5,764.7 25 1

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

13. 0.041 90.323 ↑ 5,764.7 25 1

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

14. 0.033 90.182 ↑ 5,764.7 25 1

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

15. 0.013 90.099 ↑ 5,764.7 25 1

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

16. 0.045 89.961 ↑ 5,764.7 25 1

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

17. 0.033 89.816 ↑ 5,764.7 25 1

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

18. 0.031 89.683 ↑ 5,764.7 25 1

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

19. 0.022 89.652 ↑ 5,764.7 25 1

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

20. 0.026 89.505 ↑ 5,764.7 25 1

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

21. 0.031 89.379 ↑ 5,764.7 25 1

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

22. 87.079 89.223 ↑ 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.461..89.223 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: 168687
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.004..0.005 rows=1 loops=25)

  • Index Cond: (id = c.configuration_id)
27. 0.100 0.100 ↑ 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.004 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.100 0.100 ↑ 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.004..0.004 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.004..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.100 0.100 ↑ 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.004 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.068 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.007 0.007 ↑ 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.007 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.003..0.003 rows=0 loops=25)

  • Index Cond: (id = board.nearest_term_date_id)
43. 0.050 0.050 ↓ 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.002..0.002 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.012 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.013 0.013 ↑ 1.0 1 1

Seq Scan on authority_abstract_t abstractau2_ (cost=0.00..1.85 rows=1 width=35) (actual time=0.008..0.013 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. 273.613 1,745.810 ↓ 1.5 353,601 1

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

  • Hash Cond: (interv_1.id = intervp_1.id)
57. 250.204 250.204 ↑ 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.005..250.204 rows=787,827 loops=1)

  • Filter: is_primary
  • Rows Removed by Filter: 975912
58. 90.888 1,221.993 ↑ 1.4 356,795 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 18034kB
59. 250.456 1,131.105 ↑ 1.4 356,795 1

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

  • Hash Cond: (intervp_1.person_id = pers_1.id)
60. 124.792 124.792 ↑ 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.011..124.792 rows=589,964 loops=1)

  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 117953
61. 133.617 755.857 ↑ 1.0 515,177 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 28245kB
62. 349.795 622.240 ↑ 1.0 515,177 1

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

  • Hash Cond: (pers_1.id = pnat_1.id)
63. 91.895 91.895 ↑ 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.007..91.895 rows=800,157 loops=1)

64. 109.765 180.550 ↑ 1.0 515,177 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 24221kB
65. 70.785 70.785 ↑ 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.009..70.785 rows=515,177 loops=1)

66. 0.014 0.044 ↑ 1.0 1 1

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

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

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

68. 0.003 0.015 ↑ 1.0 1 1

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

69. 0.006 0.006 ↑ 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.005..0.006 rows=1 loops=1)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 1
70. 0.006 0.006 ↑ 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.006..0.006 rows=1 loops=1)

  • Index Cond: (id = interv_2.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
71. 0.004 0.004 ↑ 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.004..0.004 rows=1 loops=1)

  • Index Cond: (id = intervp_2.person_id)
  • Heap Fetches: 1
72. 0.007 0.007 ↑ 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.006..0.007 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.033..0.034 rows=1 loops=24)

  • Join Filter: (intervp_3.person_id = pleg.id)
74. 0.024 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.067 0.336 ↑ 1.0 1 24

Nested Loop (cost=0.85..24.62 rows=1 width=8) (actual time=0.013..0.014 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.003..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. 274.381 1,711.833 ↓ 1.5 353,601 1

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

  • Hash Cond: (interv_5.id = intervp_5.id)
90. 240.837 240.837 ↑ 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..240.837 rows=787,827 loops=1)

  • Filter: is_primary
  • Rows Removed by Filter: 975912
91. 86.481 1,196.615 ↑ 1.4 356,795 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 18034kB
92. 241.980 1,110.134 ↑ 1.4 356,795 1

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

  • Hash Cond: (intervp_5.person_id = pers_5.id)
93. 117.667 117.667 ↑ 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.018..117.667 rows=589,964 loops=1)

  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 117953
94. 129.227 750.487 ↑ 1.0 515,177 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 28245kB
95. 350.011 621.260 ↑ 1.0 515,177 1

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

  • Hash Cond: (pers_5.id = pnat_4.id)
96. 87.048 87.048 ↑ 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..87.048 rows=800,157 loops=1)

97. 112.141 184.201 ↑ 1.0 515,177 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 24221kB
98. 72.060 72.060 ↑ 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.007..72.060 rows=515,177 loops=1)

99. 0.009 0.033 ↑ 1.0 1 1

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

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

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

101. 0.002 0.013 ↑ 1.0 1 1

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

102. 0.005 0.005 ↑ 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.005 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.003 0.003 ↑ 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.003 rows=1 loops=1)

  • Index Cond: (id = intervp_6.person_id)
  • Heap Fetches: 1
105. 0.004 0.004 ↑ 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.004 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.024 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.045 0.216 ↑ 1.0 1 24

Nested Loop (cost=0.85..24.62 rows=1 width=8) (actual time=0.008..0.009 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.500 ↑ 1.0 1 25

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

116. 0.025 0.350 ↑ 1.0 1 25

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

117. 0.038 0.250 ↑ 1.0 1 25

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

118. 0.100 0.100 ↑ 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.004 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.000 0.175 ↓ 0.0 0 25

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

123. 0.175 0.175 ↓ 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.007..0.007 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.003..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.025 0.375 ↓ 0.0 0 25

Limit (cost=10.14..14.15 rows=1 width=16) (actual time=0.015..0.015 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)
Planning time : 281.825 ms