Make the big things smaller. - Break the multi-polygons into single polygons - Cut the single polygons up into even smaller things using a grid - Parallelize by having one query check for containment with one subset of polygons and others check for containment on a second/third/fourth (this approach might break down if you get I/O bound and start thrashing, but to the extent that the operation remains CPU bound it will help)
P. 2009/5/13 Jorge Arévalo <[email protected]>: > Hello, > > As I said in these threads: > > http://postgis.refractions.net/pipermail/postgis-users/2009-May/023369.html > http://postgis.refractions.net/pipermail/postgis-users/2009-May/023415.html > > We're working with a table of large geometries (multipolygons) and small > number of rows. We want to check in which multipolygon of this table are the > points of another table. This another table has ~8 million of points. The > SQL code is: > > --loop for all polygons. T1 has polygons, but we use the center, a point, to > do the Within > for i in select ogc_fid from T1 LOOP > update T1 > set field_to_update = (select updating_field from T2 where > ST_Within(ST_Centroid(T1.wkb_geometry), the_geom) LIMIT 1 OFFSET 0) > where ogc_fid = i; > END LOOP > --end of loop > > (We have to update one table with a field of the another one, but the > important part is the ST_Within part) > > In the worst case (point is in the last multipolygon) the query takes 1sec. > In the best case, takes 16msec. If we take into account the worst case only > (this is, all the queries will take 1sec), we're talking about 93 days... We > need to improve the performance of this. So, we tried: > > - Force using index instead of sequential scan (SET enable_seqscan TO off). > It didn't improve the speed > - Use bounding box of multipolygons instead the multipolygons (ST_Envelope). > We can't do this because bounding box doesn't take into account > InnerBoundaries inside multipolygons, and we have multipolygons with "holes" > filled of other multipolygons > - Clustering of indexes. It didn't increase the speed > - Force_2D(). It didn't increase the speed significantly > > We're using PostGIS 1.3.5 with GEOS and PROJ support, over PostgreSQL 8.1.1. > Our operating system is Suse Linux Enterprise. The hardware is an Intel XEON > 2.5 GHZ (64 bits) and 4GB RAM. > > How could we improve the performance of this? 93 days are too much time. Is > it possible to take advantage of the 4-core processor, for example? > > Thanks in advance > Best regards > > Jorge > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
