[postgis-users] Performanceproblems

[Ethan Alpert]

The postgis query planner sometimes seems brain dead. I routinely increase performance 10-100x by selecting by my non-spatial indexes into a temp table, create a spatial index on the temp table and then perform my spatial queries.
 
-e

________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of Mark Cave-Ayland
Sent: Tue 10/12/2004 3:50 AM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] Performanceproblems



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
>


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 5864 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20041012/940987e8/attachment.bin>