[postgis-users] St_union with group by faster

[Paul Ramsey]

Indexes won't change anything, you're doing a full table scan. It's
about as fast as it's going to get. Consider using a materialized view
and just refreshing it on a schedule.

On Wed, Sep 30, 2015 at 6:57 AM, François Hugues
<hugues.francois at irstea.fr> wrote:
> Hi,
>
> You're talking about index on attributes but did you use a gist index on
> your geometries?
>
> Moreover, I'm not sure the casting is really useful but I don't know how
> much it costs.
>
> HTH
>
> Hug
>
> Le 30 sept. 2015 3:50 PM, "Guillaume ARNAUD (ancienne adresse)"
> <guillaume.arnaud at cg82.fr> a écrit :
>
> Hi all,
>
>
> I have a table in postgis which contains 6304 polygons.
>
> I need to make a view which union some of this polygon.
>
> I write this request :
>
> SELECT champ1, champ2, st_multi(st_union(geom))::geometry(MULTIPOLYGON,
> 2154) geom
> FROM table
> GROUP BY champ1, champ2, champ3, champ4, champ5
>
> The request works 36 seconds and give me the results.
>
> I have created some index on champ1 and champ2.
>
> Have you a tip to get the request faster ?
>
> Thanks
> --
> Guillaume ARNAUD
> Cellule SIGD
> Direction de l'Informatique
> Conseil Départemental de Tarn-et-Garonne
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users