explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hx0D

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 469.873 ↓ 6.2 25 1

Limit (cost=36,303.09..36,303.10 rows=4 width=16) (actual time=469.865..469.873 rows=25 loops=1)

2. 0.024 469.866 ↓ 6.2 25 1

Sort (cost=36,303.09..36,303.10 rows=4 width=16) (actual time=469.865..469.866 rows=25 loops=1)

  • Sort Key: case0_.reception_date DESC, case0_.id DESC
  • Sort Method: quicksort Memory: 27kB
3. 0.000 469.842 ↓ 11.5 46 1

Nested Loop Semi Join (cost=33,849.86..36,303.05 rows=4 width=16) (actual time=457.071..469.842 rows=46 loops=1)

4. 0.087 464.393 ↓ 60.6 424 1

Nested Loop (cost=33,849.43..34,057.15 rows=7 width=16) (actual time=456.959..464.393 rows=424 loops=1)

5. 0.107 457.121 ↓ 29.9 479 1

Unique (cost=33,849.00..33,849.08 rows=16 width=8) (actual time=456.914..457.121 rows=479 loops=1)

6. 0.286 457.014 ↓ 46.8 749 1

Sort (cost=33,849.00..33,849.04 rows=16 width=8) (actual time=456.914..457.014 rows=749 loops=1)

  • Sort Key: geolocatio3_.geolocation_id
  • Sort Method: quicksort Memory: 60kB
7. 0.982 456.728 ↓ 46.8 749 1

Nested Loop (cost=475.92..33,848.68 rows=16 width=8) (actual time=444.381..456.728 rows=749 loops=1)

8. 7.104 451.234 ↓ 41.8 1,128 1

Bitmap Heap Scan on address_t address4_ (cost=475.49..33,620.40 rows=27 width=8) (actual time=444.281..451.234 rows=1,128 loops=1)

  • Recheck Cond: ((f_unaccent(upper((street_name_french)::text)) ~~ '%ROUGE%'::text) OR (f_unaccent(upper((street_name_dutch)::text)) ~~ '%ROUGE%'::text))
  • Heap Blocks: exact=732
9. 0.001 444.130 ↓ 0.0 0 1

BitmapOr (cost=475.49..475.49 rows=8,354 width=0) (actual time=444.130..444.130 rows=0 loops=1)

10. 246.028 246.028 ↑ 3.7 1,128 1

Bitmap Index Scan on address_fr_upper_gist_idx (cost=0.00..243.74 rows=4,177 width=0) (actual time=246.028..246.028 rows=1,128 loops=1)

  • Index Cond: (f_unaccent(upper((street_name_french)::text)) ~~ '%ROUGE%'::text)
11. 198.101 198.101 ↑ 11.9 352 1

Bitmap Index Scan on address_nl_upper_gist_idx (cost=0.00..231.74 rows=4,177 width=0) (actual time=198.101..198.101 rows=352 loops=1)

  • Index Cond: (f_unaccent(upper((street_name_dutch)::text)) ~~ '%ROUGE%'::text)
12. 4.512 4.512 ↑ 1.0 1 1,128

Index Scan using geolocation_address_context_t_address_idx on geolocation_address_context_t geolocatio3_ (cost=0.43..8.45 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1,128)

  • Index Cond: (address_id = address4_.id)
13. 4.077 7.185 ↑ 1.0 1 479

Index Scan using case_geolocation_id_idx on case_t case0_ (cost=0.42..12.98 rows=1 width=24) (actual time=0.015..0.015 rows=1 loops=479)

  • Index Cond: (geolocation_id = geolocatio3_.geolocation_id)
  • Filter: ((NOT a_new_version_exists) AND ((case_type)::text <> 'UTG'::text) AND (SubPlan 1))
  • Rows Removed by Filter: 0
14.          

SubPlan (for Index Scan)

15. 3.108 3.108 ↑ 1.5 2 444

Index Scan using case_actor_authority_case_idx on case_actor_authority_t actors1_ (cost=0.43..8.64 rows=3 width=8) (actual time=0.007..0.007 rows=2 loops=444)

  • Index Cond: (case0_.id = case_id)
16. 5.512 5.512 ↓ 0.0 0 424

Index Scan using reference_case_idx on reference_t reference2_ (cost=0.43..551.92 rows=876 width=8) (actual time=0.013..0.013 rows=0 loops=424)

  • Index Cond: (external_id = case0_.id)
  • Filter: ((reference_type_id <> 66) AND (upper((reference)::text) ~~ '%PFD%'::text))
  • Rows Removed by Filter: 6
Planning time : 0.728 ms