Bug report #10619
Attribute table 'Show features visible on map' option does not speed up loading of large postgis table
Status: | Closed | ||
---|---|---|---|
Priority: | High | ||
Assignee: | Jürgen Fischer | ||
Category: | Attribute table | ||
Affected QGIS version: | master | Regression?: | No |
Operating System: | Easy fix?: | No | |
Pull Request or Patch supplied: | No | Resolution: | worksforme |
Crashes QGIS or corrupts data: | No | Copied to github as #: | 19023 |
Description
I have read in one of the postgis dump/backups from here:
http://data.nlextract.nl/bag/postgis/
actually: http://data.nlextract.nl/bag/postgis/bag-8mrt2014.backup
which has all addresses and buildings in the netherlands (>10 million records each table).
Because opening the attribute table isn't possible for such table, Jurgen pointed me to the Data Sources settings, where I selected 'Show features visible on map' as Attribute table behaviour.
Even zoomin in at one address or house, it still seems QGIS is loading ALL features before being able to just show me that one record.
This takes >5 minutes, while painting those houses is second work (because of the spatial query).
This settings option IS working with me on shapefiles though.
Can somebody confirm? (to test you could read in that dump)
Related issues
Associated revisions
restore option to open the attribute table with only currently visible features
to improve performance (actually to make it usable with large tables again).
When used as initial setting in options the open attribute table will have a
fixed spatial filter to the canvas extent when it was opened. That extent is
also highlighted to indicate the filter.
fixes #10619
History
#1 Updated by Richard Duivenvoorde over 10 years ago
update: confirmed here by myself on osgeo4w QGIS master (windows), connecting to another linux postgresql server.
it seems to work with smaller (postgres) tables and shapefiles though
#2 Updated by Giovanni Manghi over 10 years ago
- Affected QGIS version changed from 2.2.0 to master
Richard Duivenvoorde wrote:
update: confirmed here by myself on osgeo4w QGIS master (windows), connecting to another linux postgresql server.
it seems to work with smaller (postgres) tables and shapefiles though
I can confirm, seems that 2.2 behave the same way.
#3 Updated by Matthias Kuhn over 10 years ago
- Category changed from Data Provider/PostGIS to Attribute table
To be precise:
The option works, it just doesn't reveal the performance boost you expect?
#4 Updated by Richard Duivenvoorde over 10 years ago
- File retrievingAllFeaturesButShowOnlyViewedSelected.png added
- File onlyviewedattr.png added
@matthias nope, it's not the performance, it's the fact that if I 'Show features visibible on map' and I view only 10 features in the map (from this big dataset), that I'm confronted with the same in between dialog as if I select 'Show all features' (see onlyviewedattr.png) counting from 10000 to 15.000.000 in this case.
While the intended behaviour would be that the provider uses the extent of the current mapcanvas to do a spatial selection to retrieve that 10 records (instead of apparently trying to retrieve 15000000 features and then find the 10 which are in view currently).
But maybe I'm wrong. Maybe QGIS has not other way to first retrieve all features to show me the 10 which are in view.
But in that case (and in current status) I'm not able to open the attribute table with a postgis layer with that much records because whatever option I choose for 'Attribute table behaviour' QGIS takes 5 minutes to retrieve them and then does not show them to me anyway....
And if I abort the loading of the features, I'm not able to see the attributes of the current features in view, because those are apparently not loaded... (see retrievingAllFeaturesButShowOnlyViewedSelected.png)
#5 Updated by Giovanni Manghi over 10 years ago
- Priority changed from High to Severe/Regression
surprise! in QGIS 1.8 it works like a charm (as expected).
#6 Updated by Giovanni Manghi over 10 years ago
- Subject changed from 'Attribute table behaviour'-options not working with large postgis table to Attribute table 'Show features visible on map' option not working with large postgis table
#7 Updated by Matthias Kuhn over 10 years ago
- Priority changed from Severe/Regression to High
I wouldn't tag this as a blocker unless you want to postpone the release for a longer time.
That's not a quick fix.
QGIS 2.x does filtering differently and will e.g. adjust the visible items on the table when you zoom out.
QGIS 1.8 just loaded the features once on table init.
The "options" defined in the settings are also not constraints for the attribute table, but rather default values for the filtering mode you can choose on the attribute table.
#8 Updated by Giovanni Manghi over 10 years ago
Matthias Kuhn wrote:
I wouldn't tag this as a blocker unless you want to postpone the release for a longer time.
That's not a quick fix.
I don't want to enter the discussion if a longer testing/fixing time has to be granted but among the list of actual regressions this seems like a huge one compared to others. I would suggest to leave the tag blocker even if no one will fix it on time for the release, as a reminder for the development of 2.6.
#9 Updated by Matthias Kuhn over 10 years ago
I wouldn't say it's a huge one if noone complained in the +6 months the issue exists.
/me would vote for leaving it unfixed in order to get 2.4 out in reasonable time and to be free to implement a proper solution for 2.6.
YMMV
#10 Updated by Jürgen Fischer over 10 years ago
- Target version changed from Version 2.4 to Future Release - High Priority
#11 Updated by Giovanni Manghi over 10 years ago
- Target version changed from Future Release - High Priority to Version 2.6
- Priority changed from High to Severe/Regression
Now that 2.4 is out I'll re-tag this as regression.
#12 Updated by Jürgen Fischer over 10 years ago
- Target version changed from Version 2.6 to Future Release - High Priority
#13 Updated by Matthias Kuhn about 10 years ago
- Subject changed from Attribute table 'Show features visible on map' option not working with large postgis table to Attribute table 'Show features visible on map' option does not speed up loading of large postgis table
#14 Updated by Luigi Pirelli almost 10 years ago
Analising with Jurgen and discussed with Giovani Manghi we decided to postpone the solutione to the next release for this reason:
1) is not really blocker (fetching optimization effectively works on rendering)
2) it doesn't work the optimization on fetching table view due the fact that if allow the "feature" to change dynamically among filter options
3) a combination of "main" visible option and "on table view" dynamic filtering can be implemented, but it touches the table model component that is used in other part of QGIS
So our evaluation is that it's better to apply the patch after next release and leaving time to test deeply in case of regressions.
#15 Updated by Matthias Kuhn almost 10 years ago
I agree that this should be postponed to after feature freeze.
I see two possibilities that can be evaluated to tackle this:
- Running the filter as a query on provider side every time the filter changes. This will lose the context (scroll state, selection, can probably be partially restored) of the table every time the filter changes (zoom, pan) and will need to re-populate it.
- Having a look at the main model's canFetchMore() and fetchMore() methods which allow to incrementally populate tables. With this approach, the table would not be fully populated initially, reducing initial loading time. Probably a feature iterator would need to be kept open to continue filling for subsequent scroll operations. I don't know exactly what the properties of such a solution are. Maybe we can have a threshold amount of features that will switch the table to this behavior.
#16 Updated by Richard Duivenvoorde almost 10 years ago
One other observation (not 100% sure it is related),
if I load this table in QGIS (zoomed in a lot to not load all 13.000.000 records), and select 10 houses and do a 'save as' and choose only selection (10 houses), QGIS also takes minutes to do this.
I think that is because of the problem.
Just want to add this as an argument to fix this (in a next version) as in my opinion you cannot use QGIS in this way on bigger postgis tables, if both loading the attributes, and saving a selection do not work
#17 Updated by Jürgen Fischer almost 10 years ago
- Assignee deleted (
Jürgen Fischer)
#18 Updated by Martin Dobias almost 10 years ago
- Priority changed from Severe/Regression to High
Does not look like a blocker to me.
#19 Updated by Jürgen Fischer over 9 years ago
- Assignee set to Jürgen Fischer
#20 Updated by Jürgen Fischer over 9 years ago
- Status changed from Open to Closed
Fixed in changeset 86d08b6c39bad37e4c9b468f55c4c60981b60fa2.
#21 Updated by Richard Duivenvoorde over 8 years ago
- Status changed from Closed to Reopened
Hitting this again now.
Working with a Postgis db loaded with Openstreetmap data of the whole world.
Currently number of records for roads: 93.011.512
Trying to view records with attribute table. Zoomed in at streetlevel.
I can now 'Abort' the fetching (though the button seems unresponsive at times), so I can at least stop the fetching.
But the filtering is done on fetched records yes?
Would an option be to set a treshold at (user defined): 100000 records, And IF that number of features is received, stop and ask the user if he really wants to load all (maybe showing the total records, if provider has a count(*) )?
OR maybe wants to add a spatial filter (for current extent of mapcanvas)?
Not sure if this is doable for all providers, but it would make working with this kind of datasets possible I think
#22 Updated by Giovanni Manghi over 8 years ago
Richard Duivenvoorde wrote:
Hitting this again now.
confirmed here too.
#23 Updated by Jürgen Fischer over 8 years ago
- Status changed from Reopened to Feedback
Richard Duivenvoorde wrote:
Hitting this again now.
And you have "Attribute table behaviour" set to "Show features visible on map" in options? Apparently that still works fine for me.
#24 Updated by Giovanni Manghi over 8 years ago
Jürgen Fischer wrote:
Richard Duivenvoorde wrote:
Hitting this again now.
And you have "Attribute table behaviour" set to "Show features visible on map" in options? Apparently that still works fine for me.
I just tested on the latest master and it now works fine also here.
#25 Updated by Richard Duivenvoorde over 8 years ago
will test tomorrow and report back
#26 Updated by Giovanni Manghi almost 8 years ago
- Status changed from Feedback to Closed
- Resolution set to worksforme
Closing for lack of feedback, please reopen if necessary.