explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jcik : o

Settings
# exclusive inclusive rows x rows loops node
1. 0.056 432.540 ↑ 5.0 1 1

Sort (cost=33,429.68..33,429.69 rows=5 width=336) (actual time=432.540..432.540 rows=1 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: 25kB
2. 0.014 432.484 ↑ 5.0 1 1

Group (cost=33,429.39..33,429.62 rows=5 width=336) (actual time=432.483..432.484 rows=1 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.046 432.470 ↑ 5.0 1 1

Sort (cost=33,429.39..33,429.40 rows=5 width=332) (actual time=432.470..432.470 rows=1 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, adm1.adm1_code, adm2.adm2, adm2.adm2_code, a.x_lon, a.y_lat
  • Sort Method: quicksort Memory: 25kB
4. 0.006 432.424 ↑ 5.0 1 1

Nested Loop Left Join (cost=9,146.24..33,429.33 rows=5 width=332) (actual time=422.900..432.424 rows=1 loops=1)

5. 0.026 432.385 ↑ 5.0 1 1

Hash Left Join (cost=9,146.09..33,400.45 rows=5 width=324) (actual time=422.863..432.385 rows=1 loops=1)

  • Hash Cond: (a.adm1_code = adm1.adm1_code)
6. 324.331 432.337 ↑ 5.0 1 1

Bitmap Heap Scan on valid_addresses_se a (cost=9,144.62..33,398.91 rows=5 width=97) (actual time=422.816..432.337 rows=1 loops=1)

  • Recheck Cond: (((locality_ix)::text = 'hagfors'::text) AND ((address_type = 'BA'::bpchar) OR (address_type = 'BB'::bpchar) OR (address_type = 'BC'::bpchar) OR (address_type = 'BE'::bpchar)))
  • Rows Removed by Index Recheck: 1831
  • Filter: ((((street_number)::text = ''::text) OR (street_number IS NULL) OR ((street_number)::text = '36612'::text)) AND (((postalcode)::text = '68329'::text) OR ((street_number)::text = ''::text) OR (street_number IS NULL) OR ((street_number)::text = '36612'::text)))
  • Rows Removed by Filter: 67777
  • Heap Blocks: exact=54014
7. 3.749 108.006 ↓ 0.0 0 1

BitmapAnd (cost=9,144.62..9,144.62 rows=7,505 width=0) (actual time=108.006..108.006 rows=0 loops=1)

8. 31.026 31.026 ↑ 1.1 71,315 1

Bitmap Index Scan on valid_addresses_se_locality_ix_like (cost=0.00..1,595.48 rows=75,606 width=0) (actual time=31.026..31.026 rows=71,315 loops=1)

  • Index Cond: ((locality_ix)::text = 'hagfors'::text)
9. 0.004 73.231 ↓ 0.0 0 1

BitmapOr (cost=7,548.89..7,548.89 rows=407,887 width=0) (actual time=73.230..73.231 rows=0 loops=1)

10. 0.046 0.046 ↓ 0.0 0 1

Bitmap Index Scan on valid_addresses_se_address_type_ix (cost=0.00..4.44 rows=1 width=0) (actual time=0.046..0.046 rows=0 loops=1)

  • Index Cond: (address_type = 'BA'::bpchar)
11. 72.404 72.404 ↑ 1.0 394,103 1

Bitmap Index Scan on valid_addresses_se_address_type_ix (cost=0.00..7,446.60 rows=402,956 width=0) (actual time=72.404..72.404 rows=394,103 loops=1)

  • Index Cond: (address_type = 'BB'::bpchar)
12. 0.747 0.747 ↓ 1.1 5,651 1

Bitmap Index Scan on valid_addresses_se_address_type_ix (cost=0.00..93.41 rows=4,931 width=0) (actual time=0.747..0.747 rows=5,651 loops=1)

  • Index Cond: (address_type = 'BC'::bpchar)
13. 0.030 0.030 ↓ 0.0 0 1

Bitmap Index Scan on valid_addresses_se_address_type_ix (cost=0.00..4.44 rows=1 width=0) (actual time=0.030..0.030 rows=0 loops=1)

  • Index Cond: (address_type = 'BE'::bpchar)
14. 0.010 0.022 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=230) (actual time=0.022..0.022 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.012 0.012 ↑ 1.0 21 1

Seq Scan on valid_adm1_se adm1 (cost=0.00..1.21 rows=21 width=230) (actual time=0.008..0.012 rows=21 loops=1)

16. 0.033 0.033 ↑ 1.0 1 1

Index Scan using valid_adm2_se_adm2_code_ix on valid_adm2_se adm2 (cost=0.15..5.76 rows=1 width=13) (actual time=0.032..0.033 rows=1 loops=1)

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