Bug report #12630
Get sample query expression non-responsive for large PostGIS datasets
|Affected QGIS version:||master||Regression?:||No|
|Operating System:||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:||fixed/implemented|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||20745|
Apologies if I've put this into the wrong category, this is my first time posting a bug report.
I have a large dataset with more than 3 billion points. When constructing a sql expression in the Set Filter of layer properties and using the 'Sample' feature, QGIS 2.8 goes non-responsive. The field I'm sampling is of type integer.
Upon looking at the PostGIS server status, I can see that the query sent by QGIS is the following:
SELECT DISTINCT "field_to_sample"::text FROM "public"."feature_dataset" ORDER BY "field_to_sample" LIMIT 25
Within PostGIS, changing this to the actual datatype of the dataset returns a result in a little under 2 seconds;
SELECT DISTINCT "field_to_sample"::integer FROM "public"."feature_dataset" ORDER BY "field_to_sample" LIMIT 25
I imagine that the type conversion to text of large datasets (if that is what is happening?) could be causing the non-responsive behaviour within QGIS. Does the type conversion have to happen within the query?
QGIS Version 2.6 does not do this type conversion, so therefore returns the expected result within a few seconds.
postgres provider: cast result of a subquery in min/max/uniqueValue(s) (fixes #12630; followup bf56457 ff)
#1 Updated by Giovanni Manghi almost 5 years ago
- Target version set to Version 2.8.2
- Category changed from GUI to Data Provider/PostGIS
- Priority changed from Normal to Severe/Regression
I don't have such big dataset to test with right now, anyway until then I'll tag this a regression as it seems to be.
#2 Updated by Giovanni Manghi almost 5 years ago
- OS version deleted (
2.8 64 Bit)
- Affected QGIS version changed from 2.8.1 to master
- Operating System deleted (
Windows 7 Professional 64Bit)
I confirmed on 2.6.1 on a large table (2.8M records): there is considerably faster and int columns values are ordered as numbers, not as text as in 2.8.1 and master.