[postgis-users] Performanceproblems

[strk at refractions.net]

Werdin,
First of all you should split your timings
between && and distance_spheroid() runtimes.
Note that your old query did not compute distances,
but just the equivalent of the && operator.
Compare the two w/out distance_spheroid involved.
Also compare difference bwteen distance_spheroid() 
and distance().
If distance() is much faster (should be) you might transform
you data to isometric projections (since you already split
by zones).
--strk;

On Tue, Oct 12, 2004 at 09:59:45AM +0200, Werdin Jens wrote:
> Hello,
> 
> I have a performance problem with PostGis. The Database has 11 million
> entries which have coordinates in WGS84 and a timestamp with TS. If I used a
> normal postgres-table with latitude and longitude as a coloum a an index
> over time,lat,long it took not 2 secs for the query  "Select * from table
> where time between and lat between and long between..." now I transformed it
> to postgis because I want the result sorted by distance_spheroid(). The
> Table now looks like id / time / POINT(srid=4326). I created the Index,
> updated geometry stats and vacuumed like described in the manual. 
> I use the query select *, distance_spheroid from table where geopoint &&
> setsrid(box3d) and time between 2 and 3 order by distance_spheroid.
> This query now takes more than 15 mins. 
> The result should have 100 entries (average) and up to 3000 entries (max).  
> 
> Do I have to change settings in the database or takes the distance_spheroid
> so much time or is my query not good or is it better to use an index on
> time?
> 
> Thanks in advance
> 
> Jens
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users