Bug report #12630
Get sample query expression non-responsive for large PostGIS datasets
Status: | Closed | ||
---|---|---|---|
Priority: | Severe/Regression | ||
Assignee: | - | ||
Category: | Data Provider/PostGIS | ||
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 |
Description
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.
Cheers,
Pete
Associated revisions
postgres provider: cast result of a subquery in min/max/uniqueValue(s) (fixes #12630; followup bf56457 ff)
postgres provider: cast result of a subquery in min/max/uniqueValue(s) (fixes #12630; followup bf56457 ff)
(cherry picked from commit e509fc53ce5cfdb64b984177c5279a6cf7d8644c)
History
#1 Updated by Giovanni Manghi over 9 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 over 9 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.
#3 Updated by Jürgen Fischer over 9 years ago
- Status changed from Open to Closed
Fixed in changeset e509fc53ce5cfdb64b984177c5279a6cf7d8644c.
#4 Updated by Jürgen Fischer over 9 years ago
- Status changed from Closed to In Progress
#5 Updated by Jürgen Fischer over 9 years ago
- Status changed from In Progress to Closed
- Resolution set to fixed/implemented