explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Q79

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 33,896.812 ↑ 3.6 5 1

Limit (cost=15,723,750.64..15,723,750.68 rows=18 width=16) (actual time=33,896.812..33,896.812 rows=5 loops=1)

2. 0.041 33,896.811 ↑ 3.6 5 1

Sort (cost=15,723,750.64..15,723,750.68 rows=18 width=16) (actual time=33,896.810..33,896.811 rows=5 loops=1)

  • Sort Key: c.reception_date
  • Sort Method: quicksort Memory: 25kB
3. 0.003 33,896.770 ↑ 3.6 5 1

Nested Loop (cost=2.56..15,723,750.26 rows=18 width=16) (actual time=33,895.901..33,896.770 rows=5 loops=1)

4. 0.029 0.029 ↑ 1.0 1 1

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

  • Filter: (id = 4)
  • Rows Removed by Filter: 67
5. 0.225 33,896.738 ↑ 3.6 5 1

Nested Loop (cost=2.56..15,723,748.23 rows=18 width=24) (actual time=33,895.875..33,896.738 rows=5 loops=1)

  • Join Filter: (p.definition_id = pdt.id)
  • Rows Removed by Join Filter: 1470
6. 0.718 0.718 ↓ 1.0 295 1

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

7. 0.000 33,895.795 ↑ 3.6 5 295

Materialize (cost=2.56..15,723,662.52 rows=18 width=32) (actual time=110.356..114.901 rows=5 loops=295)

8. 1.248 33,895.796 ↑ 3.6 5 1

Nested Loop (cost=2.56..15,723,662.43 rows=18 width=32) (actual time=32,555.019..33,895.796 rows=5 loops=1)

9. 1.224 33,815.016 ↓ 9.6 1,348 1

Nested Loop Left Join (cost=2.13..15,722,482.38 rows=140 width=24) (actual time=9,349.511..33,815.016 rows=1,348 loops=1)

10. 2.465 33,525.320 ↓ 9.6 1,348 1

Nested Loop Left Join (cost=1.70..15,722,395.73 rows=140 width=32) (actual time=9,348.128..33,525.320 rows=1,348 loops=1)

11. 2.303 33,278.867 ↓ 9.6 1,348 1

Nested Loop (cost=1.28..15,721,220.38 rows=140 width=24) (actual time=9,346.176..33,278.867 rows=1,348 loops=1)

12. 1.266 32,849.248 ↓ 9.6 1,348 1

Nested Loop (cost=0.85..15,721,136.92 rows=140 width=24) (actual time=9,344.733..32,849.248 rows=1,348 loops=1)

13. 5,328.802 32,512.330 ↓ 9.6 1,348 1

Index Scan using case_unique_business_id_idx on case_t c (cost=0.42..15,719,977.57 rows=140 width=48) (actual time=9,343.179..32,512.330 rows=1,348 loops=1)

  • Index Cond: ((case_type)::text = 'URBANISM'::text)
  • Filter: ((NOT a_new_version_exists) AND (f_unaccent(upper((SubPlan 1))) ~~ '%THOMAS%'::text))
  • Rows Removed by Filter: 437869
14.          

SubPlan (forIndex Scan)

15. 422.050 27,183.528 ↑ 1.0 1 438,444

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

16. 211.025 26,306.640 ↑ 1.0 1 438,444

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

17. 255.748 25,868.196 ↑ 1.0 1 438,444

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

18. 24,114.420 24,114.420 ↑ 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.039..0.055 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
19. 1,498.028 1,498.028 ↑ 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.004..0.004 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
20. 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)
21. 454.838 454.838 ↑ 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.002..0.002 rows=1 loops=227,419)

  • Index Cond: (id = up.abstract_person_id)
22. 335.652 335.652 ↑ 1.0 1 1,348

Index Scan using pk_configuration_t on configuration_t config (cost=0.42..8.27 rows=1 width=16) (actual time=0.249..0.249 rows=1 loops=1,348)

  • Index Cond: (id = c.configuration_id)
23. 427.316 427.316 ↑ 1.0 1 1,348

Index Scan using pk_phase_t on phase_t p (cost=0.43..0.59 rows=1 width=24) (actual time=0.317..0.317 rows=1 loops=1,348)

  • Index Cond: (id = config.current_phase_id)
24. 243.988 243.988 ↑ 1.0 1 1,348

Index Scan using geolocation__case_idx on geolocation_t g (cost=0.42..8.39 rows=1 width=24) (actual time=0.181..0.181 rows=1 loops=1,348)

  • Index Cond: (case_id = c.id)
25. 288.472 288.472 ↑ 1.0 1 1,348

Index Scan using geolocation_localization_context_geolocation_id_idx on geolocation_localization_context_t glc (cost=0.43..0.61 rows=1 width=16) (actual time=0.211..0.214 rows=1 loops=1,348)

  • Index Cond: (g.id = geolocation_id)
  • Filter: ((context_type)::text = 'TARGET'::text)
  • Rows Removed by Filter: 2
26. 79.532 79.532 ↓ 0.0 0 1,348

Index Only Scan using pk_case_actor_authority on case_actor_authority_t actors1_ (cost=0.43..8.42 rows=1 width=16) (actual time=0.059..0.059 rows=0 loops=1,348)

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