Opened 7 years ago

Closed 6 years ago

#2354 closed enhancement (wontfix)

ST_Union performance problem

Reported by: akrherz Owned by: pramsey
Priority: medium Milestone:
Component: postgis Version: 1.5.X
Keywords: Cc:

Description

I use ST_Union to join together National Weather Service forecast zones and counties. This has worked fine for me for many years now. The most recent update of these zones by the NWS must include a few higher resolution geometries that now cause ST_Union to peg CPU for a few minutes before returning a result. My environment is:

PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit POSTGIS="1.5.8" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel.4.7.1, 23 September 2009" LIBXML="2.7.6" USE_STATS (procs from 1.5 r7360 need u

pgrade)

I am attaching my reproducer table dump, which I dumped via the command:

pg_dump -Fc -t postgis_union_issue postgis > st_union_problem.dump

the simple command to reproduce

select st_union(geom) from postgis_union_issue;

ST_IsValid indicates all the geometries are valid.

Thanks

Attachments (1)

st_union_problem.dump.gz (5.2 MB) - added by akrherz 7 years ago.

Change History (3)

Changed 7 years ago by akrherz

Attachment: st_union_problem.dump.gz added

comment:1 Changed 7 years ago by akrherz

so I had to cull some geometries to get under the 5 MB limit, with the culled geometries removed from the query I get:

NOTICE: TopologyException?: found non-noded intersection between LINESTRING (-77.0096 35.0631, -77.0089 35.0628) and LINESTRING (-77.0089 35.0628, -77.0089 35.0628) at -77.008930136999936 35.062848054000028

Here's the larger file with the other 3 geometries included:

http://mesonet.agron.iastate.edu/pickup/postgis/st_union_problem.dump

I don't get the topologyexception when they are included, just very slow.

comment:2 Changed 6 years ago by pramsey

Resolution: wontfix
Status: newclosed

I think it's unlikely this will ever get deliberately fixed, though core changes to the overlay code over time might make it better incidentally. If such changes do happen.

Note: See TracTickets for help on using tickets.