Enable Index Only Scan on geometry

[x4m]

GiST support IoS since PostgreSQL 9.6, but geometry opclass do not have necessary function. It is easy to add this function by simply calling alter operator family gist_geometry_ops_nd using gist add function 9 (geometry,geometry) geometry_gist_decompress_nd (internal); The trick is this query reuse geometry_gist_decompress_nd function, because fetch function for geometry is same no-op. For future extensibility reasons it is viable to copy geometry_gist_decompress_nd() into geometry_gist_fetch_nd().

This will allow something like this (when IoS is more performant than other plans) postgres=# set enable_seqscan = off; SET postgres=# set enable_bitmapscan = false ; SET postgres=# explain select c from datatable where c && ST_Expand(ST_MakePoint(0.1,0.1),0.1);

QUERY PLAN

---

Index Only Scan using idx on datatable (cost=0.29..83264.29 rows=40839 width=32)

Filter: (c && '010300000001000000050000000000000000000000000000000000000000000000000000009A9999999999C93F9A9999999999C93F9A9999999999C93F9A9999999999C93F000000000000000000000000000000000000000000000000'::geometry)

(2 rows)

I can submit a patch if you point out how to update this things properly in PostGIS.

[x4m]

Oh, now I see that "preview" button is there for a reason. I cannot find a way to edit the message to use proper formatting, sorry. SQL for enabling IoS

ALTER OPERATOR family gist_geometry_ops_nd USING gist ADD FUNCTION 9 (geometry, geometry) geometry_gist_decompress_nd (internal);

[komzpa]

Hello,

gist geometry index "compresses" geometry into its bounding box. It is impossible to get back a valid representation, even for points, as SRID is lost on this conversion. Only way is to go back to the heap and read it from there.

Is there some clever hack around this, or you haven't checked query output?

[x4m]

Replying to komzpa:

Hello,

gist geometry index "compresses" geometry into its bounding box. It is impossible to get back a valid representation, even for points, as SRID is lost on this conversion. Only way is to go back to the heap and read it from there.

Is there some clever hack around this, or you haven't checked query output?

True, I missed call to gserialized_datum_get_gidx_p() in gserialized_gist_compress()

Compression is irreversible.

[x4m]

Certainly, clever hacks are possible, I can produce new opclass for IoS which is not doing irreversible compression. And it will be IoS-able. But I'm not sure it worth it, in my benchmarks for cube IoS vs Index Scan was like 90 seconds to 100 seconds.