Bug report #12630

Get sample query expression non-responsive for large PostGIS datasets

Added by Pete Lelliott over 4 years ago. Updated over 4 years ago.

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

Revision e509fc53
Added by Jürgen Fischer over 4 years ago

postgres provider: cast result of a subquery in min/max/uniqueValue(s) (fixes #12630; followup bf56457 ff)

Revision a9cccd2c
Added by Jürgen Fischer over 4 years ago

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 4 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 4 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 4 years ago

  • Status changed from Open to Closed

#4 Updated by Jürgen Fischer over 4 years ago

  • Status changed from Closed to In Progress

#5 Updated by Jürgen Fischer over 4 years ago

  • Status changed from In Progress to Closed
  • Resolution set to fixed/implemented

Also available in: Atom PDF