[Qgis-user] (poor) performance loading postgis layers in qgis-server 2.8 ltr + postgis 2.1 + lizmap web-client

[Bernhard Ströbl]

I am just wondering: Why is the "big" table only twenty rows? Is this a 
multi(polygon?) table? If yes and the parts of each multipolygon are all 
over the place then this is your problem. QGIS has to load all parts 
although only a couple of them are visible at the moment. And if it 
happens that parts of all 20 features are visible QGIS has to load 
_everything_.

To make it a non-multi table:
<pseudo_code>
CREATE newtable as(
gid SERIAL NOT NULL
other_fields,
pk_constraint);
SELECT addgeometrycolumn(newtable,....); --Polygon
-- explode-code
INSERT INTO newtable(gid,the_geom,other_fields)
SELECT nextval('newtable_gid_seq') AS gid, the_geom, other_fields
FROM (SELECT ST_GeometryN(the_geom, generate_series(1, 
ST_NumGeometries(the_geom))) AS the_geom, other_fields
		FROM your_multi_table
		WHERE GeometryType(the_geom) = 'MULTIPOLYGON'
	) AS foo;
</pseudo_code>
(I do not remember where I once read this code, so I cannot give credits 
to the author here, I am afraid)

You could of course explode the multi features in QGIS and load the 
result into the db, too.
Create a spatial index on newtable and vacuum it afterwards.

Bernhard

Am 24.06.2016 um 05:40 schrieb Daniel Vicente Lühr Sierra:
> Hi,
>
> Indeed, 2.14.3 shows a better performance with respect to serving
> database data, even without yet tuning the database server.
>
> But, I still experience some performance issues. One, I detected when
> using the print composer, which is very unresponsive if there is a map
> object with a big database layer and redrawing is needed (without
> drawing the map, the UI responds fine).
>
> Second, the webclient layers load in decent time, even the "big-one".
> But, trying to use lizmap's "tooltip" feature on the big layer, or
> trying to access its attribute table is still not possible. I haven't
> checked the code, but maybe the way the data is being requested in these
> situations is suboptimal?
>
> Regards
>
> El 21/06/16 a las 11:08, Daniel Vicente Lühr Sierra escribió:
>>
>> Hi Régis,
>>
>>
>> El 21/06/16 a las 10:37, Régis Haubourg escribió:
>>> Hi,
>>> please see that thread [0]
>> Thanks.
>>> I suggest you first upgrade qgis server to last 2.14.3.
>> Ok, some weeks ago I decided to stay with 2.8 ltr, until 2.14 was more
>> tested, it looks like it is time for upgrading.
>>> Then we still have issues with big relational views, not tables - for
>>> first getcapabilities generation (ie first load for a session).
>>> You can also check your postgres logs to look at what queries are
>>> sent to PG and then tune your pg server based on those queries.
>> Ok. Although, I still need to learn how to do it ...
>>> Please keep us informed of how it works, we have the very same concerns.
>> Sure, thanks again.
>>> [0] http://osgeo-org.1560.x6.nabble.com/Server-performance-questions-td5252233i20.html#a5268188
>>>
>>> 2016-06-21 16:29 GMT+02:00 Daniel Vicente Lühr Sierra <dluhr at ieee.org>:
>>>
>>>      Hi,
>>>
>>>      It is my first time at setting up a postgres/PostGIS db for
>>>      storing "serious" data (previously, I had only tested if it
>>>      worked, with a couple of small data tables).
>>>
>>>      When testing the web-client (lizmap) installation I noticed that
>>>      the sample data loaded pretty fast, but when I tried a project
>>>      containing 2 vector layers stored in the database, one very
>>>      small, the second one was originally a ~30Mb shape file, the
>>>      loading process for the 2 layers is noticeable, and reading the
>>>      attribute table of the big vector layer (only 20 rows and 10
>>>      fields) takes ages (actually, I wasn't able to wait for it to load).
>>>
>>>      I did some "google" research and found out that there might be
>>>      some performance issues on qgis-server when accessing postgresql
>>>      layer, or it could be that the performance of the db is low (I
>>>      haven't run the tuning utility, yet).
>>>
>>>      Any ideas on how to hunt down this performance issue? May it be
>>>      an issue with the web-client, also?
>>>
>>>      Btw, everything is running locally on a VM for now, although the
>>>      final deployment will be on "real" hardware.
>>>
>>>      Thanks
>>>
>>>      --
>>>      Daniel Vicente Lühr Sierra
>>>      IEEE Member
>>>      IEEE Student Branch Counselor - Universidad Austral de Chile
>>>
>>>


__________ Information from ESET Mail Security, version of virus signature database 13698 (20160624) __________

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