I have a table in PostGIS with a quite fine partition of a region, which I'd like to dissolve at two higher levels by means of two foreign keys (e.g. municipality and state). The partition is a real one, i.e. the areas don't overlap, they just touch each other. I thought it'd be nice to do that in views, to follow the "don't insert into the database something which can be calculated from other existing data". However, a simple
SELECT id_municipality, ST_Union(the_geom) FROM areas GROUP BY id_municipality
takes around 22s on a core i7 for "just" around 36000 areas with a POLYGON geometry field. I indexed the geometry field with a GIST index, but with no luck.
I am now thinking that the only practical way would be to insert a geometry column in the municipalities table and calculate it beforehand.
Unfortunately, I need to share my results to less technical people, so installing experimental packages (like topological extensions) is not practical.