[postgis-users] Strange Performance issue

[Jerome Gasperi]

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 
indexes.

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 
cntry_name='France'

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 ?

Regards,

Jerome

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.

-- 
Jerome GASPERI
DCT/PS/TIS - BPi 1218
CENTRE NATIONAL D'ETUDES SPATIALES
18, Av Ed BELIN
31401 TOULOUSE Cedex 4
 
tel : 05 61 28 25 23
fax : 05 61 27 31 67