explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KPsR : production

Settings
# exclusive inclusive rows x rows loops node
1. 0.036 28.003 ↓ 21.0 21 1

Sort (cost=7,040.81..7,040.82 rows=1 width=115) (actual time=28.001..28.003 rows=21 loops=1)

  • Sort Key: a.street, ((COALESCE(NULLIF((a.street_number)::text, ''::text), '-1'::text))::integer), a.letter, a.postalcode, a.locality
  • Sort Method: quicksort Memory: 30kB
2. 0.068 27.967 ↓ 21.0 21 1

HashAggregate (cost=7,040.78..7,040.80 rows=1 width=115) (actual time=27.957..27.967 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, adm1.adm1_code, adm2.adm2, adm2.adm2_code, a.x_lon, a.y_lat
3. 0.029 27.899 ↓ 21.0 21 1

Nested Loop Left Join (cost=82.61..7,040.75 rows=1 width=115) (actual time=2.453..27.899 rows=21 loops=1)

4. 0.098 27.786 ↓ 21.0 21 1

Nested Loop Left Join (cost=82.46..7,038.37 rows=1 width=107) (actual time=2.421..27.786 rows=21 loops=1)

  • Join Filter: (adm1.adm1_code = a.adm1_code)
  • Rows Removed by Join Filter: 420
5. 26.677 27.646 ↓ 21.0 21 1

Bitmap Heap Scan on valid_addresses_se a (cost=82.46..7,036.90 rows=1 width=97) (actual time=2.412..27.646 rows=21 loops=1)

  • Recheck Cond: (((locality_ix)::text = 'bagarmossen'::text) OR ((postalcode)::text ~~ '128%'::text))
  • Filter: ((((locality_ix)::text = 'bagarmossen'::text) OR ((postalcode)::text ~~ '128%'::text)) AND ((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: 6048
  • Heap Blocks: exact=5946
6. 0.000 0.969 ↓ 0.0 0 1

BitmapOr (cost=82.46..82.46 rows=6,351 width=0) (actual time=0.969..0.969 rows=0 loops=1)

7. 0.154 0.154 ↑ 1.2 1,342 1

Bitmap Index Scan on valid_addresses_se_locality_ix_like (cost=0.00..18.77 rows=1,565 width=0) (actual time=0.154..0.154 rows=1,342 loops=1)

  • Index Cond: ((locality_ix)::text = 'bagarmossen'::text)
8. 0.815 0.815 ↓ 1.3 6,069 1

Bitmap Index Scan on valid_addresses_se_postalcode_ix_like (cost=0.00..63.69 rows=4,786 width=0) (actual time=0.815..0.815 rows=6,069 loops=1)

  • Index Cond: (((postalcode)::text ~>=~ '128'::text) AND ((postalcode)::text ~<~ '129'::text))
9. 0.042 0.042 ↑ 1.0 21 21

Seq Scan on valid_adm1_se adm1 (cost=0.00..1.21 rows=21 width=13) (actual time=0.001..0.002 rows=21 loops=21)

10. 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..2.37 rows=1 width=13) (actual time=0.004..0.004 rows=1 loops=21)

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