[Qgis-user] Performance rendering PostGIS-Data

[Bernhard Ströbl]

Hi Benjamin,

just a note about indices: if you index e.g. "road class" which only has 
5 classes the index may be counterproductive, because Postgres reads the 
index and then uses itx to decide which datasets to read and has to read 
most of them anyways. It might be faster to read all datasets and sort 
out which suit the condition. Check with EXPLAIN on the server.

Bernhard

Am 24.05.2016 um 15:32 schrieb Fuenfer-Koenigstein.Benjamin at swm.de:
> Hi all,
>
> I have a problem gaining better performance during rendering PostGIS Data in QIS. I hope the following example helps understanding the issue:
>
> I use road-data (line-geometries) in a PostGIS database (localhost), the geometry-table has about 400,000 rows.
> Roads have 6 different classes I want to style with different line-symbols. Depending on scale I only want to show some of the major road classes (scale up to 1:50,000) or all the roads (scale higher 1:50,000). I use a rule-based styling with road type attributes and min/max-scale, in some cases I use subclasses for bridges, tunnels and normal lanes.
>
> If I refresh the view in QGIS and check pg_log, I see that QGIS sends a SQL-statement with a bounding-box created from the current canvas:
> SELECT ... WHERE "the_geom" && st_makeenvelope(xmin,ymin,xmax,ymax)
>
> As long as the bounding-box is small enough (scale is high enough), PostGIS uses a spatial index and the performance while rendering in QGIS is quite good.
>  From a certain scale on (smaller than 1:250,000), spatial index isn't used anymore (seems that PostGIS decides that it's needles as nearly the whole data-extend is within bounding-box). From that point on, rendering  is very slow, even though only the major-roads are shown in QGIS (about 200 geometries of the total 400,000).
>
> That's what I tried already with no success:
>
> -       Trying different combinations of styling rules and min/max-scale
>
> -       Rendering-options in QGIS, simplify geometry, simplify on provider side if possible
>
> -       Generating indices on all the attributes I use for styling the road-data on the database
>
> Only thing that helps is to duplicate the layer in QGIS for each scale-level (above/under 1:50,000) and setting provider feature filter to select only features that are actually shown on the scale-level under 50.000. Than the SQL-statement logged changes to:
> SELECT ... WHERE ("the_geom" && st_makeenvelope(xmin,ymin,xmax,ymax) AND (((road_class IN ('major1','major2',))))
> Performance is much better but I'm not really glad about that solution as it makes the QGIS project unhandy and confusing for others using it because of the extra layers.
>
> Is there a way making QGIS passing SQL-statements generated from current canvas extend AND styling rules? Or another way gaining better rendering performance?
> If anyone had similar issues or has any idea, what I can do to make QGIS passing styling-rule based data selection to provider side, I'm glad to hear!
>
> Best wishes
>
> Benjamin Fünfer-Königstein
> S-IP-AN-TG Techn. Geschäftsfelder
>
>
>
>
> __________ Information from ESET Mail Security, version of virus signature database 13537 (20160524) __________
>
> The message was checked by ESET Mail Security.
> http://www.eset.com
>
>
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>



__________ Information from ESET Mail Security, version of virus signature database 13537 (20160524) __________

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