'Re: [postgis-users] Are covering indexes (index-only scans) possible with PostGIS?'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] Are covering indexes (index-only scans) possible with PostGIS?
From:       Marco Boeringa <marco () boeringa ! demon ! nl>
Date:       2021-10-03 7:20:31
Message-ID: 13c99fda-607a-6d00-b9cc-5d36ac764bde () boeringa ! demon ! nl
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]

Hi Anargyros,

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:

"The underlying requirement is that the index must physically store, or 
else be able to reconstruct, the original data value for each index entry."

So these spatial indexes likely can't be part of, or used, in a covering 
index.

For your point data, have you considered running CLUSTER 
(https://www.postgresql.org/docs/13/sql-cluster.html) using the GiST 
index to spatially optimize the dataset and allow faster acces?

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.

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.

Marco

Op 3-10-2021 om 04:42 schreef Anargyros Tomaras:
>
> Performance for my workload could be substantially improved by the use 
> of covering indexes 
> <https://www.postgresql.org/docs/11/indexes-index-only-scans.html>.
>
> I have tried but I have been unable to make them work with my GiST 
> geometry indexes (points).
>
> The latest versions I have tried against were PostgreSQL v13 and 
> PostGIS v3.1.1.
>
> I am pretty sure there must be a good reason behind this limitation 
> and I was wondering what that reason is.
>
> Thank you.
>
> Anargyros
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

[Attachment #5 (text/html)]

<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>

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread] 