QGIS makes excessive unnecessary calls to PostgreSQL #53973
Assignees
Labels
Comments
CoryAlbrecht
added
the
Bug
|
While doing this, QGIS pulls anywhere from 170% to 230% CPU usage on an 8 core, Core i9-11900H @ 2.50GHz with 64GB total RAM 38,5GB free. PostGreSQL barely shows any activity (taking 15 seconds to use 1 second of CPU time) in htop in a jail on a TrueNAS server with an 8 core, Xeon E5-2620 with 128GB RAM, 64GB used for the ZFS cache. The PostgreSQL data directory is entirely in ARC. |
|
Could you try those operation on QGIS 3.32.1 or even Master ? |
|
@nicogodet Same thing in master. |
|
@nicogodet I started the test with master, deleting 2,401 vertices from a 9,845 vertex feature, at 14:00:00. It's now 18:06, it's still going, and it is anywhere from 20 to 35 seconds between each iteration, of which there have been 833 so far. |
|
@nicogodet Still running today, 22 hours later, at 12:22, some 2,100+ repetitions, now roughly 1 minute between each set. |
|
Quick graph of the increasing gap in minutes between sets of the repeated SQL queries. https://github.com/qgis/QGIS/assets/8172909/3e01e004-8f8d-4090-aed8-27dda24ea8c9
|
|
So it finally finished at 18:14:13, or more than 28 hours after I pressed the 'delete' key, with 2,429 iterations of that set of unnecessary SQL queries. The delay between each iteration was roughly 90 seconds 28 bleeding hours to simply delete 2,401 vertices from a single feature, no analysis or anything like that, is just unacceptable. |
|
I've noticed that the same pattern of these unnecessary calls also happens when using the move feature tool. |
|
Was TopologicalEditing enabled when performing that deletion? If yes, can you check if disabling it improves things? |
github-actions
bot
added
the
stale
Topological editing makes no difference. On or off, it still happens. |
github-actions
bot
removed
the
stale
github-actions
bot
added
the
stale
github-actions
bot
removed
the
stale
2 tasks
|
Some thoughts... Is tracing / vertex snapping turned on? Do you have a layout window open which is refreshing its view? Perhaps a separate issue, but how many PostGIS layers do you have in your QGIS project? Do you also see unnecessary SQL call bloat on project load? ie. #51110 |
Yes, I use tracing extensively.
No. All refreshes are done by the time I start trying delete vertices
About two dozen in this one. I store everything in my home PostgreSQL server. But I see similar unnecessary calls in smaller projects with only a handful of layers.
I don't know. I haven't done that research. |
|
@weca-theo Just started a vertex delete with all snapping and tracing tuned off, and it still does the
Over and over again at increasing intervals, currently around 15 seconds between. |
|
While trying to figure out why tracing has stopped working, I noticed from the PostGIS log panel that a right click in the canvas (but without any previous left clicks to define a feature) that QGIS fires off 43 of these SQL statement groups. The project has 27 layers, with 5 of them active (visible) and the calls are for those 5 layers and all include a st_makeenvelope() call with coordinates similar to but not exactly the same as the map viewport. When no feature has been defined by previous left clicks, a right click should do nothing. So perhaps this is a symptom of where the unnecessary calls are coming from? |
|
So I tried to delete a bunch of vertices with snapping and tracing turned off, and it;'s been at it for over 36 hours so far, and the unnecessary accesses ar about 2 minutes apart. I hope somebody looks at this soon and can tone down the DB access calls. |
A right click on the map canvas, using the Identify Features tool, does not need a left click. It will return a list of map layers visible and located under the mouse cursor. So a right click doesn't 'do nothing'. As to the other issues, I'm not a developer so can't help. But hopefully someone else can think of why there is so much SQL bloat in this case. |
|
@weca-theo Sorry if I was unclear in that comment. I was not talking about the "Identify Features" tool. I was talking about the "Add Polygon" tool. When using the "Add Polygon" tool you left click on various spots to set the vertices (possibly snapping and tracing, which I do a lot of), and then you right click to finish off that feature and the attribute form pops up. https://docs.qgis.org/3.34/en/docs/user_manual/working_with_vector/editing_geometry_attributes.html?highlight=click#adding-features If you haven't done any left clicks to add vertices of a new feature, then a right click should do nothing at all. Why would an action that does nothing at all spawn multiple PostGIS calls? |
Interesting. Right click completes the shape, but doesn't commit it to the Postgres table until Save Edits is clicked... hmmm. |
This is normal behaviour. QGIS has an edit buffer. Also, the situation is more complex due to the existance of different transaction mode settings between QGIS and PostGIS. |
Sorry, bad sentence structure from me there. Indeed it is entirely normal behaviour. Regardless, I'm still stumped by OP's observations. @CoryAlbrecht if you turn off the other 5 layers in QGIS, then repeat the process, do the excessive SQL calls occurs still? |
|
@weca-theo ''' However, that's not a use case that is going to happen for me, as I need those multiple other layers to trace features from. Also, any other edit tool makes this happen as well, and they have in common making the canvas redraw when finished, so maybe it's However, that doesn't explain the bazillion extra calls to anything editing vertices that makes an unneeded call between 1 and 4 times per vertex being affected. |
|
interesting. However I'm certainly not the right person here. Hoping one of the devs picks this up, as QGIS-PostgreSQL networking is extremely bloated these days, across multiple versions. Considering it's the best open source SQL engine for GIS and the leading open source GIS software, you'd expect the networking aspect to be refined by now. You should add further info regarding your postgresql server. is it localhost? is it hosted in AWS/Azure? Do you have a corporate VPN in the office to connect to first? what's your network latency/bandwidth like etc. |
|
My database server is located at home. PostgreSQL 14.8 with PostGIS 3.3.1 running in a jail on TrueNAS-13.0-U6.1 (FreeBSD 13.1-RELEASE-p9). Xeon E5-2620, 8 cores, 16 threads, 160GB memory. The data is replicated out to a VPS on DigitalOcean running FreeBSD 13.0-RELEASE-p9 with identical PG software though not in a jail, but that is purely for backup, not use. Thanks to ZFS ARC and that 160GB of memory, the 10GB of /var/db/postgres/data14 is cached in memory better than 99.9% of the time. I doubt that either an Azure or AWS instance could give me the same level of responsiveness I have here at home. ;-) |
This was referenced Jan 25, 2024
|
Still seeing this on 3.36 Maidenhead QGIS version Active Python plugins |
|
Deleting 98 vertices, needed 106 of these select statements, taking 38 minutes and 36 seconds. As you can see, the time from BEGIN to CLOSE SQL statements is fractions of a millisecond, but QGIS pauses for longer and longer each time between. Why does it even need to do that in the first place to delete a vertex? Just delete the vertices and then redraw without going to the database. Ridiculous! |
|
I note that by merely selecting a lot of vertices with the vertex editor too can also take many minutes for QGIS to become responsive again. |
|
So I started deleting not quite 2,600 vertices that were left in the middle of a newly merged feature along what used to be the border between the original 2 features. It's been going on now for more than 48 hours and as you can see the pause between the unnecessary calls is closing in on 15 minutes. The cursor name started at qgis_4315, and is currently at qgis_4803, which, based on my past investigations, would seem to indicate that it's at most 25% of the way through after more than two days and that I am going to have to wait another 6 days. |
|
I got the same problem with tables in geopackage |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
What is the bug or the crash?
When doing anything with vertices, QGIS makes the following set of exactly the same (except or the cursor id) SQL queries 1 or more times for each vertex being worked on:
To remove 600 vertices selected with the vertex tool from a feature, that set of calls was made more than 800 times.
With a second deletion of 514 vertices, there was a pause of ~34 seconds before QGIS started making these repeated queries. It started off of pretty fast, several sets per second, but got slower over time. At the end it was 4 seconds between sets and a total of 515 times called. The entire delete operation took roughly 00:15:38 to complete from the time I pressed the 'delete' key, about 00:15:02 with nothing but the set of SQL statements repeated hundreds of times.
Here is the PostgreSQL for doing the delete, plus the canvas redraw when finished
queries.log
Since the vertices are being removed from the in-memory copy of the feature and and nothing is being written, it would seem the query (for whatever reason it is being used), only needs to be done once since the result from the database will never change.
What is the point of these excessive, seemingly needless queries?
Steps to reproduce the issue
tail -FVersions
QGIS version
3.28.8-Firenze
QGIS code revision
cdd15e0
Qt version
5.15.3
Python version
3.10.6
GDAL/OGR version
3.4.1
PROJ version
8.2.1
EPSG Registry database version
v10.041 (2021-12-03)
GEOS version
3.10.2-CAPI-1.16.0
SQLite version
3.37.2
PDAL version
2.3.0
PostgreSQL client version
unknown
SpatiaLite version
5.0.1
QWT version
6.1.4
QScintilla2 version
2.11.6
OS version
Ubuntu 22.04.2 LTS
This copy of QGIS writes debugging output.
Active Python plugins
quick_map_services
0.19.33
buffer_without_overlaps
0.2
splitmultipart
1.0.0
debug_vs
0.7
IPyConsole
version 2.0
naficp
1.0
GroupStats
2.2.7
QuickOSM
2.2.2
sagaprovider
2.12.99
otbprovider
2.12.99
MetaSearch
0.3.6
db_manager
0.1.20
grassprovider
2.12.99
processing
2.12.99
Supported QGIS version
New profile
Additional context
No response
The text was updated successfully, but these errors were encountered: