Sorting PostGIS layer in attribute view is extremely slow

[qgib]

Author Name: Nyall Dawson (@nyalldawson)
Original Redmine Issue: 7609
Affected QGIS version: master
Redmine category:vectors

---

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

[qgib]

Author Name: Pedro Venâncio (Pedro Venâncio)

---

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.

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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.

---

• category_id was configured as Vectors
• priority_id was changed from Normal to Severe/Regression

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

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)

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

Fixed in changeset "66fadee8ef4e1fb8863f62a61d25159f23c4eee6".

---

• status_id was changed from Open to Closed

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

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) ?

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

Hi Regis,

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

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

OK thanks, let's see tomorrow then.

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

regis Haubourg wrote:

OK thanks, let's see tomorrow then.

is this related?

#16569