Bug report #21303

Postgis: very noticeable slowdown when opening the attributes table if the table has a large number of columns

Added by Giovanni Manghi almost 6 years ago. Updated over 5 years ago.

Status:Closed
Priority:High
Assignee:Alessandro Pasotti
Category:Attribute table
Affected QGIS version:3.5(master) Regression?:Yes
Operating System: Easy fix?:No
Pull Request or Patch supplied:Yes Resolution:fixed/implemented
Crashes QGIS or corrupts data:No Copied to github as #:29121

Description

I'm observing the following (on 3.4.4 and master, Linux and Windows):

if the table of attributes has a large number of columns (63 in my case), then operations like opening the table and pasting features is MUCH slower if compared to the same data loaded in QGIS 2.18.

My test scenario is a PostGIS layer (both client and server are on very fast connections) which has only a hand full (~4000) of small polygons (none larger than 80ha with no more than 600 vertexes, but much less on average).

Associated revisions

Revision 4f30a44b
Added by Alessandro Pasotti over 5 years ago

Postgis: cache information about enum fields

This is called several times and can slow down substantially
the opening of the attribute table.

Partially fixes #21303 (down from ~30 to ~6 seconds on a remote
connection)

The remaining ~4 seconds (compared to ~2 seconds in 2.18) are due
to the check for enums and provider-side constraints, that were
not implemented in 2.18.

See: QgsEnumerationWidgetFactory::fieldScore and the call to
enumValues for details, fieldScore is called several times
because QgsAttributeTableModel::loadAttributes is also
called multiple times and it queries for widget configuration
all the times.

History

#1 Updated by Giovanni Manghi almost 6 years ago

pasting new features (copied from an identical layer) is also unbearably slow, on QGIS 2.18 is done in very quick way (saving afterwards can be slow on both versions, depending on how many features were pasted).

#2 Updated by Giovanni Manghi almost 6 years ago

If the number of columns is reduced (like ~10) then opening the table operation do work at an acceptable speed, while pasting features remains VERY slow even if the copy operation was done on similar/equal layer with just a few columns.

#3 Updated by Alessandro Pasotti almost 6 years ago

Confirmed

#4 Updated by Giovanni Manghi almost 6 years ago

  • Subject changed from very noticeable slowdown in table of attributes operations if the table as a large number of columns to very noticeable slowdown in table of attributes operations if the table has a large number of columns

#5 Updated by Alessandro Pasotti almost 6 years ago

Can you split the ticket in two?

I've found the bottleneck for the paste slowdown issue, but the opening slowdown is completely unrelated.

#6 Updated by Giovanni Manghi almost 6 years ago

Alessandro Pasotti wrote:

Can you split the ticket in two?

I've found the bottleneck for the paste slowdown issue, but the opening slowdown is completely unrelated.

sure

#7 Updated by Giovanni Manghi almost 6 years ago

  • Subject changed from very noticeable slowdown in table of attributes operations if the table has a large number of columns to very noticeable slowdown when opening the attributes table if the table has a large number of columns

#8 Updated by Alessandro Pasotti over 5 years ago

  • Status changed from Open to Feedback

I cannot reproduce with a (localhost) PG table with 4000 records and 100 columns, maybe you could share a project and data?

#9 Updated by Giovanni Manghi over 5 years ago

  • Status changed from Feedback to Open

Alessandro Pasotti wrote:

I cannot reproduce with a (localhost) PG table with 4000 records and 100 columns, maybe you could share a project and data?

test project sent privately.

#10 Updated by Alessandro Pasotti over 5 years ago

  • Assignee set to Alessandro Pasotti

#11 Updated by Alessandro Pasotti over 5 years ago

  • Status changed from Open to In Progress
  • Pull Request or Patch supplied changed from No to Yes
  • Resolution set to fixed/implemented

from 30 secs to 6 secs: https://github.com/qgis/QGIS/pull/9219

There is no room for substancial further speed improvements because of the new checks for enums that need to happen at least once.

#12 Updated by Alessandro Pasotti over 5 years ago

  • Subject changed from very noticeable slowdown when opening the attributes table if the table has a large number of columns to Postgis: very noticeable slowdown when opening the attributes table if the table has a large number of columns

#13 Updated by Alessandro Pasotti over 5 years ago

  • Status changed from In Progress to Closed
  • % Done changed from 0 to 100

Also available in: Atom PDF