explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9nH5

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,644.229 ↑ 1.0 1 1

Aggregate (cost=55,763,624.70..55,763,624.71 rows=1 width=8) (actual time=4,644.229..4,644.229 rows=1 loops=1)

2. 0.001 4,644.227 ↓ 0.0 0 1

Nested Loop (cost=2.13..55,763,624.68 rows=6 width=8) (actual time=4,644.227..4,644.227 rows=0 loops=1)

3. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on authority_abstract_t abstractau2_ (cost=0.00..1.85 rows=1 width=8) (actual time=0.008..0.013 rows=1 loops=1)

  • Filter: (id = 4)
  • Rows Removed by Filter: 67
4. 0.000 4,644.213 ↓ 0.0 0 1

Nested Loop (cost=2.13..55,763,622.77 rows=6 width=16) (actual time=4,644.213..4,644.213 rows=0 loops=1)

  • Join Filter: (p.definition_id = pdt.id)
5. 0.034 0.034 ↓ 1.0 295 1

Seq Scan on phase_definition_t pdt (cost=0.00..6.92 rows=292 width=8) (actual time=0.004..0.034 rows=295 loops=1)

6. 0.045 4,644.185 ↓ 0.0 0 295

Materialize (cost=2.13..55,763,589.59 rows=6 width=24) (actual time=15.743..15.743 rows=0 loops=295)

7. 0.000 4,644.140 ↓ 0.0 0 1

Nested Loop (cost=2.13..55,763,589.56 rows=6 width=24) (actual time=4,644.140..4,644.140 rows=0 loops=1)

8. 0.001 4,644.140 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.71..55,763,166.68 rows=50 width=16) (actual time=4,644.140..4,644.140 rows=0 loops=1)

9. 0.000 4,644.139 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.28..55,763,135.73 rows=50 width=24) (actual time=4,644.139..4,644.139 rows=0 loops=1)

10. 0.000 4,644.139 ↓ 0.0 0 1

Nested Loop (cost=0.85..55,762,713.11 rows=50 width=16) (actual time=4,644.139..4,644.139 rows=0 loops=1)

11. 0.000 4,644.139 ↓ 0.0 0 1

Nested Loop (cost=0.42..55,762,683.30 rows=50 width=16) (actual time=4,644.139..4,644.139 rows=0 loops=1)

  • Join Filter: (c.configuration_id = config.id)
12. 55.750 55.750 ↑ 1.0 660,885 1

Seq Scan on configuration_t config (cost=0.00..14,120.65 rows=660,965 width=16) (actual time=0.008..55.750 rows=660,885 loops=1)

13. 120.190 4,626.195 ↓ 0.0 0 660,885

Materialize (cost=0.42..55,252,839.03 rows=50 width=40) (actual time=0.007..0.007 rows=0 loops=660,885)

14. 1,406.496 4,506.005 ↓ 0.0 0 1

Index Scan using case_unique_business_id_idx on case_t c (cost=0.42..55,252,838.78 rows=50 width=40) (actual time=4,506.005..4,506.005 rows=0 loops=1)

  • Index Cond: ((case_type)::text = 'URBANISM'::text)
  • Filter: ((NOT a_new_version_exists) AND (f_unaccent(upper((SubPlan 1))) ~~ '%PIERRE%'::text) AND ((upper(((SubPlan 2))::text) ~~ '%PU%'::text) OR (upper(((SubPlan 3))::text) ~~ '%PU%'::text)))
  • Rows Removed by Filter: 439217
15.          

SubPlan (forIndex Scan)

16. 211.025 3,069.108 ↑ 1.0 1 438,444

Nested Loop Left Join (cost=1.56..35.34 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=438,444)

17. 211.025 2,630.664 ↑ 1.0 1 438,444

Nested Loop Left Join (cost=1.13..29.32 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=438,444)

18. 127.874 2,192.220 ↑ 1.0 1 438,444

Nested Loop (cost=0.85..29.00 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=438,444)

19. 1,315.332 1,315.332 ↑ 1.0 1 438,444

Index Scan using intervenor_case_idx on intervenor_t i (cost=0.43..20.55 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=438,444)

  • Index Cond: (case_id = c.id)
  • Filter: ((((c.case_type)::text = ANY ('{URBANISM,GENERIC,ENVIRONMENT,ADMINISTRATIVE,SUBDIVISION,STATUTORY_PROVISIONING,GEN,PPAS,PAD}'::text[])) AND ((intervenor_role)::text = 'MANAGER_EXPERT'::text)) OR (((c.case_type)::text = ANY ('{REPORTING,INFRINGEMENT}'::text[])) AND ((intervenor_role)::text = 'INFRACTION_REPORTING_CONTROLLER_PU'::text)) OR (((c.case_type)::text = 'PEB'::text) AND ((intervenor_role)::text = 'MANAGER_PEB'::text)))
  • Rows Removed by Filter: 2
20. 749.014 749.014 ↑ 1.0 1 374,507

Index Scan using pk_intervening_user_t on intervening_user_t iu (cost=0.42..8.45 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=374,507)

  • Index Cond: (id = i.id)
  • Filter: (on_behalf_of_authority_id = c.managing_authority_id)
  • Rows Removed by Filter: 0
21. 227.419 227.419 ↑ 1.0 1 227,419

Index Scan using pk_user_person_t on user_person_t up (cost=0.28..0.30 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=227,419)

  • Index Cond: (id = iu.user_id)
22. 227.419 227.419 ↑ 1.0 1 227,419

Index Scan using pk_person_natural_t on person_natural_t p_1 (cost=0.42..6.00 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=227,419)

  • Index Cond: (id = up.abstract_person_id)
23. 0.000 21.715 ↓ 0.0 0 4,343

Limit (cost=0.43..45.09 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=4,343)

24. 21.715 21.715 ↓ 0.0 0 4,343

Index Scan using reference_case_idx on reference_t ref_ibge (cost=0.43..45.09 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=4,343)

  • Index Cond: (external_id = c.id)
  • Filter: ((managing_authority_id = 21) AND (reference_type_id = 28))
  • Rows Removed by Filter: 7
25. 0.000 8.686 ↓ 0.0 0 4,343

Limit (cost=0.43..45.09 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,343)

26. 8.686 8.686 ↓ 0.0 0 4,343

Index Scan using reference_case_idx on reference_t ref_region (cost=0.43..45.09 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,343)

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

Index Scan using pk_phase_t on phase_t p (cost=0.43..0.59 rows=1 width=24) (never executed)

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

Index Scan using geolocation__case_idx on geolocation_t g (cost=0.42..8.44 rows=1 width=24) (never executed)

  • Index Cond: (case_id = c.id)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using geolocation_localization_context_geolocation_id_idx on geolocation_localization_context_t glc (cost=0.43..0.61 rows=1 width=16) (never executed)

  • Index Cond: (g.id = geolocation_id)
  • Filter: ((context_type)::text = 'TARGET'::text)
30. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_case_actor_authority on case_actor_authority_t actors1_ (cost=0.43..8.45 rows=1 width=16) (never executed)

  • Index Cond: ((case_id = c.id) AND (authority_abstract_id = 4))
  • Heap Fetches: 0
Planning time : 19.079 ms