[Qgis-user] opening multitable postgis view is slow

[Jan Hartmann]

Just a hunch, Ronnie, would it hel to use PostGIS inheritance: creating 
master and child tables and using the ones you need for particular zoom 
levels?

http://www.postgresql.org/docs/9.1/static/tutorial-inheritance.html

Jan

On 04/17/2013 10:41 AM, Lassche, M.R. wrote:
> 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
>
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20130417/2a684420/attachment.html>