Bug report #20018

Server and desktop too slow with Postgis views

Added by Jesus Rodriguez Marcial about 1 year ago. Updated 7 months 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 11 months 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 9 months 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 9 months ago

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

#4 Updated by Regis Haubourg 7 months 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