Feature request #1855

Postgis View Layers with OID as primary key are loading very slow

Added by Horst Düster over 11 years ago. Updated over 11 years ago.

Status:Closed
Priority:Low
Assignee:Jürgen Fischer
Category:Data Provider
Pull Request or Patch supplied: Resolution:fixed
Easy fix?:No Copied to github as #:11915

Description

I want to load a postgis view layer defined in a way that the OID represents the primary key of the view query result. No real primary key is defined in the source tables. When I query this view from psql it gives me the query result very fast. When I load this layer into QGIS it needs a very long time, more than 1 minute, to load this layer.

1855_selectpkey.diff Magnifier - patch to implement optional key selection for views (22.8 KB) Jürgen Fischer, 2009-08-12 01:54 PM

History

#1 Updated by Giovanni Manghi over 11 years ago

Hi,

what version of qgis are you using? platform?

#2 Updated by Horst Düster over 11 years ago

svn cbf7cf64 (SVN r11253)
kubuntu 9.04
postgres 8.4.0

#3 Updated by Jürgen Fischer over 11 years ago

Replying to hdus:

I want to load a postgis view layer defined in a way that the OID represents the primary key of the view query result. No real primary key is defined in the source tables. When I query this view from psql it gives me the query result very fast. When I load this layer into QGIS it needs a very long time, more than 1 minute, to load this layer.

Does "loading" mean only "inserting" or "displaying"? Inserting the view to the project can be slow, while QGIS tries to figure out what the primary key column is. Once that's done displaying should be quick. Saving the project and reloading it should be also quick as QGIS saves the key column it found.

If you add the layer using python you can also specify the key column using key=column and QGIS will just verify that it's unique.

See also #1535

#4 Updated by Horst Düster over 11 years ago

Replying to [comment:3 jef]:

Replying to hdus:

I want to load a postgis view layer defined in a way that the OID represents the primary key of the view query result. No real primary key is defined in the source tables. When I query this view from psql it gives me the query result very fast. When I load this layer into QGIS it needs a very long time, more than 1 minute, to load this layer.

Does "loading" mean only "inserting" or "displaying"? Inserting the view to the project can be slow, while QGIS tries to figure out what the primary key column is. Once that's done displaying should be quick. Saving the project and reloading it should be also quick as QGIS saves the key column it found.

If you add the layer using python you can also specify the key column using key=column and QGIS will just verify that it's unique.

See also #1535

Loading means inserting. I took a look at the comments of #1535 and there gjm talked about an option to select the primary key column by the user when he is loading the postgis layer. Maybe it is possible to make some changes of the Postgis loader UI in this direction?

#5 Updated by Horst Düster over 11 years ago

Replying to [comment:5 jef]:

Thank you for the patch. Yes in this way it works fine for me!

#6 Updated by Jürgen Fischer over 11 years ago

  • Resolution set to fixed
  • Status changed from Open to Closed

applied in d6134d5c (SVN r11480)

Also available in: Atom PDF