Bug report #20018

Server and desktop too slow with Postgis views

Added by Jesus Rodriguez Marcial almost 2 years ago. Updated over 1 year ago.

Status:Feedback
Priority:Normal
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:3.4.5 Regression?:No
Operating System:Windows Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:27840

Description

If a layer is defined with a view or with sql, qgis issues a select distinct count of the id, I guess that it does that to assert that the Id is unique. If I have a huge table, it takes some 60 seconds to execute (and naively enough, multiply it if several layers are based on the same exact view) It causes timeouts in qgis server, and makes the map unusable. There should be a way to tell Qgis that I'm pretty sure that the PK is valid. Adding insult to injury, I don't even want to edit the data.

History

#1 Updated by Jürgen Fischer over 1 year ago

  • Status changed from Open to Feedback

Please test with QGIS 3.4 - QGIS 2.18 reached it's end of life.

#2 Updated by Evgeniy Baryshkov over 1 year ago

Jürgen Fischer wrote:

Please test with QGIS 3.4 - QGIS 2.18 reached it's end of life.

Same problem in QGIS 3.4.5 and 3.6

#3 Updated by Giovanni Manghi over 1 year ago

  • Affected QGIS version changed from 2.18.23 to 3.4.5
  • Status changed from Feedback to Open

#4 Updated by Regis Haubourg over 1 year ago

  • Status changed from Open to Feedback

Can you try the "trust dataprovider" option that is in the project properties / data source ? That should speed up loading times a lot since this option skips those consuming queries for views and datasources that don't have those metadata (extent, geometry type, id).
?
In my cases, with 250 big views in a project, the opening time went from 1min30s down to 4s . That was made on purpose for QGIS server use cases.

Also available in: Atom PDF