gistfile1.txt

EXPLAIN ANALYZE SELECT places.*,
 (SELECT COUNT(*) FROM vqrs WHERE vqrs.place_id = places.id) vqr_count,
 ST_Distance(location, 'POINT(-104.987389 39.741727)')/1000/1.609344 d,
 rtrim(split_part(ST_AsText(places.location), ' ', 2), ')') lat,
 ltrim(split_part(ST_AsText(places.location), ' ', 1), 'POINT(') long
 FROM places
 WHERE places.region = 'CO' AND
 places.locality = 'Denver' AND
 ST_DWithin(location, 'POINT(-104.987389 39.741727)', 845.344, false)
 ORDER BY d ASC
 LIMIT 20
;



                                                                                                                                                                     QUERY PLAN                                                                                                                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=17.22..17.23 rows=1 width=383) (actual time=305.906..305.932 rows=20 loops=1)
   ->  Sort  (cost=17.22..17.23 rows=1 width=383) (actual time=305.903..305.917 rows=20 loops=1)
         Sort Key: (((_st_distance(places.location, '0101000020E610000087DBA161313F5AC0B4C70BE9F0DE4340'::geography, 0::double precision, true) / 1000::double precision) / 1.609344::double precision))
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Index Scan using index_places_on_location on places  (cost=0.00..17.21 rows=1 width=383) (actual time=0.295..284.988 rows=7781 loops=1)
               Index Cond: (location && '0101000020E610000087DBA161313F5AC0B4C70BE9F0DE4340'::geography)
               Filter: (((region)::text = 'CO'::text) AND ((locality)::text = 'Denver'::text) AND ('0101000020E610000087DBA161313F5AC0B4C70BE9F0DE4340'::geography && _st_expand(location, 845.344::double precision)) AND _st_dwithin(location, '0101000020E610000087DBA161313F5AC0B4C70BE9F0DE4340'::geography, 845.344::double precision, false))
               SubPlan 1
                 ->  Aggregate  (cost=8.27..8.28 rows=1 width=0) (actual time=0.004..0.005 rows=1 loops=7781)
                       ->  Index Scan using index_vqrs_on_place_id on vqrs  (cost=0.00..8.27 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=7781)
                             Index Cond: (place_id = places.id)
 Total runtime: 306.041 ms
(12 rows)

(END)