[postgis-devel] Gserialized vs. non-serialized &&

[Paragon Corporation]

Paul,

I'm a bit puzzled.  What is the difference between (geometry_gistjoinsel,
geometry_gist_sel),
(geography_gist_selectivity,geography_gist_join_selectivity), and (contsel,
contjoinsel).
I was flipping between them for your gserialized && and didn't notice any
difference in speed or answers when using any of them.  I was expecting it
would break or the speeds would be at least different.
Which makes me wonder why do we have all these variants in the first place.
The contsel, contjoinsel is built into PostgreSQL -- if the speed is the
same in all, why did we invent our own PostGIS
specific for geography and geometry.

In trying to figure out what raster should be changed to in

http://trac.osgeo.org/postgis/ticket/758

I noticed that for the gserialized case your && looks like
-- gserialized  (and evidently I assume you used to use geometry_gistjoinsel
, geometry_gist_sel since that code is remarked out for gserialized)
CREATE OPERATOR &&(
  PROCEDURE = geometry_2d_overlaps,
  LEFTARG = geometry,
  RIGHTARG = geometry,
  COMMUTATOR = &&,
  RESTRICT = contsel,
  JOIN = contjoinsel);

-- The regular geometry one hasn't changed since PostGIS 1.5 and uses the
geometry_gistjoinsel, geometry_gist_sel (which I think Jorge mistakenly
assumed
In his GSERIALIZED check was set aside for GSERIALIZED and regular used
postgis_gistjoinsel (which hasn't been used since PostGIS 1.4 I think (it
was renamed in postGIS 1.5 to geometry_gist... I assume to distinguish
between geography and geometry selectors)


Anyrate getting to the point.  I was expecting the gserialized to look
similar to the geography && which looks like:
CREATE OPERATOR &&(
  PROCEDURE = geography_overlaps,
  LEFTARG = geography,
  RIGHTARG = geography,
  COMMUTATOR = &&,
  RESTRICT = geography_gist_selectivity,
  JOIN = geography_gist_join_selectivity);

So what exactly does this RESTRICT/JOIN do and when does it kick in?

FWIW:  Its not just the new gserialized index operator that is slower, but
even without a spatial index the gserialized && is slower:

Here are some tests I did with my 100,316 some record parcel dataset  (for
the gserialized case it doesn't seem to make  a difference in timings or
answer if I swap out the RESTRICT/JOIN 
Functions with geography_gist_* or geometry_gist_*

-- 1.5 (2.0 non-gserialized) index 125 ms, Answer: 7358, no index 25,709ms
Answer:  7358
-- 2.0 gserialized index 998ms, no index: 29,172 ms Answer: 7358
SELECT count(p1.the_geom) 
from par35 As p1 INNER JOIN par35 As p2 ON p1.the_geom && p2.the_geom
WHERE p2.map_id LIKE '0101%';

Thanks,
Regina