Loading a PostGIS View is very slow

[horst.duester@…]

The loading of a PostGIS view is much slower than the loading of a table with the same structure.

The simple view CREATE OR REPLACE VIEW geo_gemeinden_v AS

SELECT oid, ogc_fid, wkb_geometry, name, gem_bfs, gmde_name, gmde_nr, bzrk_nr, eg_nr, plz, ktn_nr, new_date, archive_date, archive FROM geo_gemeinden;

needs 24secs to load. The table geo_gemeinden with the same structure less than 1sec.

For testing purposes I attach the dump of geo_gemeinden

[horst.duester@…]

Please excuse but the mentioned file could not be attached due to size.

[g_j_m]

Qgis has to do a lot more work when loading views. It has to work out which table/column each column in the view comes from, and then choose a column to use as a unique key. This entails working out that information for all views in the database. If you have a lot of views in the database, this may take some time.

Nothwithstanding all of that, 24 seconds is a lot longer than 1 second, and this area clearly needs some more work, but it's unlikely to happen before we release 0.8.

[horst.duester@…]

Now release 0.8 is released and I want to ask for the state of solution. My DB has more than 300 Geo-Layers and much more Geo-Views. QGIS takes a very long time to load a view.

[g_j_m]

A potential speed-up has been committed to head (r6610). Please test if you can. If it works, I'll port it to the 0.8.1 branch.

[g_j_m]

Hmm. It's actually slower :(

[horst.duester@…]

I'll come back to my qustion about the state of solution. The slowness of views is a real and essential problem. I'm not able to use QGIS in our enterprise environment due to the view problem.

Horst Duester SO!GIS Kanton Solothurn

[g_j_m]

Nothing more has been done about this. I'd actually forgotten about it! I'll have another go this weekend.

Gavin

[mhugent]

A fix for this is in r7071. It also adresses column renaming with 'AS' and views that refer to other views.

[(none)]

Milestone Version 0.9 deleted