[postgis-devel] Geometry Index on GSERIALIZED

Paul Ramsey pramsey at opengeo.org
Fri Dec 17 16:44:44 PST 2010

Well, I have my first proof-of-concept test turning over using the
GSERIALIZED index binding, shared with geography, on a geometry table
(the operator is a triple &&& to avoid colliding with the existing
one, for now).

Nothing working is committed yet, but it's getting closer. I think
next I need to work through the online regression failures that occur
when GSERIALIZED_ON is set. Then I should be able to flip over to the
new format relatively quickly.

Now, the new index is multi-dimensional, which means it builds out to
the maximum dimensionality necessary to index the inputs. That is all
good. An interesting issue occurs when querying it, however... as it
stands, if you query a 3d table with a 2d object, the 2d object is
projected onto the zero plane of the higher dimensions and then the
query is run. So the query below carried out with a 2d linestring
returns no answers. The solution is pretty clear in changing the
gidx_overlaps and gidx_contains function definitions, but it's a core
change to the existing index, so I will proceed carefully. The change
will cause lower dimensional objects to have full coverage of the
higher dimensions, so a 2d object will operate like a cylinder in the
higher space, rather than a piece of paper.

Fun fun, maybe we'll have a Christmas Miracle (n-d indexing and
gserialized storage committed and working).


postgis20=# select count(*) from random_points where pt &&&
'LINESTRING(10000 10000 10000, 20000 20000 20000)'::geometry;
(1 row)

postgis20=# explain analyze select count(*) from random_points where
pt &&& 'LINESTRING(10000 10000 10000, 20000 20000 20000)'::geometry;

 Aggregate  (cost=39301.62..39301.63 rows=1 width=0) (actual
time=1.360..1.360 rows=1 loops=1)
   ->  Bitmap Heap Scan on random_points  (cost=22455.62..38051.62
rows=500000 width=0) (actual time=1.347..1.349 rows=2 loops=1)
         Recheck Cond: (pt &&&
         ->  Bitmap Index Scan on rpg  (cost=0.00..22330.62
rows=500000 width=0) (actual time=1.336..1.336 rows=2 loops=1)
               Index Cond: (pt &&&
 Total runtime: 1.458 ms
(6 rows)

More information about the postgis-devel mailing list