[postgis-users] Are covering indexes (index-only scans) possible with PostGIS?
Marco Boeringa
marco at boeringa.demon.nl
Sun Oct 3 00:20:31 PDT 2021
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 at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20211003/3cd1b05b/attachment.html>
More information about the postgis-users
mailing list