explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hii

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 4,825.300 ↑ 1.0 25 1

Limit (cost=7.00..11,196.21 rows=25 width=1,846) (actual time=4,062.771..4,825.300 rows=25 loops=1)

2. 377.770 4,825.291 ↑ 4,946.6 25 1

Nested Loop (cost=7.00..55,348,596.07 rows=123,665 width=1,846) (actual time=4,062.769..4,825.291 rows=25 loops=1)

3. 0.043 778.744 ↑ 4,946.6 25 1

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

4. 0.054 774.501 ↑ 4,946.6 25 1

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

5. 0.041 774.397 ↑ 4,946.6 25 1

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

6. 0.030 773.781 ↑ 4,946.6 25 1

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

7. 0.052 767.476 ↑ 4,946.6 25 1

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

8. 0.125 767.324 ↑ 4,946.6 25 1

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

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

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

10. 0.044 763.769 ↑ 4,946.6 25 1

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

11. 0.036 763.625 ↑ 4,946.6 25 1

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

12. 0.063 756.214 ↑ 5,764.7 25 1

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

13. 0.059 752.501 ↑ 5,764.7 25 1

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

14. 0.033 745.517 ↑ 5,764.7 25 1

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

15. 0.051 745.384 ↑ 5,764.7 25 1

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

16. 0.063 738.208 ↑ 5,764.7 25 1

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

17. 0.040 730.020 ↑ 5,764.7 25 1

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

18. 0.050 720.280 ↑ 5,764.7 25 1

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

19. 0.045 720.230 ↑ 5,764.7 25 1

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

20. 0.047 711.185 ↑ 5,764.7 25 1

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

21. 0.054 703.788 ↑ 5,764.7 25 1

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

22. 557.635 696.459 ↑ 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=34.966..696.459 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.536 138.824 ↑ 1.0 1 536

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

25. 138.288 138.288 ↑ 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.258..0.258 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. 7.275 7.275 ↑ 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.273..0.291 rows=1 loops=25)

  • Index Cond: (id = c.configuration_id)
27. 7.350 7.350 ↑ 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.293..0.294 rows=1 loops=25)

  • Index Cond: (c.id = case_id)
28. 9.000 9.000 ↑ 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.359..0.360 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. 9.700 9.700 ↑ 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.388..0.388 rows=1 loops=25)

  • Index Cond: (case_id = c.id)
31. 8.125 8.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.324..0.325 rows=1 loops=25)

  • Index Cond: (id = g.target_principal_address_id)
32. 7.125 7.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.284..0.285 rows=0 loops=25)

  • Index Cond: (g.id = geolocation_id)
  • Filter: ((context_type)::text = 'TARGET'::text)
  • Rows Removed by Filter: 1
33. 0.100 0.100 ↓ 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.004..0.004 rows=0 loops=25)

  • Index Cond: (c.id = id)
34. 6.925 6.925 ↑ 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.276..0.277 rows=1 loops=25)

  • Index Cond: (c.id = id)
35. 3.650 3.650 ↑ 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.146..0.146 rows=1 loops=25)

  • Index Cond: (prt.delivery_id = id)
36. 7.375 7.375 ↑ 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.272..0.295 rows=1 loops=25)

  • Index Cond: (case_id = c.id)
  • Filter: (authority_abstract_id = 20)
  • Rows Removed by Filter: 2
37. 0.100 0.100 ↑ 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.003..0.004 rows=1 loops=25)

  • Index Cond: (id = p.definition_id)
38. 3.200 3.200 ↑ 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.127..0.128 rows=1 loops=25)

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

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

40. 0.141 0.141 ↑ 1.0 28 1

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

41. 0.100 0.100 ↑ 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.004..0.004 rows=1 loops=25)

  • Index Cond: (c.version_id = id)
42. 6.275 6.275 ↓ 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.250..0.251 rows=0 loops=25)

  • Index Cond: (id = board.nearest_term_date_id)
43. 0.575 0.575 ↓ 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.022..0.023 rows=0 loops=25)

  • Index Cond: (id = tfc.term_id)
44. 0.050 0.050 ↓ 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.002..0.002 rows=0 loops=25)

  • Index Cond: (id = term.term_dictionary_id)
45. 4.200 4.200 ↓ 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.168..0.168 rows=0 loops=25)

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

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

47. 0.393 0.393 ↑ 1.0 1 1

Seq Scan on authority_abstract_t abstractau2_ (cost=0.00..1.85 rows=1 width=35) (actual time=0.387..0.393 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. 278.668 2,021.786 ↓ 1.5 353,601 1

Hash Join (cost=78,928.66..129,489.54 rows=229,105 width=8) (actual time=1,364.182..2,021.786 rows=353,601 loops=1)

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

Seq Scan on intervenor_t interv_1 (cost=0.00..45,313.37 rows=788,388 width=16) (actual time=0.569..381.265 rows=787,827 loops=1)

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

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

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

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

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

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

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

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

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

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

64. 109.404 219.204 ↑ 1.0 515,177 1

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

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

66. 0.019 0.987 ↑ 1.0 1 1

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

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

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

68. 0.002 0.019 ↑ 1.0 1 1

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

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

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

  • Index Cond: (id = pers_2.id)
73. 0.264 17.712 ↑ 1.0 1 24

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

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

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

75. 0.050 5.832 ↑ 1.0 1 24

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

76. 3.432 3.432 ↑ 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.142..0.143 rows=1 loops=24)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 1
77. 2.350 2.350 ↑ 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.094..0.094 rows=1 loops=25)

  • Index Cond: (id = interv_3.id)
  • Filter: ((intervening_person_context)::text = 'REQUESTER'::text)
78. 3.744 3.744 ↑ 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.156..0.156 rows=1 loops=24)

  • Index Cond: (id = intervp_3.person_id)
  • Heap Fetches: 24
79. 7.320 7.320 ↑ 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.304..0.305 rows=1 loops=24)

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

SubPlan (for Nested Loop)

81. 0.504 0.504 ↓ 0.0 0 24

Seq Scan on juridical_form_t (cost=0.00..1.19 rows=1 width=516) (actual time=0.020..0.021 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. 266.615 1,616.257 ↓ 1.5 353,601 1

Hash Join (cost=78,928.66..129,489.54 rows=229,105 width=8) (actual time=1,141.108..1,616.257 rows=353,601 loops=1)

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

Seq Scan on intervenor_t interv_5 (cost=0.00..45,313.37 rows=788,388 width=16) (actual time=0.006..210.191 rows=787,827 loops=1)

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

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

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

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

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

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

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

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

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

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

97. 98.006 166.077 ↑ 1.0 515,177 1

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

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

99. 0.011 0.038 ↑ 1.0 1 1

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

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

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

101. 0.003 0.016 ↑ 1.0 1 1

Nested Loop (cost=0.85..24.62 rows=1 width=8) (actual time=0.016..0.016 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.005..0.005 rows=1 loops=1)

  • Index Cond: (case_id = c.id)
  • Filter: is_primary
  • Rows Removed by Filter: 1
103. 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)
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.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.144 0.672 ↑ 1.0 1 24

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

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

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

108. 0.044 0.288 ↑ 1.0 1 24

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

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

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

  • Index Cond: (id = intervp_7.person_id)
  • Heap Fetches: 24
112. 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.47 rows=1 width=33) (actual time=0.003..0.003 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.100 7.950 ↑ 1.0 1 25

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

116. 0.050 7.725 ↑ 1.0 1 25

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

117. 0.073 6.775 ↑ 1.0 1 25

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

118. 0.150 0.150 ↑ 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.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
119. 6.552 6.552 ↑ 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.234..0.234 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.900 0.900 ↑ 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.036..0.036 rows=1 loops=25)

  • Index Cond: (id = iu.user_id)
121. 0.125 0.125 ↑ 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.005..0.005 rows=1 loops=25)

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

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

123. 0.250 0.250 ↓ 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.010..0.010 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.050 0.125 ↑ 1.0 1 25

Limit (cost=0.43..22.62 rows=1 width=16) (actual time=0.005..0.005 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.150 ↓ 0.0 0 25

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

127. 0.150 0.150 ↓ 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.006..0.006 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 1.775 ↓ 0.0 0 25

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

129. 0.025 1.725 ↓ 0.0 0 25

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

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

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

131. 0.100 0.100 ↓ 1.7 12 25

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

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

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

  • Index Cond: (reference_type_id = 50)
133. 0.000 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.125 0.125 ↓ 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.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: 10
135. 0.025 0.100 ↑ 1.0 1 25

Limit (cost=0.43..22.62 rows=1 width=25) (actual time=0.004..0.004 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.000 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.125 0.125 ↓ 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.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.300 ↓ 0.0 0 25

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

140. 0.025 0.275 ↓ 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.011..0.011 rows=0 loops=25)

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

BitmapAnd (cost=10.14..10.14 rows=1 width=0) (actual time=0.010..0.010 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.175 0.175 ↑ 3.0 43 25

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

  • Index Cond: (reference_type_id = 50)
Planning time : 497.039 ms