[postgis-users] Query planner problems

[Don Drake]

You're correct about the standalone statement, I thought the query plan was
from your function call.  Sorry about that.

You have the wrong index type:
CREATE INDEX city_gist ON swoodbridge.city USING btree (the_geom);

You don't want btree, you want gist.

-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:40 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Query planner problems

Don Drake wrote:
> Try recompiling the procedure, I believe the query plan is cached.

How? but just running the create or replace again?

But that will have no impact on the standalone select statement that I 
can not get to use the index.

-Steve

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

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