explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tn4s

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 6,493.794 ↑ 3.6 5 1

Limit (cost=12,290,960.33..12,290,960.38 rows=18 width=16) (actual time=6,493.794..6,493.794 rows=5 loops=1)

2. 0.020 6,493.793 ↑ 3.6 5 1

Sort (cost=12,290,960.33..12,290,960.38 rows=18 width=16) (actual time=6,493.793..6,493.793 rows=5 loops=1)

  • Sort Key: c.reception_date
  • Sort Method: quicksort Memory: 25kB
3. 0.008 6,493.773 ↑ 3.6 5 1

Nested Loop (cost=2.56..12,290,959.96 rows=18 width=16) (actual time=6,493.537..6,493.773 rows=5 loops=1)

4. 0.028 0.028 ↑ 1.0 1 1

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

  • Filter: (id = 4)
  • Rows Removed by Filter: 67
5. 0.156 6,493.737 ↑ 3.6 5 1

Nested Loop (cost=2.56..12,290,957.93 rows=18 width=24) (actual time=6,493.511..6,493.737 rows=5 loops=1)

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

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

7. 0.120 6,493.540 ↑ 3.6 5 295

Materialize (cost=2.56..12,290,872.21 rows=18 width=32) (actual time=21.261..22.012 rows=5 loops=295)

8. 0.745 6,493.420 ↑ 3.6 5 1

Nested Loop (cost=2.56..12,290,872.12 rows=18 width=32) (actual time=6,272.116..6,493.420 rows=5 loops=1)

9. 0.988 6,487.275 ↓ 10.2 1,350 1

Nested Loop Left Join (cost=2.13..12,289,759.43 rows=132 width=24) (actual time=2,395.531..6,487.275 rows=1,350 loops=1)

10. 0.887 6,478.187 ↓ 10.2 1,350 1

Nested Loop Left Join (cost=1.70..12,289,677.64 rows=132 width=32) (actual time=2,395.513..6,478.187 rows=1,350 loops=1)

11. 0.757 6,471.900 ↓ 10.2 1,350 1

Nested Loop (cost=1.28..12,288,569.29 rows=132 width=24) (actual time=2,395.500..6,471.900 rows=1,350 loops=1)

12. 1.723 6,464.393 ↓ 10.2 1,350 1

Nested Loop (cost=0.85..12,288,490.87 rows=132 width=24) (actual time=2,395.488..6,464.393 rows=1,350 loops=1)

13. 2,497.306 6,457.270 ↓ 9.4 1,350 1

Index Scan using case_unique_business_id_idx on case_t c (cost=0.42..12,287,306.17 rows=143 width=48) (actual time=2,395.474..6,457.270 rows=1,350 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: 439482
14.          

SubPlan (forIndex Scan)

15. 0.000 3,959.964 ↑ 1.0 1 439,996

Nested Loop Left Join (cost=1.56..26.88 rows=1 width=17) (actual time=0.009..0.009 rows=1 loops=439,996)

16. 0.000 3,519.968 ↑ 1.0 1 439,996

Nested Loop Left Join (cost=1.13..20.85 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=439,996)

17. 568.024 3,079.972 ↑ 1.0 1 439,996

Nested Loop (cost=0.85..20.54 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=439,996)

18. 1,759.984 1,759.984 ↑ 1.0 1 439,996

Index Scan using intervenor_case_idx on intervenor_t i (cost=0.43..12.09 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=439,996)

  • 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. 751.964 751.964 ↑ 1.0 1 375,982

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=375,982)

  • Index Cond: (id = i.id)
  • Filter: (on_behalf_of_authority_id = c.managing_authority_id)
  • Rows Removed by Filter: 0
20. 457.476 457.476 ↑ 1.0 1 228,738

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.002 rows=1 loops=228,738)

  • Index Cond: (id = iu.user_id)
21. 457.476 457.476 ↑ 1.0 1 228,738

Index Scan using pk_person_natural_t on person_natural_t p_1 (cost=0.42..6.01 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=228,738)

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

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

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

Index Scan using pk_phase_t on phase_t p (cost=0.43..0.58 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=1,350)

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

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

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

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.006..0.006 rows=1 loops=1,350)

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

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.004..0.004 rows=0 loops=1,350)

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