[postgis-users] Strange Performance issue
jerome.gasperi at cnes.fr
Thu Oct 13 05:12:30 PDT 2005
I'm experiencing strange performance behaviour using PostGIS.
I've a database containing :
+ a 'country' table containing countries of the world :
+ 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
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
SELECT count(*) FROM world_1000000 WHERE the_geom &&
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
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
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
More information about the postgis-users