Untitled

gis=> explain analyze select osm_id, way from (select distinct on (p.way) p.osm_id, p.way as way,l.highway as int_tc_type
gis(> from planet_osm_point p
gis(> join planet_osm_line l
gis(> on ST_DWithin(p.way,l.way,0.1)
gis(> join (values ('tertiary',1),
gis(>              ('unclassified',2),
gis(>              ('residential',3),
gis(>              ('living_street',4),
gis(>              ('service',5)
gis(> ) as v (type,prio)
gis(> on v.type=l.highway
gis(> where p.highway='turning_circle'
gis(> order by p.way,v.prio) as foo where way && transform(setSRID('BOX3D(7.857107 48.111101, 7.860465 48.112841)'::box3d,4326),900913)
gis-> ;
                                                                                                                                                QUERY PLAN                                                                                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan foo  (cost=1005.21..1005.23 rows=1 width=36) (actual time=7.809..7.815 rows=1 loops=1)
   ->  Unique  (cost=1005.21..1005.22 rows=1 width=118) (actual time=7.806..7.811 rows=1 loops=1)
         ->  Sort  (cost=1005.21..1005.21 rows=1 width=118) (actual time=7.803..7.805 rows=2 loops=1)
               Sort Key: p.way, "*VALUES*".column2
               Sort Method:  quicksort  Memory: 25kB
               ->  Nested Loop  (cost=0.00..1005.20 rows=1 width=118) (actual time=1.339..7.787 rows=2 loops=1)
                     Join Filter: (l.highway = "*VALUES*".column1)
                     ->  Nested Loop  (cost=0.00..1005.07 rows=1 width=114) (actual time=1.313..7.753 rows=3 loops=1)
                           Join Filter: ((p.way && st_expand(l.way, 0.1::double precision)) AND (st_distance(p.way, l.way) < 0.1::double precision))
                           ->  Index Scan using new_point_index on planet_osm_point p  (cost=0.00..14.72 rows=1 width=104) (actual time=1.232..1.446 rows=1 loops=1)
                                 Index Cond: (way && '010300002031BF0D000100000005000000AA72FA4C32B12A418EE39D81CB5D5741AA72FA4C32B12A4141304908145E5741A63022EC1DB42A4141304908145E5741A63022EC1DB42A418EE39D81CB5D5741AA72FA4C32B12A418EE39D81CB5D5741'::geometry)
                                 Filter: ((way && '010300002031BF0D000100000005000000AA72FA4C32B12A418EE39D81CB5D5741AA72FA4C32B12A4141304908145E5741A63022EC1DB42A4141304908145E5741A63022EC1DB42A418EE39D81CB5D5741AA72FA4C32B12A418EE39D81CB5D5741'::geometry) AND (highway = 'turning_circle'::text))
                           ->  Index Scan using new_line_index on planet_osm_line l  (cost=0.00..984.48 rows=235 width=1249) (actual time=0.057..4.493 rows=41 loops=1)
                                 Index Cond: (l.way && st_expand(p.way, 0.1::double precision))
                                 Filter: (l.way && st_expand(p.way, 0.1::double precision))
                     ->  Values Scan on "*VALUES*"  (cost=0.00..0.06 rows=5 width=36) (actual time=0.002..0.006 rows=5 loops=3)
 Total runtime: 7.945 ms
(17 rows)