explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BsO7 : Postgres 11.4 Hash Join

Settings
# exclusive inclusive rows x rows loops node
1. 1.136 140.347 ↓ 33.7 2,731 1

Subquery Scan on roads_text_name (cost=11,643.73..11,646.77 rows=81 width=160) (actual time=139.066..140.347 rows=2,731 loops=1)

  • Buffers: shared hit=5,972
2. 6.509 139.211 ↓ 33.7 2,731 1

Sort (cost=11,643.73..11,643.93 rows=81 width=480) (actual time=139.056..139.211 rows=2,731 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: 885kB
  • Buffers: shared hit=5,972
3. 120.509 132.702 ↓ 33.7 2,731 1

Hash Join (cost=0.87..11,641.16 rows=81 width=480) (actual time=0.758..132.702 rows=2,731 loops=1)

  • Buffers: shared hit=5,972
4. 12.161 12.161 ↓ 3.0 2,731 1

Index Scan using planet_osm_hstore_line_way_idx on planet_osm_hstore_line planet_osm_hstore_line (cost=0.42..11,610.92 rows=896 width=327) (actual time=0.292..12.161 rows=2,731 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=5,972
5. 0.013 0.032 ↑ 1.0 18 1

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

6. 0.019 0.019 ↑ 1.0 18 1

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

Planning time : 1.237 ms
Execution time : 140.543 ms