[Qgis-user] qgis essen + postgis / long project init times

[Florian Lohoff]

Hi,
i am having a hard time optimizing the qgis project startup times.

postgres 9.4.6-0+deb8u1
postgis 2.1.4+dfsg-3

qgis 1:2.14.0+13jessie

Database Machine is a seperate machine with 24Gigabyte and 8 Cores
and a 8 Disk Raid 10 and a Germany osm2pgsql import.

When starting qgis on a client and loading the project qgis runs
this query (From postgres log)

	LOG:  duration: 168161.385 ms  statement: SELECT DISTINCT
		upper(geometrytype("way")),st_srid("way"),st_ndims("way")
		FROM "public"."planet_osm_polygon"

for every layer referencing this table. As i have water, roadareas,
landuse, amenity in different layers i have the above 5 times which
results in 840 seconds project Init/Load time.

The query is not i/o bound - i havent got any disk i/o going on
while this query runs just one CPU at 100% for the time.

I failed to product functional indexes beeing used with DISTINCT
and i havent found any obvious way to instruct qgis to avoid this
query.

The geometry column way is defined like this:

	way                | geometry(Geometry,4326) |

For example i added this to the table:

    "enforce_ndims_way" CHECK (st_ndims(way) = 2 OR way IS NULL)
    "enforce_srid_way" CHECK (st_srid(way) = 4326 OR way IS NULL)

So asking for a distinct st_srid(way) is completely useless. Same
for the ndims.

Another thing is that this could be cached for the startup time
when the same table is referenced multiple times. Its a waste
of i/o and CPU.

I am currently out of ideas to get this to acceptable times.

Flo
-- 
Florian Lohoff                                                 f at zz.de
      We need to self-defend - GnuPG/PGP enable your email today!
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 828 bytes
Desc: Digital signature
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20160329/99ca0c0b/attachment.sig>