[postgis-users] Strange Performance issue

Jerome Gasperi jerome.gasperi at cnes.fr
Thu Oct 13 05:12:30 PDT 2005

Hi ,

I'm experiencing strange performance behaviour using PostGIS.
I've a database containing :
             + a 'country' table containing countries of the world : 
Multipolygon table
             + multiple tables containing from 1000 to 1000000 simple 
polygons (no holes, 4 vertices) representing
                 satellite acquisition footprints.

All tables are GIST indexed (using GIST_GEOMETRY_OPS). Database is 
vacummed (--full --analyze).
My final goal is to get all footprints within a country.

At first time i try a simple query to get all footprints within the 
bounding box of a country.

For example, taking the 1000000 footprint table within France :

       SELECT count(*) FROM world_1000000, country
       WHERE country.cntry_name='France'
       AND the_geom && country.geometry_country;
The \timing command gives 2882 results in ~4378 ms

It's a curiously long time for this kind of query regarding the spatial 

So i try a two passes approach, first get the bounding box of France and 
then compute the bounding box intersections :

        SELECT AsText(envelope(geometry_country)) FROM country WHERE 

Gives me the 'polygon like' bounding box of France in ~28 ms (let's call 
it $BBOX)

        SELECT count(*) FROM world_1000000 WHERE the_geom && 
GeomFromText('$BBOX', 4326);

The \timing command gives 2882 results in ~73 ms
So the overall command gives me the result in ~ 73 + 28 ~ 100 ms

It's 40x faster than the first query. Therefore it should have be quite 
the same time since the two approach seems (to me!) to do exactly the 
same thing.

I try with different countries in size and complexity, and with smallest 
footprints tables, with comparable performance results.
How can you explain this difference ?



PS : I'm currently using PostgreSQL 8.0.3, PostGIS 1.0.4 and Geos 2.1.4 
(all compiled by hand) under linux AND Mac OS X.
I also try with PostgreSQL 8.1 and  PostGIS CVS with the same behaviour.

DCT/PS/TIS - BPi 1218
18, Av Ed BELIN
31401 TOULOUSE Cedex 4
tel : 05 61 28 25 23
fax : 05 61 27 31 67

More information about the postgis-users mailing list