[postgis-users] Query planner problems

[Don Drake]

Try recompiling the procedure, I believe the query plan is cached.

-Don

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Stephen
Woodbridge
Sent: Monday, January 09, 2006 2:27 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Query planner problems

Hi all,

I'm trying to create a pgpsql function to spatially assign the nearest 
city in a table to a point.

CREATE OR REPLACE
FUNCTION swoodbridge.get_closest_city(  public.geometry,   float8) 
RETURNS int4 AS
$BODY$
   declare
     pnt   alias for $1;
     miles alias for $2;
     dist  float;
     cid   integer;

   begin
     if pnt is null then
       return 0;
     end if;
     dist = swoodbridge.miles_to_degree(miles, 0.0);
     select city_id into cid from swoodbridge.city t_in where 
expand(pnt, dist) && t_in.the_geom
       order by distance_sphere(pnt, t_in.the_geom) asc limit 1;
     if not found then
       return 0;
     else
       return cid;
     end if;
   end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

But it is working horridly slow. I extracted the select and just add 
constants of "pnt" so I could run explain on it. I can not get it to use 
the gist index on the table.

select city_id from swoodbridge.city t_in
   where t_in.the_geom && expand(setsrid(makepoint(0,0),4326), 10)
   order by distance_sphere(setsrid(makepoint(0,0),4326), t_in.the_geom)
     asc limit 1;


> Limit  (cost=166074.43..166074.43 rows=1 width=29)
>   ->  Sort  (cost=166074.43..166215.21 rows=56314 width=29)
>         Sort Key:
distance_sphere('0101000020E610000000000000000000000000000000000000'::geomet
ry, the_geom)
>         ->  Seq Scan on city t_in  (cost=0.00..160699.25 rows=56314
width=29)
>               Filter: (the_geom &&
'0103000020E6100000010000000500000000000000000024C000000000000024C0000000000
00024C0000000000000244000000000000024400000000000002440000000000000244000000
000000024C000000000000024C000000000000024C0'::geometry)

It has the following gist index and it has been vacuum analyzed.

CREATE INDEX city_gist ON swoodbridge.city USING btree (the_geom);

Is there some way to force it to use the index?

-Steve
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users