explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TMLO : Postgres 12.1 Hash Join

Settings
# exclusive inclusive rows x rows loops node
1. 1.909 3,393.934 ↓ 63.2 2,779 1

Subquery Scan on roads_text_name (cost=6,437.23..6,448.78 rows=44 width=160) (actual time=3,391.613..3,393.934 rows=2,779 loops=1)

  • Buffers: shared hit=9,077
2. 8.812 3,392.025 ↓ 63.2 2,779 1

Sort (cost=6,437.23..6,437.34 rows=44 width=517) (actual time=3,391.604..3,392.025 rows=2,779 loops=1)

  • Sort Key: planet_osm_hstore_line.z_order DESC, (COALESCE(planet_osm_hstore_line.layer, 0)), (length((planet_osm_hstore_line.tags -> 'name'::text))) DESC, (osml10n_get_streetname_from_tags(planet_osm_hstore_line.tags, true, false, ' - '::text, 'de'::text, planet_osm_hstore_line.way, NULL::text)) DESC, planet_osm_hstore_line.osm_id DESC
  • Sort Method: quicksort Memory: 894kB
  • Buffers: shared hit=9,077
3. 3,364.036 3,383.213 ↓ 63.2 2,779 1

Hash Join (cost=1..6,436.03 rows=44 width=517) (actual time=5.532..3,383.213 rows=2,779 loops=1)

  • Buffers: shared hit=9,077
4. 19.147 19.147 ↓ 5.7 2,779 1

Index Scan using planet_osm_hstore_line_way_idx on planet_osm_hstore_line planet_osm_hstore_line (cost=0.55..6,419.38 rows=491 width=352) (actual time=0.423..19.147 rows=2,779 loops=1)

  • Index Cond: (planet_osm_hstore_line.way && '01030000A0110F00000100000005000000AF78D2F4ECA632411CD92E58CD2E58410000000000000000AF78D2F4ECA63241E4EED5138D3958410000000000000000D3CF6EE3EBD13241E4EED5138D3958410000000000000000D3CF6EE3EBD132411CD92E58CD2E58410000000000000000AF78D2F4ECA632411CD92E58CD2E58410000000000000000'::geometry)
  • Filter: ((((planet_osm_hstore_line.tags -> 'name'::text) IS NOT NULL) OR ((planet_osm_hstore_line.tags -> 'oneway'::text) = ANY ('{yes,-1}'::text[])) OR ((planet_osm_hstore_line.tags -> 'junction'::text) = 'roundabout'::text)) AND ((planet_osm_hstore_line.tags -> 'highway'::text) = ANY ('{motorway,motorway_link,trunk,trunk_link,primary,primary_link,secondary,secondary_link,tertiary,tertiary_link,residential,unclassified,road,service,pedestrian,raceway,living_street,construction}'::text[])))
  • Buffers: shared hit=9,077
5. 0.013 0.030 ↑ 1.0 18 1

Hash (cost=0.23..0.23 rows=18 width=32) (actual time=0.029..0.03 rows=18 loops=1)

6. 0.017 0.017 ↑ 1.0 18 1

Values Scan (cost=0..0.23 rows=18 width=32) (actual time=0.003..0.017 rows=18 loops=1)

Planning time : 1.409 ms
Execution time : 3,394.189 ms