[postgis-users] Does anyone have multipolygons with large numbers of polygons

[Obe, Regina]

I know there has been some discussion in the past about how
generate_series doesn't scale well when you are dealing with large
numbers of geometries.  My suspicion was that it was because
generate_series has to materialize the result before outputting it and
that the result key is not indexed.

Unfortunately I don't have geometries large enough to test the theory
out, but does seem to be faster for me even for  structures where I have
more than 10 or geometries per multipolygon.  Can some people try this
out.  The below will take each multi geom and break it out into single
geom structures and will handle in theory a MULTI structure with up to
500,000 geoms and can easily handle more by increasing the number of
records in the materialized generate series table.

Can you compare this:

SELECT g.n
	INTO pgis_gs
FROM generate_series(1,500000) g(n);

ALTER TABLE pgis_gs
  ADD CONSTRAINT pgis_gs_pkey PRIMARY KEY(n);

SELECT t.gid, ST_GeometryN(the_geom, g.n) As poly
FROM somtable t CROSS JOIN pgis_gs g
WHERE g.n <= ST_NumGeometries(the_geom);

With these:

--this will handle at most 5000 (for small sets of geometries - incurs a
huge penalty)

SELECT t.gid, ST_GeometryN(the_geom, g.n) As poly
FROM somtable t CROSS JOIN generate_series(1,5000) g(n)
WHERE g.n <= ST_NumGeometries(the_geom);

-this can handle more but I suspect gets really bad for large numbers
SELECT t.gid, ST_GeometryN(the_geom, ST_NumGeometries(the_geom)) As poly
FROM somtable t;

Thanks,
Regina
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.