[postgis-users] Postgis query slowing down with polygon bounds

[Mark Cave-Ayland]

On Tue, 2007-01-23 at 10:29 +1300, Rob Young wrote:
> I developed a pL/pgsql function that creates a bounding box for
> lat/long data.  Given a latitude, long and distance in metres, it
> returns a box that bounds a circle at that point with a radius equal
> to the distance parameter.  Here's an example:
> 
> select AsText(expand_sphere( 5, 3, 600 * 1000 ));
> 
> "POLYGON((8.41054153442383 -0.389891535043716,8.41054153442383
> 10.3898906707764,-2.41054105758667 10.3898906707764,-2.41054105758667
> -0.389891535043716,8.41054153442383 -0.389891535043716))"
> 
> However, when I use this in  a query (below) in my testing database,
> it runs significantly slower than when I run the query without
> bounding the data using expand_sphere.
> 
> 
> select *, astext(point) from test
> WHERE
> point && expand_sphere( 5, 3, 600 * 1000 ) AND
> distance_sphere( setsrid( makepoint( 3, 5 ), 4326 ), point ) < 600 * 1000;
> 
> 
> This has me puzzled, any suggestions as to what I should change to
> speed up this query?  The table test has a one geometry column, point,
> which contains point data.  There is a GIST index on point, and I
> anazlysed the database after adding the index.
> 
> Also, if there's interest I would be happy to share my expand_sphere code.
> 
> Regards
> Robert Young  :-)


Hi Robert,

Did you mark your expand_sphere() function as IMMUTABLE as part of its
CREATE FUNCTION definition? If not, the PostgreSQL planner assumes that
the function is VOLATILE and hence will not optimise the result as a
constant.

http://www.postgresql.org/docs/8.2/interactive/sql-createfunction.html


Kind regards,

Mark.