[postgis-users] St_union with group by faster

[Paragon Corporation]

If you do a Materialized view and are using PostgreSQL 9.4, make sure to add a unique index on it and refresh with

REFRESH MATERIALIZED VIEW COUNCURRENTLY

That way your reads aren't blocked by building and might as well add a spatial index for faster spatial joins when used in queries

So

CREATE MATERIALIZED VIEW vw_mat_whatever AS
SELECT champ1, champ2, 
st_multi(st_union(geom))::geometry(MULTIPOLYGON, 2154)  AS geom
FROM table
GROUP BY champ1, champ2, champ3, champ4, champ5;


CREATE UNIQUE INDEX idx_vw_mat_whatever
  ON vw_mat_whatever
  USING btree
  (champ1, champ2);


CREATE INDEX idx_vw_mat_whatever_geom 
ON vw_mat_whatever
USING gist(geom);


-- then schedule a job to do this every so often --
REFRESH MATERIALIZED VIEW COUNCURRENTLY vw_mat_whatever;

Hope that helps,
Regina

http://www.postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Paul Ramsey
Sent: Wednesday, September 30, 2015 10:04 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] St_union with group by faster

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
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users