[postgis-users] Query not using index - why not?

[Andrew Edmondson]

I have a table called location (see definition below) - with a longlat column.
I want to select everything from location where longlat is within 50000 meters of a given point.
This query takes ages - and the EXPLAIN says it's doing a scan, rather than using the index.

Any ideas on how I can make it use the, or another better, index?

Thanks,

-Ed

<TABLE DEFINITION - INCLUDING INDEXES>

                                  Table "public.location"
   Column    |   Type   |                             Modifiers                             
-------------+----------+-------------------------------------------------------------------
 location_id | integer  | not null default nextval('public.location_location_id_seq'::text)
 longlat     | geometry | 
Indexes: location_location_id_key unique btree (location_id),
         location_location_idunique_idx unique btree (location_id),
         location_longlat_idx gist (longlat)
Check constraints: "$1" (srid(longlat) = 4278)
                   "$2" ((geometrytype(longlat) = 'POINT'::text) OR (longlat IS NULL))

</TABLE DEFINITION>

<QUERY>

EXPLAIN SELECT *, DISTANCE_SPHEROID('SRID=4278;POINT(120.4 14.583333)', longlat, 'SPHEROID[\"Airy_1830\",6377563.396,299.3249646]') AS distance FROM location WHERE DISTANCE_SPHEROID('SRID=4278;POINT(120.4 14.583333)', longlat, 'SPHEROID[\"Airy_1830\",6377563.396,299.3249646]') < 50000;

                                                                                QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on "location"  (cost=0.00..25.00 rows=333 width=40)
   Filter: (distance_spheroid('SRID=4278;POINT(120.4 14.583333)'::geometry, longlat, 'SPHEROID("Airy_1830",6377563.396,299.3249646)'::spheroid) < 50000::double precision)
(2 rows)

</QUERY>
-- 
##############################################################################
Andrew Edmondson
PGP Key: http://search.keyserver.net:11371/pks/lookup?op=get&search=0xCEE814DC
PGP Fingerprint: 7B32 4D1E AC4F 29E2 9EAA 9550 1A3D BBA4 CEE8 14DC