<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.E-MailFormatvorlage17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="DE" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal">Dear List,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">in version 3.10 LTR when connecting to a PostgreSQL database with the Data Source Manager, QGIS performs a query to determine all occurring geometry types in tables and views that have the unrestricted geometry type GEOMETRY.<o:p></o:p></p>
<p class="MsoNormal">In one of our main databases (size about 65 GB) this query needs 130 GB of temporary disc space and causes an incident, when the storage on the server runs out. <o:p></o:p></p>
<p class="MsoNormal">Of course the user can prevent this by checking the „Don’t resolve type of unrestricted columns (GEOMTRY)“, but if this is not default, most users even don’t understand the meaning of the option.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">The statement QGIS sends to the database is:<o:p></o:p></p>
<p class="MsoNormal"><i>SELECT 0, array_agg(DISTINCT 25832::text || ':' || UPPER(geometrytype("geometrie"))) FROM "xxxx"."yyyyy"
<o:p></o:p></i></p>
<p class="MsoNormal"><i>UNION <o:p></o:p></i></p>
<p class="MsoNormal"><i>SELECT 1, …<o:p></o:p></i></p>
<p class="MsoNormal">and so on for 148 tables.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Because there were obviosly changes in this part of the program between 3.4 LTR and 3.10 LTR, we examined this query in 3.4 too.<o:p></o:p></p>
<p class="MsoNormal">There is one single query generated for each table instead of one query for all tables together connected with UNION.<o:p></o:p></p>
<p class="MsoNormal">This was much more ressource-friendly than the current query is.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">A test in version 3.16 showed the same query as in 3.10 LTR is used. <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Does anyone know, why the query was changed between 3.4 an 3.10?<o:p></o:p></p>
<p class="MsoNormal">Maybe there is a way to modify the query once more to avoid database incidents.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thank you and kind regards<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Dirk<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<hr>
<p></p>
<font face="Arial">Monatlich aktuelle Informationen Ihrer Stadtverwaltung in unserem Newsletter!
<a href="https://www.stadt-koeln.de/service/onlinedienste/newsletter-anmeldung?para=allgemein">
Newsletter Anmeldung</a><br>
<br>
</font>
<hr>
<p></p>
<font face="Arial"><a href="https://www.bundesregierung.de/breg-de/themen/corona-warn-app"><img src="https://styleguide.bundesregierung.de/resource/blob/72496/1760346/6d7f611945ca42908c50804510c5335b/breg-vorschaubild-01-unterstuetzt-842x595px-jpg-srgb-v01-data.png" width="400"></a>
<p></p>
</font>
</body>
</html>