[postgis-users] points in polygon

[Young Kim]

hello all,

looking to select points in a polygon FAST. Seems like a simple
problem, but could not find good doc anywhere.

Thanks in advance.


BACKGROUND:
points - 8663 points in greater los angeles area.
          - has "gist" index on "the_geom"
polygon - city of los angeles
            - has "gist" index on "the_geom"
            - has "btree" index on "fips"
also ran "vaccum analyze"


PROBLEM:
looking to select all points in city of los angeles.
out of 8663, 3248 points are contained in city of los angeles polygon.
it takes ~163000 ms. that's roughly 3 minutes.
How can I make this faster?


QUERY (x,y values are extent of polygon):
SELECT   point.id
FROM      point, polygon
WHERE   (x >= -118.668411254883)
       AND (y >= 33.7045364379883)
       AND (x <= -117.753349304199)
       AND (y <= 34.3367500305176)
       AND (polygon.fips = '0644000')
       AND point.the_geom && polygon.the_geom
       AND (contains(polygon.the_geom, point.the_geom))


 Aggregate  (cost=9.08..9.09 rows=1 width=4) (actual
time=162893.411..162893.411 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..9.05 rows=9 width=4) (actual
time=4137.275..162885.507 rows=3248 loops=1)
         Join Filter: contains("outer".the_geom, "inner".the_geom)
         ->  Index Scan using idx_polygon_fips on polygon 
(cost=0.00..3.01 rows=1 width=9414) (actual time=0.013..0.017 rows=1
loops=1)
               Index Cond: ((fips)::text = '0644000'::text)
         ->  Index Scan using idx_point_the_geom on point 
(cost=0.00..6.02 rows=1 width=25) (actual time=62.512..17889.149
rows=7050 loops=1)
               Index Cond: (point.the_geom && "outer".the_geom)
               Filter: ((point.x >= -118.668411254883) AND (point.y >=
33.7045364379883) AND (point.x <= -117.753349304199) AND (point.y <=
34.3367500305176) AND (point.the_geom && "outer".the_geom))
 Total runtime: 162893.483 ms