[postgis-users] Performanceproblems

[Mark Cave-Ayland]

Hi Jens,

It sounds as if the PostgreSQL planner is getting confused and choosing a
poor plan. Can you post the versions of PostgreSQL/PostGIS you are using
along with your table schema (\d <table name>) and the full output of
EXPLAIN ANALYZE <your SQL query here> for your poorly performing query.


Kind regards,

Mark.

------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT 

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk
 

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Werdin Jens
> Sent: 12 October 2004 09:00
> To: 'postgis-users at postgis.refractions.net'
> Subject: [postgis-users] Performanceproblems
> 
> 
> 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
>