[postgis-users] Problem intersecting big tables

[Pierre Racine]

Hi,

I have one table with 21 000 000 polygons (most of the forest stands of Canada) and another one with 25 very big polygons (I expect each of them intersects with some 100000 polygons from the first one). When I intersect the two tables like this:

CREATE TABLE result AS
SELECT ST_Intersection(a.geom, b.geom) as geom, aif, bid
FROM tablewith25poly a, tablewith21000000poly b
WHERE ST_Intersects(a.geom, b.geom);

The server crashes after something like two hours.

The weird thing is that if I do the same query with one polygon from the 25 polygon table at a time like this:

CREATE TABLE resultXX AS
SELECT ST_Intersection(a.geom, b.geom) as geom, aif, bid
FROM tablewith25poly a, tablewith21000000poly b
WHERE ST_Intersects(a.geom, b.geom) and aid=XX;

Where XX goes from 01 to 25, every query pass without problem... So I assume there is no problem with the validity of the 21 000 000 polygons.

How can I quickly trace where the problem comes from? I can install my own compiled version of PostGIS if necessary...

I have "PostgreSQL 8.4.5, compiled by Visual C++ build 1400, 32-bit" and Postgis "2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"

Thanks for any help,

Pierre