[postgis-users] Query planner problems

[Stephen Woodbridge]

Don Drake wrote:
> 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.

Thanks Don,

I can't believe I did that. <seesh> And that I didn't catch it when I 
reviewed it. <sigh>

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