Bug report #477

Loading a PostGIS View is very slow

Added by Redmine Admin over 13 years ago. Updated about 11 years ago.

Status:Closed
Priority:Low
Assignee:Gavin Macaulay -
Category:Vectors
Affected QGIS version: Regression?:No
Operating System:RedHat Easy fix?:No
Pull Request or Patch supplied: Resolution:fixed
Crashes QGIS or corrupts data: Copied to github as #:10536

Description

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

History

#1 Updated by Redmine Admin over 13 years ago

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

#2 Updated by Gavin Macaulay - over 13 years ago

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.

#3 Updated by Redmine Admin over 13 years ago

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.

#4 Updated by Gavin Macaulay - over 13 years ago

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

#5 Updated by Gavin Macaulay - over 13 years ago

Hmm. It's actually slower :(

#6 Updated by Redmine Admin over 13 years ago

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

#7 Updated by Gavin Macaulay - over 13 years ago

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

Gavin

#8 Updated by Marco Hugentobler about 13 years ago

  • Status changed from Open to Closed
  • Resolution set to fixed

A fix for this is in 5324e893 (SVN r7072). It also adresses column renaming with 'AS' and views that refer to other views.

#9 Updated by Anonymous about 11 years ago

Milestone Version 0.9 deleted

Also available in: Atom PDF