explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yGLc

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 2,388.419 ↓ 0.0 0 1

Limit (cost=8,067.47..8,067.49 rows=5 width=16) (actual time=2,388.419..2,388.419 rows=0 loops=1)

2. 0.004 2,388.418 ↓ 0.0 0 1

Sort (cost=8,067.47..8,067.49 rows=5 width=16) (actual time=2,388.418..2,388.418 rows=0 loops=1)

  • Sort Key: case0_.reception_date DESC, case0_.id DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.000 2,388.414 ↓ 0.0 0 1

Nested Loop Semi Join (cost=4,924.15..8,067.41 rows=5 width=16) (actual time=2,388.414..2,388.414 rows=0 loops=1)

4. 0.001 2,388.414 ↓ 0.0 0 1

Nested Loop (cost=4,923.72..7,832.31 rows=113 width=16) (actual time=2,388.414..2,388.414 rows=0 loops=1)

5. 0.002 2,388.413 ↓ 0.0 0 1

HashAggregate (cost=4,923.29..4,925.56 rows=227 width=8) (actual time=2,388.413..2,388.413 rows=0 loops=1)

  • Group Key: geolocatio3_.geolocation_id
6. 0.001 2,388.411 ↓ 0.0 0 1

Nested Loop (cost=92.31..4,922.73 rows=227 width=8) (actual time=2,388.411..2,388.411 rows=0 loops=1)

7. 99.323 2,388.410 ↓ 0.0 0 1

Bitmap Heap Scan on address_t address4_ (cost=91.88..1,777.37 rows=381 width=8) (actual time=2,388.410..2,388.410 rows=0 loops=1)

  • Recheck Cond: ((f_unaccent(upper((street_name_french)::text)) ~~ '%Prosper%'::text) OR (f_unaccent(upper((street_name_dutch)::text)) ~~ '%Prosper%'::text))
  • Rows Removed by Index Recheck: 966
  • Heap Blocks: exact=2,809
8. 0.002 2,289.087 ↓ 0.0 0 1

BitmapOr (cost=91.88..91.88 rows=381 width=0) (actual time=2,289.087..2,289.087 rows=0 loops=1)

9. 1,094.162 1,094.162 ↓ 15.5 2,894 1

Bitmap Index Scan on address_fr_upper_gist_idx (cost=0.00..45.82 rows=187 width=0) (actual time=1,094.162..1,094.162 rows=2,894 loops=1)

  • Index Cond: (f_unaccent(upper((street_name_french)::text)) ~~ '%Prosper%'::text)
10. 1,194.923 1,194.923 ↓ 12.0 2,332 1

Bitmap Index Scan on address_nl_upper_gist_idx (cost=0.00..45.88 rows=195 width=0) (actual time=1,194.923..1,194.923 rows=2,332 loops=1)

  • Index Cond: (f_unaccent(upper((street_name_dutch)::text)) ~~ '%Prosper%'::text)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using geolocation_address_context_t_address_idx on geolocation_address_context_t geolocatio3_ (cost=0.43..8.25 rows=1 width=16) (never executed)

  • Index Cond: (address_id = address4_.id)
12. 0.000 0.000 ↓ 0.0 0

Index Scan using case_geolocation_id_idx on case_t case0_ (cost=0.42..12.79 rows=1 width=24) (never executed)

  • Index Cond: (geolocation_id = geolocatio3_.geolocation_id)
  • Filter: ((NOT a_new_version_exists) AND ((case_type)::text <> 'URBANISME'::text) AND (SubPlan 1))
13.          

SubPlan (for Index Scan)

14. 0.000 0.000 ↓ 0.0 0

Index Scan using case_actor_authority_case_idx on case_actor_authority_t actors1_ (cost=0.43..8.66 rows=3 width=8) (never executed)

  • Index Cond: (case0_.id = case_id)
15. 0.000 0.000 ↓ 0.0 0

Index Scan using reference_case_idx on reference_t reference2_ (cost=0.43..2.08 rows=1 width=8) (never executed)

  • Index Cond: (external_id = case0_.id)
  • Filter: ((reference_type_id <> 10035) AND (upper((reference)::text) ~~ '%9767%'::text))
Planning time : 1.131 ms
Execution time : 2,388.821 ms