[postgis-users] Query planner problems

[Emily Gouge]

Try looking into the function update_geometry_stats.  I believe the geometry statistics can effect 
the query planner.

 From the postgis manual:

update_geometry_stats([<table_name>, <column_name>])

     Update statistics about spatial tables for use by the query planner. You will also need to run 
"VACUUM ANALYZE [table_name] [column_name]" for the statistics gathering process to be complete. 
NOTE: starting with PostgreSQL 8.0 statistics gathering is automatically performed running "VACUUM 
ANALYZE".



Stephen Woodbridge wrote:

> 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