I'm working in Postgres 9.6. I have a table that looks like this:
gid | integer | not null default nextval('usgovt_gid_seq'::regclass) category | character varying(12) | geom | geometry(MultiPolygon,5070) | Indexes: "usgovt_pkey" PRIMARY KEY, btree (gid) "ix_usgovt_category" btree (category) "usgovt_geom_idx" gist (geom)
I want to retrieve the union of every polygon with a category of 1:
SELECT ST_Union(geom) AS geom FROM usgovt WHERE category='1' GROUP BY category;
However this query is EXTREMELY slow (half an hour and hasn't finished yet), and this is on a reasonably powerful machine, and I've tweaked Postgres's memory settings etc.
Why is this slow? Shouldn't it be instant? Since this is a single table, presumably the polygons can't overlap, so it's pretty much just an additive operation. PostGIS just needs to add them together to make a single large MultiPolygon.
Or perhaps polygons can overlap within a table? If so, is there some way to tell PostGIS that these polygons don't overlap, so it can just add them together?