On Mon, 2007-09-10 at 11:40 +0200, Stefan Zweig wrote: > Right, RECHECK && seems to be enabled by default for gist indices. Why is > that needed and how can I disable the RECHECK clause? In my case, SRID > lookups are not necessary when a CHECK (srid(the_geom)=4326) constraint is > used since all imported objects are guaranteed to have 4326 then, or am I > wrong? I wonder what consequences would have changes or insertions of > Geometry objects if I do disable the RECHECK clause? Aren't such changes > stored into the gist index back again automatically?
The RECHECK clause means that for an entry that is read from the index for a given operator, the corresponding heap operator is also applied before the final result is returned. This allows PostgreSQL to support lossy indices. In the case of PostGIS, the RECHECK clause is really there to enforce the SRID check, as at the time some people were insistent that they wanted mixed SRID columns, even though building an index on a mixed-SRID column is non-sensical. You can remove the recheck clause by executing a "DROP OPERATOR CLASS gist_geometry_ops" statement to remove the existing opclass and then copy the "CREATE OPERATOR CLASS gist_geometry_ops" command from lwpostgis.sql but without the RECHECK keywords. > I further still do not understand why the RECHECK times for the the_geom and > bbox columns differ: It seems that the cached Geometry bounding box is *not* > used and the whole Geometry object is scanned, *even if* hasbbox(the_geom) > returns true for all objects. In my eyes this decreases strongly and > unnecessarily the performance of certain bounding box queries. For example, imagine that your index search returns a single country outline as a match. With a RECHECK clause, the corresponding full geometry has to be read in from disk and decompressed into memory. Once this is done, the bounding box extracted *from the full geometry* is used. In the case of a BBOX, the corresponding RECHECK is performed on the small fixed-size BBOX type - no need to spend the time loading and decompressing the full geometry into memory. HTH, Mark. -- ILande - Open Source Consultancy http://www.ilande.co.uk _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
