[Qgis-user] opening multitable postgis view is slow

[Lassche, M.R.]

Bernard,

Yes there are spatial indexes in place. The get extent is slow because building the result set for the whole table (9 million rows with point geometry) is slow.
What I do now is a multi step approach
1 - alter the query in such a way that no rows are returned - simple by adding a where condition that is never true e.g where 1=2
2- opening the layer in QGis, gives me no results 
4 - zoom in to the desired region
5 - alter the view so the query does return rows
6 - refresh the data layer.

This does work, but is kind of a hassle.

Ronnie

-----Oorspronkelijk bericht-----
Van: Bernhard Ströbl [mailto:bernhard.stroebl at jena.de] 
Verzonden: woensdag 17 april 2013 10:29
Aan: qgis-user at lists.osgeo.org
Onderwerp: Re: [Qgis-user] opening multitable postgis view is slow

Hi Ronnie,

do you have a spatial index on the geometry columns of the tables contained in the view?
(just guessing)

Bernhard

Am 17.04.2013 10:26, schrieb Lassche, M.R.:
> Hi all,
>
> When loading multi table views in QGis it always starts with determining the extent of the result set.
> With pg_stat_activity you can see the following query running for a long time: SELECT st_extent("geom") FROM "bag"."vw_bag_pand_functie"
>
> Does anyone has ideas of how to speed up the loading this dataset in QGis. The view is as efficient as can be, it will normally returns something like 9 million rows for the whole of the Netherlands, but normally I zoom in to a region of interest and then there is no need of first establishing the extent of the whole data set.
>
> All suggestions are welcome
> Ronnie



__________ Information from ESET Mail Security, version of virus signature database 8236 (20130417) __________

The message was checked by ESET Mail Security.
http://www.eset.com