Bug report #477
Loading a PostGIS View is very slow
| 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 17 years ago
Please excuse but the mentioned file could not be attached due to size.
#2
Updated by Gavin Macaulay - over 17 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 about 17 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 - about 17 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 - about 17 years ago
Hmm. It's actually slower :(
#6
Updated by Redmine Admin about 17 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 - about 17 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 almost 17 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 over 14 years ago
Milestone Version 0.9 deleted