[postgis-users] question on gist performance

[Mark Cave-Ayland]

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