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

david.garnier at etudier-online.com david.garnier at etudier-online.com
Fri Oct 24 07:18:33 PDT 2003


Browse the archives. This question has been debated at least 3 times during the
last year. Basically, you should think a little bit more about what you're
asking the DB to do.

Best Regards,
David Garnier

Quoting Andrew Edmondson <a.edmondson at eris.qinetiq.com>:

> 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
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 





More information about the postgis-users mailing list