Opened 10 years ago

Closed 10 years ago

#1855 closed patch (fixed)

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

Reported by: hdus Owned by: jef
Priority: minor: annoyance Milestone: Version 1.3.0
Component: Data Provider Version: Trunk
Keywords: Cc: horst.duester@…
Must Fix for Release: Yes Platform: All
Platform Version: Awaiting user input: no

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.

Attachments (1)

1855_selectpkey.diff (22.8 KB) - added by jef 10 years ago.
patch to implement optional key selection for views

Download all attachments as: .zip

Change History (9)

comment:1 Changed 10 years ago by lutra

Owner: changed from nobody to mhugent

Hi,

what version of qgis are you using? platform?

comment:2 Changed 10 years ago by hdus

svn r11252 kubuntu 9.04 postgres 8.4.0

comment:3 in reply to:  description ; Changed 10 years ago by jef

Owner: changed from mhugent to 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

comment:4 in reply to:  3 Changed 10 years ago by hdus

Replying to 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?

Changed 10 years ago by jef

Attachment: 1855_selectpkey.diff added

patch to implement optional key selection for views

comment:5 Changed 10 years ago by jef

Type: bugpatch

comment:6 in reply to:  5 Changed 10 years ago by hdus

Replying to jef:

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

comment:7 Changed 10 years ago by jef

Milestone: Version 1.2.0Version 1.3.0

comment:8 Changed 10 years ago by jef

Resolution: fixed
Status: newclosed

applied in r11479

Note: See TracTickets for help on using tickets.