<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
</head>
<body>
<p>Hi Anargyros,<br>
</p>
<p>If I understand you well, you expect the spatial index to be part
of the covering index? Spatial indexes like GiST however, do not
store the original geometry as part of the index. This is in
violation of the requirement that is described in the Help page
you refer to:</p>
<p>"The underlying requirement is that the index must physically
store, or else be able to reconstruct, the original data value for
each index entry."</p>
<p>So these spatial indexes likely can't be part of, or used, in a
covering index.</p>
<p>For your point data, have you considered running CLUSTER
(<a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/13/sql-cluster.html">https://www.postgresql.org/docs/13/sql-cluster.html</a>) using the
GiST index to spatially optimize the dataset and allow faster
acces?</p>
<p>You might also attempt dropping the GiST index after running
CLUSTER, and subsequently creating a BRIN spatial index on the
geometry column if the dataset is truly huge (and the associated
GiST index cannot fit in memory). BRIN indexes are far smaller and
can usually fit in memory, but require the data to be spatially
clustered to be efficient, so that is why you need to CLUSTER the
data first.</p>
<p>In my limited experience using BRIN, it is slower for polygon and
line data, but I guess that for really large point datasets that
are properly spatially clustered, it might well perform better.
Also, I think there were some improvements to BRIN in the latest
PostgreSQL/PostGIS versions. It may at least be worth a try.<br>
</p>
<p>Marco<br>
</p>
<div class="moz-cite-prefix">Op 3-10-2021 om 04:42 schreef Anargyros
Tomaras:<br>
</div>
<blockquote type="cite"
cite="mid:BY5PR05MB71377C44732521686FD1E861AEAD9@BY5PR05MB7137.namprd05.prod.outlook.com">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<style>@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}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]-->
<div class="WordSection1">
<p class="MsoNormal">Performance for my workload could be
substantially improved by the use of
<a
href="https://www.postgresql.org/docs/11/indexes-index-only-scans.html"
moz-do-not-send="true">covering indexes</a>.<o:p></o:p></p>
<p class="MsoNormal">I have tried but I have been unable to make
them work with my GiST geometry indexes (points).<o:p></o:p></p>
<p class="MsoNormal">The latest versions I have tried against
were PostgreSQL v13 and PostGIS v3.1.1.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I am pretty sure there must be a good
reason behind this limitation and I was wondering what that
reason is.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thank you.<o:p></o:p></p>
<p class="MsoNormal">Anargyros<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>