Spatial indicies

gistfile1.txt

#
# Indexes for units table
#

Indexes:
    "units_pkey" PRIMARY KEY, btree (id)
    "index_units_on_normalization" UNIQUE, btree (normalization)
    "index_units_on_city_id" btree (city_id)
    "index_units_on_coordinates" gist (coordinates)
    "index_units_on_neighborhood_id" btree (neighborhood_id)
    "index_units_on_price" btree (price)
    "index_units_on_zip_code_id" btree (zip_code_id)
    "units_community_id" btree (community_id)


#
# using index - good
#

select units.id from units, cities where (ST_Intersects(cities.polygon, units.coordinates)) and cities.id = 235;

                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..42.85 rows=73 width=4) (actual time=46.055..462.111 rows=4355 loops=1)
   Join Filter: _st_intersects(cities.polygon, units.coordinates)
   ->  Index Scan using cities_pkey on cities  (cost=0.00..8.27 rows=1 width=49005) (actual time=0.049..0.050 rows=1 loops=1)
         Index Cond: (id = 235)
   ->  Index Scan using index_units_on_coordinates on units  (cost=0.00..32.74 rows=7 width=104) (actual time=45.867..366.072 rows=4509 loops=1)
         Index Cond: (cities.polygon && units.coordinates)
 Total runtime: 462.697 ms
(7 rows)



#
# not using index - why?
#

explain analyze SELECT id,community_id,bed,bath,lat,lon,photo_0_id,price,price_max,neighborhood_id FROM "units" WHERE (ST_Point_Inside_Circle(coordinates, -121.44, 38.5691, 0.025));

                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on units  (cost=0.00..170371.35 rows=458703 width=54) (actual time=6.564..1241.207 rows=113 loops=1)
   Filter: st_point_inside_circle(coordinates, (-121.44)::double precision, 38.5691::double precision, 0.025::double precision)
 Total runtime: 1241.271 ms
(3 rows)