<div dir="ltr"><div class="gmail_default" style="font-family:monospace">So after getting very frustrated with QGIS taking literally hours to simply delete vertices, I made a bug report</div><div class="gmail_default" style="font-family:monospace"><br></div><div class="gmail_default" style="font-family:monospace"><a href="https://github.com/qgis/QGIS/issues/53973" target="_blank">https://github.com/qgis/QGIS/issues/53973</a><br></div><div class="gmail_default" style="font-family:monospace"><br></div><div class="gmail_default" style="font-family:monospace"><br></div><div class="gmail_default" style="font-family:monospace"><br></div><div class="gmail_default" style="font-family:monospace"><blockquote style="margin:0 0 0 40px;border:none;padding:0px"><div class="gmail_default" style="font-family:monospace"><br></div><div class="gmail_default" style="font-family:monospace"><p style="box-sizing:border-box;margin-top:0px;margin-bottom:16px;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px">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:</p></div><div class="gmail_default" style="font-family:monospace"><div style="box-sizing:border-box;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px;overflow:auto"><pre style="box-sizing:border-box;font-family:ui-monospace,SFMono-Regular,"SF Mono",Menlo,Consolas,"Liberation Mono",monospace;font-size:11.9px;margin-top:0px;margin-bottom:16px;padding:16px;overflow:auto;line-height:1.45;border-radius:6px"><code style="box-sizing:border-box;font-family:ui-monospace,SFMono-Regular,"SF Mono",Menlo,Consolas,"Liberation Mono",monospace;padding:0px;margin:0px;background:transparent;border-radius:6px;word-break:normal;border:0px;display:inline;overflow:visible;line-height:inherit">Jul 27 16:54:17 postgresql-3 postgres[97809]: [2219-1] ::>[2023-07-27 16:54:17.743 EDT][64c2d4bf.17e11][97809/] <maps%Alternate History> LOG:  duration: 0.323 ms  statement: BEGIN READ ONLY;DECLARE qgis_571 BINARY CURSOR FOR SELECT "id","names"::text,"main_rgb"::text,"border_rgb"::text,"stripe_rgb"::text,"start_date"::text,"end_date"::text,"sovereign"::text,"category"::text,"predecessors"::text,"successors"::text FROM "bop"."timeline_level_0" WHERE "id"=143</code></pre></div></div><div class="gmail_default" style="font-family:monospace"><div style="box-sizing:border-box;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px;overflow:auto"><pre style="box-sizing:border-box;font-family:ui-monospace,SFMono-Regular,"SF Mono",Menlo,Consolas,"Liberation Mono",monospace;font-size:11.9px;margin-top:0px;margin-bottom:16px;padding:16px;overflow:auto;line-height:1.45;border-radius:6px"><code style="box-sizing:border-box;font-family:ui-monospace,SFMono-Regular,"SF Mono",Menlo,Consolas,"Liberation Mono",monospace;padding:0px;margin:0px;background:transparent;border-radius:6px;word-break:normal;border:0px;display:inline;overflow:visible;line-height:inherit">Jul 27 16:54:17 postgresql-3 postgres[97809]: [2220-1] ::>[2023-07-27 16:54:17.744 EDT][64c2d4bf.17e11][97809/] <maps%Alternate History> LOG:  duration: 0.059 ms  statement: FETCH FORWARD 2000 FROM qgis_571</code></pre></div></div><div class="gmail_default" style="font-family:monospace"><div style="box-sizing:border-box;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px;overflow:auto"><pre style="box-sizing:border-box;font-family:ui-monospace,SFMono-Regular,"SF Mono",Menlo,Consolas,"Liberation Mono",monospace;font-size:11.9px;margin-top:0px;margin-bottom:16px;padding:16px;overflow:auto;line-height:1.45;border-radius:6px"><code style="box-sizing:border-box;font-family:ui-monospace,SFMono-Regular,"SF Mono",Menlo,Consolas,"Liberation Mono",monospace;padding:0px;margin:0px;background:transparent;border-radius:6px;word-break:normal;border:0px;display:inline;overflow:visible;line-height:inherit">Jul 27 16:54:17 postgresql-3 postgres[97809]: [2221-1] ::>[2023-07-27 16:54:17.744 EDT][64c2d4bf.17e11][97809/] <maps%Alternate History> LOG:  duration: 0.049 ms  statement: CLOSE qgis_571;COMMIT</code></pre></div></div><div class="gmail_default" style="font-family:monospace"><div style="box-sizing:border-box;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px;overflow:auto"><div style="box-sizing:border-box"><span aria-label="Copy" value="Jul 27 16:54:17 postgresql-3 postgres[97809]: [2219-1] ::>[2023-07-27 16:54:17.743 EDT][64c2d4bf.17e11][97809/] <maps%Alternate History> LOG:  duration: 0.323 ms  statement: BEGIN READ ONLY;DECLARE qgis_571 BINARY CURSOR FOR SELECT "id","names"::text,"main_rgb"::text,"border_rgb"::text,"stripe_rgb"::text,"start_date"::text,"end_date"::text,"sovereign"::text,"category"::text,"predecessors"::text,"successors"::text FROM "bop"."timeline_level_0" WHERE "id"=143
Jul 27 16:54:17 postgresql-3 postgres[97809]: [2220-1] ::>[2023-07-27 16:54:17.744 EDT][64c2d4bf.17e11][97809/] <maps%Alternate History> LOG:  duration: 0.059 ms  statement: FETCH FORWARD 2000 FROM qgis_571
Jul 27 16:54:17 postgresql-3 postgres[97809]: [2221-1] ::>[2023-07-27 16:54:17.744 EDT][64c2d4bf.17e11][97809/] <maps%Alternate History> LOG:  duration: 0.049 ms  statement: CLOSE qgis_571;COMMIT" role="button" style="box-sizing:border-box;display:inline-block;line-height:20px;vertical-align:middle;border-width:1px;border-style:solid;border-radius:6px;padding:0px"></span></div></div></div><div class="gmail_default" style="font-family:monospace"><p style="box-sizing:border-box;margin-top:0px;margin-bottom:16px;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px">To remove 600 vertices selected with the vertex tool from a feature, that set of calls was made more than 800 times.</p></div><div class="gmail_default" style="font-family:monospace"><p style="box-sizing:border-box;margin-top:0px;margin-bottom:16px;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px"><span style="color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px;background-color:rgb(255,255,255);font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;letter-spacing:normal;display:inline!important;float:none">With a second deletion of 514 vertices, there was a pause of ~34 seconds before QGIS started making these repeated queries. It started </span>off of pretty<br></p></div><div class="gmail_default" style="font-family:monospace"><p style="box-sizing:border-box;margin-top:0px;margin-bottom:16px;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px">Here is the PostgreSQL for doing the delete, plus the canvas redraw when finished</p></div><div class="gmail_default" style="font-family:monospace"><p style="box-sizing:border-box;margin-top:0px;margin-bottom:16px;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px"><a href="https://github.com/qgis/QGIS/files/12188596/queries.log" style="box-sizing:border-box;background-color:transparent;text-decoration-line:none" target="_blank">queries.log</a></p></div><div class="gmail_default" style="font-family:monospace"><p style="box-sizing:border-box;margin-top:0px;margin-bottom:16px;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px">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.</p></div><div class="gmail_default" style="font-family:monospace"><p style="box-sizing:border-box;margin-top:0px;margin-bottom:16px;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px">What is the point of these excessive, seemingly needless queries?</p></div></blockquote></div><div class="gmail_default" style="font-family:monospace"><div style="box-sizing:border-box;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px;overflow:auto"><div style="box-sizing:border-box"><span aria-label="Copy" value="Jul 27 16:54:17 postgresql-3 postgres[97809]: [2219-1] ::>[2023-07-27 16:54:17.743 EDT][64c2d4bf.17e11][97809/] <maps%Alternate History> LOG:  duration: 0.323 ms  statement: BEGIN READ ONLY;DECLARE qgis_571 BINARY CURSOR FOR SELECT "id","names"::text,"main_rgb"::text,"border_rgb"::text,"stripe_rgb"::text,"start_date"::text,"end_date"::text,"sovereign"::text,"category"::text,"predecessors"::text,"successors"::text FROM "bop"."timeline_level_0" WHERE "id"=143
Jul 27 16:54:17 postgresql-3 postgres[97809]: [2220-1] ::>[2023-07-27 16:54:17.744 EDT][64c2d4bf.17e11][97809/] <maps%Alternate History> LOG:  duration: 0.059 ms  statement: FETCH FORWARD 2000 FROM qgis_571
Jul 27 16:54:17 postgresql-3 postgres[97809]: [2221-1] ::>[2023-07-27 16:54:17.744 EDT][64c2d4bf.17e11][97809/] <maps%Alternate History> LOG:  duration: 0.049 ms  statement: CLOSE qgis_571;COMMIT" role="button" style="box-sizing:border-box;display:inline-block;line-height:20px;vertical-align:middle;border-width:1px;border-style:solid;border-radius:6px;padding:0px"></span></div></div></div><div class="gmail_default" style="font-family:monospace"><p style="box-sizing:border-box;margin-top:0px;margin-bottom:16px;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px">in a later comment, I talk about how QGIS took about 28 hours to delete roughly 2,400 vertices from a roughly 23,200 vertex feature and how the ever increasing delay between the repetitive calls was 90 seconds, making these unnecessary database calls the likely culprit for making it take so long.</p><p dir="auto" style="box-sizing:border-box;margin-top:0px;margin-bottom:16px;color:rgb(31,35,40);font-family:-apple-system,BlinkMacSystemFont,"Segoe UI","Noto Sans",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji";font-size:14px"><br></p></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Jun 25, 2023 at 3:04 PM Cory Albrecht <<a href="mailto:maps@hanfastolfe.com" target="_blank">maps@hanfastolfe.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div class="gmail_default" style="font-family:monospace">I wasn't asking anybody to fix a problem, so, no, there is no ticket. I was asking why the QGIS code makes thousands of SQL reads for a very simple operation. If all that is being done is moving a single vertex, why is there even a need to read from the database at all?</div><div class="gmail_default" style="font-family:monospace"><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sat, Jun 24, 2023 at 5:12 PM Alessandro Pasotti <<a href="mailto:apasotti@gmail.com" target="_blank">apasotti@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi Cory,<br>
<br>
I think many developers have one or two ideas about where to look,<br>
they probably didn't have the time to look into it.<br>
<br>
Is there a ticket already?<br>
<br>
<br>
On Sat, Jun 24, 2023 at 8:46 PM Cory Albrecht via QGIS-Developer<br>
<<a href="mailto:qgis-developer@lists.osgeo.org" target="_blank">qgis-developer@lists.osgeo.org</a>> wrote:<br>
><br>
> So, nobody knows why QGIS makes all those seemingly wasteful SQL reads?<br>
><br>
> On Fri, Jun 16, 2023 at 1:18 PM Cory Albrecht <<a href="mailto:maps@hanfastolfe.com" target="_blank">maps@hanfastolfe.com</a>> wrote:<br>
>><br>
>> So QGIS took almost 2.5 hours to delete those 1,000-ish vertices, spitting out those seemingly unnecessary transaction blocks every second or so. Judging by the numbers of the named blocks, roughly 5,000 of them were done just to delete those 1,000-ish vertices.<br>
>><br>
>> Right now, on that same feature, I went to add a new vertx in the middle of the gap created by the deletion of the 1,000-ish last night, drag it to a new spot, and right clock to place. Those same seemingly unnecessary read-only transactions with a single select are being done, but at the rate of roughly 100 per second and the process takes about 7-8 minutes after the right click before QGIS becomes responsive again. I've done it a few times this morning, exiting QGIS without saving, and same thing each time.<br>
>><br>
>> Why so many of these database queries?<br>
>><br>
>><br>
>> On Thu, Jun 15, 2023 at 5:40 PM Cory Albrecht <<a href="mailto:maps@hanfastolfe.com" target="_blank">maps@hanfastolfe.com</a>> wrote:<br>
>>><br>
>>> Hello,<br>
>>><br>
>>> So I've been trying to track down why my postgresql server was getting so many Signal 10 (SUG BS, memory bus) errors. So far I have figured out that they are only happening when QGIS is connecting to it using GSS/Kerberos encryption. No clue yet as to which side is the culprit (e.g. bad QGIS kerberised data flow, or bad PostgreSQL handling of the connection). QGIS cinnectio with SSL encryption is fine.<br>
>>><br>
>>> Anyways, while doing this I've been watching a lot of PostgreSQL server logs, and I noticed the following behaviour of QGIS:<br>
>>><br>
>>> When using the vertex editor to delete a whole slew (1,000-ish) vertices from a polygon feature, I saw that QGIS was making repeated DB cals like this:<br>
>>><br>
>>> Jun 15 17:14:19 postgresql-3 postgres[87136]: [3204-1] ::>[2023-06-15 17:14:19.289 EDT][648b7d94.15460][87136/] <maps%Alternate History> LOG:  duration: 0.330 ms  statement: BEGIN READ ONLY;DECLARE qgis_1062 BINARY CURSOR FOR SELECT "feature_uuid"::text,"name"::text,"start_date"::text,"end_date"::text,"main_rgb"::text,"realm_uuid"::text,"stripe_rgb"::text,"border_rgb"::text,"border_shade"::text,"sovereign"::text,"type"::text,"info_url"::text,array_out("predecessors")::text,array_out("successors")::text,boolout("visible")::text,boolout("has_children")::text,"full_name"::text,"alternate_name"::text,"flag_url"::text FROM "Balance of Power"."timeline_level0" WHERE "feature_uuid"::text='fbbec581-eb8f-4068-8729-8f9d63896bd5'<br>
>>> Jun 15 17:14:19 postgresql-3 postgres[87136]: [3205-1] ::>[2023-06-15 17:14:19.290 EDT][648b7d94.15460][87136/] <maps%Alternate History> LOG:  duration: 0.628 ms  statement: FETCH FORWARD 2000 FROM qgis_1062<br>
>>> Jun 15 17:14:19 postgresql-3 postgres[87136]: [3206-1] ::>[2023-06-15 17:14:19.291 EDT][648b7d94.15460][87136/] <maps%Alternate History> LOG:  duration: 0.045 ms  statement: CLOSE qgis_1062;COMMIT<br>
>>><br>
>>> An example of the QGIS console output:<br>
>>><br>
>>> ./src/core/qgsmessagelog.cpp:29 : (logMessage) [4ms] 2023-06-15T17:37:08 PostGIS[1] NOTICE: LOG:  duration: 0.399 ms  statement: FETCH FORWARD 2000 FROM qgis_2017<br>
>>> ./src/core/qgsmessagelog.cpp:29 : (logMessage) [1ms] 2023-06-15T17:37:08 PostGIS[1] NOTICE: LOG:  duration: 0.035 ms  statement: CLOSE qgis_2017;COMMIT<br>
>>> ./src/core/qgsmessagelog.cpp:29 : (logMessage) [3ms] 2023-06-15T17:37:08 PostGIS[1] NOTICE: LOG:  duration: 0.240 ms  statement: BEGIN READ ONLY;DECLARE qgis_2018 BINARY CURSOR FOR SELECT "feature_uuid"::text,"name"::text,"start_date"::text,"end_date"::text,"main_rgb"::text,"realm_uuid"::text,"stripe_rgb"::text,"border_rgb"::text,"border_shade"::text,"sovereign"::text,"type"::text,"info_url"::text,array_out("predecessors")::text,array_out("successors")::text,boolout("visible")::text,boolout("has_children")::text,"full_name"::text,"alternate_name"::text,"flag_url"::text FROM "Balance of Power"."timeline_level0" WHERE "feature_uuid"::text='fbbec581-eb8f-4068-8729-8f9d63896bd5'<br>
>>> ./src/core/qgsmessagelog.cpp:29 : (logMessage) [2ms] 2023-06-15T17:37:08 PostGIS[1] NOTICE: LOG:  duration: 0.406 ms  statement: FETCH FORWARD 2000 FROM qgis_2018<br>
>>> ./src/core/qgsmessagelog.cpp:29 : (logMessage) [1ms] 2023-06-15T17:37:08 PostGIS[1] NOTICE: LOG:  duration: 0.034 ms  statement: CLOSE qgis_2018;COMMIT<br>
>>><br>
>>><br>
>>><br>
>>> That is repeated 4 times every couple of seconds as I wait for QGIS to finish deleting these 1,000-ish vertices. So far it's at begin block qgis_2058 (increasing sequentially) and it's been 20+ minutes that QGIS has been frozen, CPU usage for the process jumping around between 100% and 300%.<br>
>>><br>
>>> (Before you ask, Fall 2021 Dell XPS 9510, 11th Gen Intel Core i9-11900H @ 2.50GHz 8 cores, 64GB RAM.)<br>
>>><br>
>>> So why is it repeatedly making database calls to get the non geometry attributes of that same singular feature? Why can't it just use the cached data instead of making all these wasteful DB queries?<br>
>>><br>
>>> And why is it taking so long to delete the vertices? Is it related to all the unnecessary DB queries?<br>
>>><br>
>>><br>
> _______________________________________________<br>
> QGIS-Developer mailing list<br>
> <a href="mailto:QGIS-Developer@lists.osgeo.org" target="_blank">QGIS-Developer@lists.osgeo.org</a><br>
> List info: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-developer" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/qgis-developer</a><br>
> Unsubscribe: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-developer" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/qgis-developer</a><br>
<br>
<br>
<br>
-- <br>
Alessandro Pasotti<br>
QCooperative:  <a href="http://www.qcooperative.net" rel="noreferrer" target="_blank">www.qcooperative.net</a><br>
ItOpen:   <a href="http://www.itopen.it" rel="noreferrer" target="_blank">www.itopen.it</a><br>
</blockquote></div>
</blockquote></div>