[postgis-users] St_union with group by faster
lr at pcorp.us
Wed Sep 30 21:26:48 PDT 2015
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
CREATE MATERIALIZED VIEW vw_mat_whatever AS
SELECT champ1, champ2,
st_multi(st_union(geom))::geometry(MULTIPOLYGON, 2154) AS geom
GROUP BY champ1, champ2, champ3, champ4, champ5;
CREATE UNIQUE INDEX idx_vw_mat_whatever
CREATE INDEX idx_vw_mat_whatever_geom
-- then schedule a job to do this every so often --
REFRESH MATERIALIZED VIEW COUNCURRENTLY vw_mat_whatever;
Hope that helps,
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:
> 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.
> 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,
> 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 ?
> 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
postgis-users mailing list
postgis-users at lists.osgeo.org
More information about the postgis-users