explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 35bP

Settings
# exclusive inclusive rows x rows loops node
1. 0.115 1,198.565 ↑ 1.0 1 1

Aggregate (cost=267,384.53..267,384.54 rows=1 width=8) (actual time=1,198.565..1,198.565 rows=1 loops=1)

2. 1.155 1,198.450 ↑ 8.5 1,338 1

Nested Loop (cost=137,090.07..267,356.10 rows=11,374 width=8) (actual time=1,154.157..1,198.450 rows=1,338 loops=1)

3. 1.048 1,154.295 ↑ 14.4 1,720 1

HashAggregate (cost=137,089.65..137,337.29 rows=24,764 width=8) (actual time=1,153.923..1,154.295 rows=1,720 loops=1)

  • Group Key: geolocatio2_.geolocation_id
4. 97.535 1,153.247 ↑ 7.4 3,337 1

Hash Join (cost=97,102.56..137,027.74 rows=24,764 width=8) (actual time=919.668..1,153.247 rows=3,337 loops=1)

  • Hash Cond: (geolocatio2_.address_id = address3_.id)
5. 136.391 136.391 ↑ 1.0 1,553,955 1

Seq Scan on geolocation_address_context_t geolocatio2_ (cost=0.00..33,850.03 rows=1,554,003 width=16) (actual time=0.003..136.391 rows=1,553,955 loops=1)

6. 1.145 919.321 ↑ 8.0 5,205 1

Hash (cost=96,583.39..96,583.39 rows=41,533 width=8) (actual time=919.321..919.321 rows=5,205 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 716kB
7. 46.187 918.176 ↑ 8.0 5,205 1

Bitmap Heap Scan on address_t address3_ (cost=3,870.34..96,583.39 rows=41,533 width=8) (actual time=872.374..918.176 rows=5,205 loops=1)

  • Recheck Cond: ((f_unaccent(upper((sortable_address_fr)::text)) ~~ '%ROME%'::text) OR (f_unaccent(upper((sortable_address_nl)::text)) ~~ '%ROMA%'::text))
  • Heap Blocks: exact=2829
8. 0.001 871.989 ↓ 0.0 0 1

BitmapOr (cost=3,870.34..3,870.34 rows=41,700 width=0) (actual time=871.989..871.989 rows=0 loops=1)

9. 443.132 443.132 ↑ 5.2 4,003 1

Bitmap Index Scan on sortable_address_fr_upper_gist_idx (cost=0.00..1,956.79 rows=20,850 width=0) (actual time=443.132..443.132 rows=4,003 loops=1)

  • Index Cond: (f_unaccent(upper((sortable_address_fr)::text)) ~~ '%ROME%'::text)
10. 428.856 428.856 ↑ 17.3 1,203 1

Bitmap Index Scan on sortable_address_nl_upper_gist_idx (cost=0.00..1,892.79 rows=20,850 width=0) (actual time=428.856..428.856 rows=1,203 loops=1)

  • Index Cond: (f_unaccent(upper((sortable_address_nl)::text)) ~~ '%ROMA%'::text)
11. 20.698 43.000 ↑ 1.0 1 1,720

Index Scan using case_geolocation_id_idx on case_t case0_ (cost=0.42..5.24 rows=1 width=16) (actual time=0.025..0.025 rows=1 loops=1,720)

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

SubPlan (for Index Scan)

13. 22.302 22.302 ↑ 3.0 1 1,593

Index Scan using case_actor_authority_case_idx on case_actor_authority_t actors1_ (cost=0.43..8.65 rows=3 width=8) (actual time=0.014..0.014 rows=1 loops=1,593)

  • Index Cond: (case0_.id = case_id)
Planning time : 0.789 ms
Execution time : 1,199.086 ms