Bug report #7609
Sorting PostGIS layer in attribute view is extremely slow
| 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
[FIX #7609] Fetch and cache data for sorting column in one query
History
#1
Updated by Pedro Venâncio over 11 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 11 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 11 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 11 years ago
- Status changed from Open to Closed
Fixed in changeset 66fadee8ef4e1fb8863f62a61d25159f23c4eee6.
#5
Updated by Regis Haubourg over 11 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 11 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 11 years ago
OK thanks, let's see tomorrow then.