explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CFA6

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 4,477.009 ↑ 1.0 25 1

Limit (cost=6.56..67,999.04 rows=25 width=848) (actual time=4,296.907..4,477.009 rows=25 loops=1)

2. 268.406 4,477.003 ↑ 4,865.8 25 1

Nested Loop (cost=6.56..330,837,817.54 rows=121,645 width=848) (actual time=4,296.906..4,477.003 rows=25 loops=1)

3. 0.044 162.844 ↑ 4,865.8 25 1

Nested Loop (cost=6.56..93,236,841.06 rows=121,645 width=813) (actual time=6.102..162.844 rows=25 loops=1)

4. 0.022 153.875 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=6.13..93,084,090.45 rows=136,279 width=773) (actual time=5.574..153.875 rows=25 loops=1)

5. 0.639 146.428 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=5.70..93,004,769.50 rows=136,279 width=773) (actual time=5.572..146.428 rows=25 loops=1)

  • Join Filter: (termdic.id = term.term_dictionary_id)
  • Rows Removed by Join Filter: 5888
6. 0.039 145.414 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=5.70..92,524,378.09 rows=136,279 width=702) (actual time=5.450..145.414 rows=25 loops=1)

7. 0.051 145.325 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=5.42..92,483,151.29 rows=136,279 width=702) (actual time=5.449..145.325 rows=25 loops=1)

8. 0.471 143.474 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=4.99..92,362,728.51 rows=136,279 width=694) (actual time=5.446..143.474 rows=25 loops=1)

  • Join Filter: (c.version_id = vers.id)
  • Rows Removed by Join Filter: 4532
9. 0.092 142.703 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=4.99..91,982,501.78 rows=136,279 width=596) (actual time=5.384..142.703 rows=25 loops=1)

  • Join Filter: (c.managing_authority_id = a.id)
  • Rows Removed by Join Filter: 675
10. 0.037 142.561 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=4.99..91,925,263.25 rows=136,279 width=559) (actual time=5.366..142.561 rows=25 loops=1)

11. 0.037 136.224 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=4.56..91,294,051.57 rows=136,279 width=526) (actual time=5.356..136.224 rows=25 loops=1)

12. 0.640 133.012 ↑ 5,451.2 25 1

Nested Loop (cost=4.13..91,228,543.59 rows=136,279 width=351) (actual time=5.354..133.012 rows=25 loops=1)

  • Join Filter: (pdt.id = p.definition_id)
  • Rows Removed by Join Filter: 7182
13. 0.043 131.947 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=4.13..90,625,501.33 rows=136,279 width=349) (actual time=5.325..131.947 rows=25 loops=1)

14. 0.042 127.279 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=3.71..90,525,760.46 rows=136,279 width=319) (actual time=5.313..127.279 rows=25 loops=1)

15. 0.045 127.162 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=3.42..90,475,775.38 rows=136,279 width=295) (actual time=5.305..127.162 rows=25 loops=1)

16. 0.055 120.167 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=2.99..90,390,547.17 rows=136,279 width=303) (actual time=5.292..120.167 rows=25 loops=1)

17. 0.026 114.187 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=2.56..90,259,164.80 rows=136,279 width=249) (actual time=5.282..114.187 rows=25 loops=1)

18. 0.060 113.886 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=2.13..90,161,343.29 rows=136,279 width=233) (actual time=5.270..113.886 rows=25 loops=1)

19. 0.061 113.826 ↑ 5,451.2 25 1

Nested Loop (cost=1.70..90,060,046.03 rows=136,279 width=233) (actual time=5.268..113.826 rows=25 loops=1)

20. 0.043 106.565 ↑ 5,451.2 25 1

Nested Loop Left Join (cost=1.27..89,977,868.20 rows=136,279 width=225) (actual time=5.252..106.565 rows=25 loops=1)

21. 0.033 100.372 ↑ 5,451.2 25 1

Nested Loop (cost=0.85..89,883,510.36 rows=136,279 width=211) (actual time=5.235..100.372 rows=25 loops=1)

22. 91.984 94.664 ↑ 5,930.1 25 1

Index Scan using case_unique_business_id_idx on case_t c (cost=0.42..89,777,705.88 rows=148,253 width=203) (actual time=5.224..94.664 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.680 ↑ 1.0 1 536

Limit (cost=0.43..210.96 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=536)

25. 2.680 2.680 ↑ 1.0 1 536

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

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

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

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

Index Scan using case_status_board_to_case_idx on case_status_board_t board (cost=0.42..0.68 rows=1 width=22) (actual time=0.246..0.246 rows=1 loops=25)

  • Index Cond: (c.id = case_id)
28. 7.200 7.200 ↑ 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.288..0.288 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.73 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=25)

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

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

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

Index Scan using pk_address_t on address_t ad (cost=0.43..0.95 rows=1 width=70) (actual time=0.237..0.237 rows=1 loops=25)

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

Index Scan using geolocation_localization_context_geolocation_id_idx on geolocation_localization_context_t glc (cost=0.43..0.62 rows=1 width=8) (actual time=0.278..0.278 rows=0 loops=25)

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

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

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

Index Scan using pk_permit_request_t on permit_request_t prt (cost=0.42..0.72 rows=1 width=38) (actual time=0.162..0.185 rows=1 loops=25)

  • Index Cond: (c.id = id)
35. 0.380 0.425 ↑ 1.0 288 25

Materialize (cost=0.00..8.43 rows=295 width=18) (actual time=0.001..0.017 rows=288 loops=25)

36. 0.045 0.045 ↓ 1.0 298 1

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

37. 3.175 3.175 ↑ 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.127..0.127 rows=1 loops=25)

  • Index Cond: (prt.delivery_id = id)
38. 6.300 6.300 ↑ 1.0 1 25

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

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

Materialize (cost=0.00..1.42 rows=28 width=37) (actual time=0.000..0.002 rows=28 loops=25)

40. 0.009 0.009 ↑ 1.0 28 1

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

41. 0.254 0.300 ↑ 1.0 182 25

Materialize (cost=0.00..8.79 rows=186 width=106) (actual time=0.000..0.012 rows=182 loops=25)

42. 0.046 0.046 ↑ 1.0 186 1

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

43. 1.800 1.800 ↓ 0.0 0 25

Index Scan using term_for_case_pk on term_for_case_t tfc (cost=0.43..0.87 rows=1 width=24) (actual time=0.072..0.072 rows=0 loops=25)

  • Index Cond: (id = board.nearest_term_date_id)
44. 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)
45. 0.325 0.375 ↓ 1.0 236 25

Materialize (cost=0.00..8.53 rows=235 width=87) (actual time=0.000..0.015 rows=236 loops=25)

46. 0.050 0.050 ↓ 1.0 236 1

Seq Scan on term_dictionary_t termdic (cost=0.00..7.35 rows=235 width=87) (actual time=0.004..0.050 rows=236 loops=1)

47. 7.425 7.425 ↓ 0.0 0 25

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

  • Index Cond: (id = tfc.deadline_id)
48. 8.925 8.925 ↑ 1.0 1 25

Index Scan using case_actor_authority_case_idx on case_actor_authority_t actors1_ (cost=0.43..1.11 rows=1 width=40) (actual time=0.337..0.357 rows=1 loops=25)

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

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

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

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

SubPlan (for Nested Loop)

52. 0.000 0.000 ↓ 0.0 0

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

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

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

54. 0.000 0.000 ↓ 0.0 0

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

55. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
56. 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)
57. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: (id = pers.id)
  • Heap Fetches: 0
59. 295.488 1,991.662 ↓ 1.4 354,613 1

Hash Join (cost=80,042.57..162,907.74 rows=262,503 width=8) (actual time=668.403..1,991.662 rows=354,613 loops=1)

  • Hash Cond: (intervp_1.person_id = pnat_1.id)
60. 382.566 1,534.671 ↓ 2.2 579,170 1

Hash Join (cost=60,746.93..134,916.69 rows=262,503 width=24) (actual time=504.635..1,534.671 rows=579,170 loops=1)

  • Hash Cond: (intervp_1.person_id = pers_1.id)
61. 424.905 900.662 ↓ 2.2 579,170 1

Hash Join (cost=26,769.12..91,599.47 rows=262,503 width=16) (actual time=250.827..900.662 rows=579,170 loops=1)

  • Hash Cond: (interv_1.id = intervp_1.id)
62. 227.133 227.133 ↓ 1.0 796,742 1

Seq Scan on intervenor_t interv_1 (cost=0.00..48,883.23 rows=773,623 width=16) (actual time=0.005..227.133 rows=796,742 loops=1)

  • Filter: is_primary
  • Rows Removed by Filter: 979318
63. 114.297 248.624 ↓ 1.0 592,362 1

Hash (cost=16,570.56..16,570.56 rows=586,685 width=16) (actual time=248.624..248.624 rows=592,362 loops=1)

  • Buckets: 524288 Batches: 4 Memory Usage: 11035kB
64. 134.327 134.327 ↓ 1.0 592,362 1

Seq Scan on intervening_person_t intervp_1 (cost=0.00..16,570.56 rows=586,685 width=16) (actual time=0.015..134.327 rows=592,362 loops=1)

  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 118780
65. 141.512 251.443 ↑ 1.0 802,034 1

Hash (cost=20,683.58..20,683.58 rows=810,258 width=8) (actual time=251.443..251.443 rows=802,034 loops=1)

  • Buckets: 524288 Batches: 4 Memory Usage: 11940kB
66. 109.931 109.931 ↑ 1.0 802,034 1

Seq Scan on person_t pers_1 (cost=0.00..20,683.58 rows=810,258 width=8) (actual time=0.006..109.931 rows=802,034 loops=1)

67. 93.942 161.503 ↓ 1.0 516,401 1

Hash (cost=10,865.06..10,865.06 rows=513,806 width=8) (actual time=161.503..161.503 rows=516,401 loops=1)

  • Buckets: 524288 Batches: 2 Memory Usage: 14178kB
68. 67.561 67.561 ↓ 1.0 516,401 1

Seq Scan on person_natural_t pnat_1 (cost=0.00..10,865.06 rows=513,806 width=8) (actual time=0.007..67.561 rows=516,401 loops=1)

69. 0.017 0.682 ↑ 1.0 1 1

Nested Loop (cost=1.70..38.04 rows=1 width=17) (actual time=0.679..0.682 rows=1 loops=1)

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

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

71. 0.005 0.026 ↑ 1.0 1 1

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

72. 0.006 0.006 ↑ 2.0 1 1

Index Scan using intervenor_case_idx on intervenor_t interv_2 (cost=0.43..20.08 rows=2 width=8) (actual time=0.006..0.006 rows=1 loops=1)

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

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

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

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

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

  • Index Cond: (id = pers_2.id)
76. 0.240 15.072 ↑ 1.0 1 24

Nested Loop (cost=1.70..39.24 rows=1 width=25) (actual time=0.613..0.628 rows=1 loops=24)

  • Join Filter: (intervp_3.person_id = pleg.id)
77. 0.024 8.184 ↑ 1.0 1 24

Nested Loop (cost=1.28..37.55 rows=1 width=16) (actual time=0.340..0.341 rows=1 loops=24)

78. 0.055 5.496 ↑ 1.0 1 24

Nested Loop (cost=0.85..36.99 rows=1 width=8) (actual time=0.228..0.229 rows=1 loops=24)

79. 2.016 2.016 ↑ 2.0 1 24

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

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 1
80. 3.425 3.425 ↑ 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.137..0.137 rows=1 loops=25)

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

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

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

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

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

SubPlan (for Nested Loop)

84. 0.096 0.096 ↓ 0.0 0 24

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

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

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

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

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

87. 0.000 0.000 ↓ 0.0 0

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

88. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
89. 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)
90. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = intervp_4.person_id)
  • Heap Fetches: 0
91. 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.45 rows=1 width=8) (never executed)

  • Index Cond: (id = pers_4.id)
  • Heap Fetches: 0
92. 295.373 2,030.824 ↓ 1.4 354,613 1

Hash Join (cost=80,042.57..162,907.74 rows=262,503 width=8) (actual time=676.999..2,030.824 rows=354,613 loops=1)

  • Hash Cond: (intervp_5.person_id = pnat_4.id)
93. 389.930 1,569.483 ↓ 2.2 579,170 1

Hash Join (cost=60,746.93..134,916.69 rows=262,503 width=24) (actual time=508.986..1,569.483 rows=579,170 loops=1)

  • Hash Cond: (intervp_5.person_id = pers_5.id)
94. 442.851 927.962 ↓ 2.2 579,170 1

Hash Join (cost=26,769.12..91,599.47 rows=262,503 width=16) (actual time=255.039..927.962 rows=579,170 loops=1)

  • Hash Cond: (interv_5.id = intervp_5.id)
95. 232.326 232.326 ↓ 1.0 796,742 1

Seq Scan on intervenor_t interv_5 (cost=0.00..48,883.23 rows=773,623 width=16) (actual time=0.005..232.326 rows=796,742 loops=1)

  • Filter: is_primary
  • Rows Removed by Filter: 979318
96. 116.748 252.785 ↓ 1.0 592,362 1

Hash (cost=16,570.56..16,570.56 rows=586,685 width=16) (actual time=252.785..252.785 rows=592,362 loops=1)

  • Buckets: 524288 Batches: 4 Memory Usage: 11035kB
97. 136.037 136.037 ↓ 1.0 592,362 1

Seq Scan on intervening_person_t intervp_5 (cost=0.00..16,570.56 rows=586,685 width=16) (actual time=0.016..136.037 rows=592,362 loops=1)

  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
  • Rows Removed by Filter: 118780
98. 141.821 251.591 ↑ 1.0 802,034 1

Hash (cost=20,683.58..20,683.58 rows=810,258 width=8) (actual time=251.591..251.591 rows=802,034 loops=1)

  • Buckets: 524288 Batches: 4 Memory Usage: 11940kB
99. 109.770 109.770 ↑ 1.0 802,034 1

Seq Scan on person_t pers_5 (cost=0.00..20,683.58 rows=810,258 width=8) (actual time=0.006..109.770 rows=802,034 loops=1)

100. 97.238 165.968 ↓ 1.0 516,401 1

Hash (cost=10,865.06..10,865.06 rows=513,806 width=8) (actual time=165.968..165.968 rows=516,401 loops=1)

  • Buckets: 524288 Batches: 2 Memory Usage: 14178kB
101. 68.730 68.730 ↓ 1.0 516,401 1

Seq Scan on person_natural_t pnat_4 (cost=0.00..10,865.06 rows=513,806 width=8) (actual time=0.008..68.730 rows=516,401 loops=1)

102. 0.011 0.039 ↑ 1.0 1 1

Nested Loop (cost=1.70..38.04 rows=1 width=17) (actual time=0.038..0.039 rows=1 loops=1)

  • Join Filter: (intervp_6.person_id = pnat_5.id)
103. 0.006 0.025 ↑ 1.0 1 1

Nested Loop (cost=1.28..37.55 rows=1 width=16) (actual time=0.024..0.025 rows=1 loops=1)

104. 0.002 0.015 ↑ 1.0 1 1

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

105. 0.005 0.005 ↑ 2.0 1 1

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

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

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

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

  • Index Cond: (id = intervp_6.person_id)
  • Heap Fetches: 1
108. 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.48 rows=1 width=25) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (id = pers_6.id)
109. 0.120 0.624 ↑ 1.0 1 24

Nested Loop (cost=1.70..39.24 rows=1 width=25) (actual time=0.025..0.026 rows=1 loops=24)

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

Nested Loop (cost=1.28..37.55 rows=1 width=16) (actual time=0.015..0.016 rows=1 loops=24)

111. 0.020 0.264 ↑ 1.0 1 24

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

112. 0.144 0.144 ↑ 2.0 1 24

Index Scan using intervenor_case_idx on intervenor_t interv_7 (cost=0.43..20.08 rows=2 width=8) (actual time=0.005..0.006 rows=1 loops=24)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 1
113. 0.100 0.100 ↑ 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.004..0.004 rows=1 loops=25)

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

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

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

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

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

SubPlan (for Nested Loop)

117. 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=7) (actual time=0.002..0.002 rows=0 loops=24)

  • Filter: (id = pleg_1.juridical_form_id)
  • Rows Removed by Filter: 15
118. 0.075 5.625 ↑ 1.0 1 25

Nested Loop Left Join (cost=1.56..34.97 rows=1 width=17) (actual time=0.221..0.225 rows=1 loops=25)

119. 0.025 5.450 ↑ 1.0 1 25

Nested Loop Left Join (cost=1.13..28.94 rows=1 width=8) (actual time=0.215..0.218 rows=1 loops=25)

120. 0.051 5.325 ↑ 1.0 1 25

Nested Loop (cost=0.85..28.63 rows=1 width=8) (actual time=0.210..0.213 rows=1 loops=25)

121. 0.150 0.150 ↑ 1.0 1 25

Index Scan using intervenor_case_idx on intervenor_t i (cost=0.43..20.18 rows=1 width=8) (actual time=0.004..0.006 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
122. 5.124 5.124 ↑ 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.183..0.183 rows=1 loops=28)

  • Index Cond: (id = i.id)
  • Filter: (on_behalf_of_authority_id = c.managing_authority_id)
  • Rows Removed by Filter: 0
123. 0.100 0.100 ↑ 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.004..0.004 rows=1 loops=25)

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

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

  • Index Cond: (id = up.abstract_person_id)
125. 0.000 0.200 ↓ 0.0 0 25

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

126. 0.200 0.200 ↓ 0.0 0 25

Index Scan using reference_case_idx on reference_t ref_muni (cost=0.43..210.96 rows=1 width=16) (actual time=0.008..0.008 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: 12
127. 0.000 0.150 ↑ 1.0 1 25

Limit (cost=0.43..210.96 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=25)

128. 0.150 0.150 ↑ 1.0 1 25

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

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

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

130. 0.125 0.125 ↓ 0.0 0 25

Index Scan using reference_case_idx on reference_t ref_ibge (cost=0.43..210.96 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
131. 0.025 0.150 ↓ 0.0 0 25

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

132. 0.125 0.125 ↓ 0.0 0 25

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

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

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

134. 0.125 0.125 ↓ 0.0 0 25

Index Scan using reference_case_idx on reference_t ref_muni_1 (cost=0.43..210.96 rows=1 width=25) (actual time=0.005..0.005 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: 12
135. 0.025 0.150 ↑ 1.0 1 25

Limit (cost=0.43..210.96 rows=1 width=25) (actual time=0.006..0.006 rows=1 loops=25)

136. 0.125 0.125 ↑ 1.0 1 25

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

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

Limit (cost=0.43..210.96 rows=1 width=25) (actual time=0.006..0.006 rows=0 loops=25)

138. 0.125 0.125 ↓ 0.0 0 25

Index Scan using reference_case_idx on reference_t ref_ibge_1 (cost=0.43..210.96 rows=1 width=25) (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
139. 0.025 0.150 ↓ 0.0 0 25

Limit (cost=0.43..210.96 rows=1 width=25) (actual time=0.006..0.006 rows=0 loops=25)

140. 0.125 0.125 ↓ 0.0 0 25

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

  • Index Cond: (external_id = c.id)
  • Filter: ((managing_authority_id = 20) AND (reference_type_id = 50))
  • Rows Removed by Filter: 12
Planning time : 143.647 ms