explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EUt6

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 66.134 ↓ 1.6 21 1

Group (cost=58,921.31..58,922.48 rows=13 width=406) (actual time=66.121..66.134 rows=21 loops=1)

  • Group Key: a.street, a.street_number, a.extra_number, a.letter, a.premise, a.postalcode, a.locality, a.address_type, a.uuid, adm1.adm1, (CASE WHEN (length((adm1.adm1_code)::text) = 1) THEN concat('0', (adm1.adm1_code)::character(1)) ELSE (adm1.adm1_code)::text END), adm2.adm2, (CASE WHEN (length((adm2.adm2_code)::text) = 1) THEN concat('0', (adm2.adm2_code)::character(1)) ELSE (adm2.adm2_code)::text END), a.x_lon, a.y_lat
2. 0.056 66.121 ↓ 1.6 21 1

Sort (cost=58,921.31..58,921.34 rows=13 width=406) (actual time=66.119..66.121 rows=21 loops=1)

  • Sort Key: a.street, a.street_number, a.extra_number, a.letter, a.premise, a.postalcode, a.locality, a.address_type, a.uuid, adm1.adm1, (CASE WHEN (length((adm1.adm1_code)::text) = 1) THEN concat('0', (adm1.adm1_code)::character(1)) ELSE (adm1.adm1_code)::text END), adm2.adm2, (CASE WHEN (length((adm2.adm2_code)::text) = 1) THEN concat('0', (adm2.adm2_code)::character(1)) ELSE (adm2.adm2_code)::text END), a.x_lon, a.y_lat
  • Sort Method: quicksort Memory: 30kB
3. 0.133 66.065 ↓ 1.6 21 1

Nested Loop Left Join (cost=247.85..58,921.07 rows=13 width=406) (actual time=11.100..66.065 rows=21 loops=1)

4. 0.146 65.848 ↓ 2.3 21 1

Hash Left Join (cost=247.71..58,878.84 rows=9 width=342) (actual time=11.083..65.848 rows=21 loops=1)

  • Hash Cond: (a.adm1_code = adm1.adm1_code)
5. 57.231 65.682 ↓ 2.3 21 1

Bitmap Heap Scan on valid_addresses_se a (cost=246.23..58,877.33 rows=9 width=124) (actual time=11.034..65.682 rows=21 loops=1)

  • Recheck Cond: (((locality_ix)::text = 'bagarmossen'::text) OR ((postalcode)::text ~~ '123%'::text))
  • Rows Removed by Index Recheck: 14
  • Filter: (((address_type = 'NA'::bpchar) OR (address_type = 'NB'::bpchar) OR (address_type = 'NC'::bpchar)) AND (((street_ix)::text = 'xvoxnegraend'::text) OR (((street)::text % 'XVoxnegränd'::text) AND ((levenshtein('xvoxnegränd'::text, lower((street)::text), 1, 1, 2) <= 3) OR (similarity('xvoxnegränd'::text, lower((street)::text)) >= '0.5'::double precision)))))
  • Rows Removed by Filter: 6495
  • Heap Blocks: exact=6093
6. 0.002 8.451 ↓ 0.0 0 1

BitmapOr (cost=246.23..246.23 rows=22,107 width=0) (actual time=8.451..8.451 rows=0 loops=1)

7. 0.258 0.258 ↑ 1.2 1,342 1

Bitmap Index Scan on valid_addresses_se_locality_ix_like (cost=0.00..36.18 rows=1,567 width=0) (actual time=0.257..0.258 rows=1,342 loops=1)

  • Index Cond: ((locality_ix)::text = 'bagarmossen'::text)
8. 8.191 8.191 ↑ 4.0 5,188 1

Bitmap Index Scan on valid_addresses_se_postalcode_gin (cost=0.00..210.05 rows=20,540 width=0) (actual time=8.191..8.191 rows=5,188 loops=1)

  • Index Cond: ((postalcode)::text ~~ '123%'::text)
9. 0.007 0.020 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=222) (actual time=0.020..0.020 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.013 0.013 ↑ 1.0 21 1

Seq Scan on valid_adm1_se adm1 (cost=0.00..1.21 rows=21 width=222) (actual time=0.009..0.013 rows=21 loops=1)

11. 0.084 0.084 ↑ 1.0 1 21

Index Scan using valid_adm2_se_adm2_code_ix on valid_adm2_se adm2 (cost=0.15..4.61 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=21)

  • Index Cond: (adm2_code = a.adm2_code)
Planning time : 4.748 ms