Bug report #20270

Freeze when opening properties dialog on very large PostGIS layer

Added by Geoff Smith almost 2 years ago. Updated almost 2 years ago.

Status:Open
Priority:High
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:3.4.1 Regression?:Yes
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:28091

Description

In QGIS 3.4.0 on OSX I'm not able to open the properties dialog on a PostGIS vector layer. The app just freezes (but doesn't crash).

This only happens when I'm opening properties on a very large table (mine has 22 million rows). With a smaller table, it opens fine. Looking at pg_stat_activity on the database while QGIS is frozen, it looks like QGIS is running a 'SELECT COUNT FROM table;', which is known to perform really badly in Postgres on large table unfortunately.

I also left it to run for a few minutes and it does eventually open the properties dialog, which is another bit of evidence that it's because of this count query.

I'm not a postgres expert, but I've had a look into workarounds and unfortunately the only ones I can find involve modifying the count query rather than some config tweak in postgres, which makes it difficult for someone to workaround without a modification to QGIS.

I don't have this problem on the latest QGIS 2 and i think this was only introduced in the last couple of months since I think I had a working QGIS 3 at some point in the last few months. So i'm guessing something new has triggered a count where there wasn't one before.

This is my first bug report on QGIS, please let me know if I can provide any more info or run something to help diagnose.

History

#1 Updated by Giovanni Manghi almost 2 years ago

  • Operating System deleted (OSX 10.14)
  • Priority changed from Normal to High

#2 Updated by Antal Kosza almost 2 years ago

I can confirm this bug.

In my layer, there are approx. 3.5 million rows. Qgis did not finish in half an hour, but after killing the running query process, the properties dialog box appears instantly.

The same query (SELECT count (*) FROM tablename) with psql was 210 ms, according to the timing option. With ogrinfo ca. 500 ms.

My system is debian buster, with self build qgis, from up to date branch release_3_4.
details
QGIS verzi├│ 3.4.1-Madeira
QGIS 8f1c51b4a6
Qt 5.11.2
GDAL/OGR 2.3.2
GEOS 3.7.0-CAPI-1.11.0
PostgreSQL 11.1 (Debian 11.1-1+b1)
PROJ 5.2.0

#3 Updated by Giovanni Manghi almost 2 years ago

  • Affected QGIS version changed from 3.4.0 to 3.4.1

Very painful regression for all large postgis tables users.

#5 Updated by Geoff Smith almost 2 years ago

Thanks for the suggestion Antal, though I'm afraid that workaround doesn't improve things for me, still does the "SELECT *" when I open the layer properties.

Also available in: Atom PDF