Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#3932 closed defect (invalid)

Enable Index Only Scan on geometry

Reported by: x4m Owned by: pramsey
Priority: medium Milestone: PostGIS 2.4.2
Component: postgis Version: 2.4.x
Keywords: Cc:

Description

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.

Change History (5)

comment:1 Changed 2 years ago by 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);

comment:2 Changed 2 years ago by 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?

comment:3 in reply to:  2 Changed 2 years ago by 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.

comment:4 Changed 2 years ago by x4m

Resolution: invalid
Status: newclosed

comment:5 Changed 2 years ago by 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.

Note: See TracTickets for help on using tickets.