Bug report #7609

Sorting PostGIS layer in attribute view is extremely slow

Added by Nyall Dawson over 6 years ago. Updated over 6 years ago.

Status:Closed
Priority:Severe/Regression
Assignee:-
Category:Vectors
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:16537

Description

In current master sorting a PostGIS table by an attribute is very slow. The same table (containing ~ 1 million rows) exported to a shp file sorts in around 10 seconds, but the PostGIS version never finishes and endlessly spams the console with:

Debug: src/providers/postgres/qgspostgresconn.cpp: 804: (openCursor) Starting read-only transaction
Debug: src/providers/postgres/qgspostgresconn.cpp: 818: (closeCursor) Committing read-only transaction

Associated revisions

Revision 66fadee8
Added by Matthias Kuhn over 6 years ago

[FIX #7609] Fetch and cache data for sorting column in one query

History

#1 Updated by Pedro Venâncio over 6 years ago

I confirm, I notice quite a difference, even with layers much smaller than the Nyall.

But this only happens when I order a column for the 1st time, that is, clicking on a column header, it takes a long time to do the sorting. After this initial sorting, clicking on the same column to reverse the sort, the change is instantaneous.
Also, if I sort by another column, it continues to be fast.

Closing the attribute table and reopening, the slow pace of the 1st ordination happen again.

In 1.8 the ordering is always fast and, in my case, the database is on the machine where I have the QGIS master.

This seems limited to PostGIS, it does not happen with the same data in Spatialite or Shapefile.

#2 Updated by Giovanni Manghi over 6 years ago

  • Category set to Vectors
  • Priority changed from Normal to Severe/Regression

Pedro Venâncio wrote:

I confirm, I notice quite a difference, even with layers much smaller than the Nyall.

But this only happens when I order a column for the 1st time, that is, clicking on a column header, it takes a long time to do the sorting. After this initial sorting, clicking on the same column to reverse the sort, the change is instantaneous.
Also, if I sort by another column, it continues to be fast.

Closing the attribute table and reopening, the slow pace of the 1st ordination happen again.

In 1.8 the ordering is always fast and, in my case, the database is on the machine where I have the QGIS master.

This seems limited to PostGIS, it does not happen with the same data in Spatialite or Shapefile.

it should be a regression then.

#3 Updated by Regis Haubourg over 6 years ago

Tested in revision 268c037 , sorting attribute table in large postgis datasets leads to freeze and crash.
Same table as shapefile works OK (not real fast, but sufficient and stable)

#4 Updated by Matthias Kuhn over 6 years ago

  • Status changed from Open to Closed

#5 Updated by Regis Haubourg over 6 years ago

Hi Matthias,
since last revision, attribute show NULL values in attribute window, when identify is OK. It's true also fo shp this time. Do we reopen this ticket (it's probably related) ?

#6 Updated by Matthias Kuhn over 6 years ago

Hi Regis,

It should be already fixed on master.
I only noticed this morning, so the Windows nightly is probably buggy until tomorrow.

#7 Updated by Regis Haubourg over 6 years ago

OK thanks, let's see tomorrow then.

#8 Updated by Giovanni Manghi over 6 years ago

regis Haubourg wrote:

OK thanks, let's see tomorrow then.

is this related?

#7649

Also available in: Atom PDF