[postgis-users] Re: [PERFORM] Bad query optimizer misestimation because of TOAST

[strk at refractions.net]

On Thu, Feb 03, 2005 at 11:30:32AM +0100, Markus Schaber wrote:
(cut)
> I do not know whether PostGIS actually rechecks against the real
> geometry. If the app needs the bbox check (&& operator), then the lossy
> index contains just all the information. If a real intersection is
> needed, PostGIS users usually use "(column && bbox_of_reference) AND
> intersects(column, reference)". This uses the bbox based index for
> efficient candidate selection, and then uses the rather expensive
> geometric intersection algorithm for real decision.

PostGIS always recheck, only to handle SRID consistency.
This is one of the problems Paul was ranting about in a previous
post ;). If no RECHECK is performed, queries using index
will not notice SRID mismatch, while those using a sequencial
scan will.

Of course, we might give power to the user and assume they know
what they are doing, thus never checking for SRID and never
RECHECKING. 

I wonder if dropping the RECHECK is another workaround for your
problem. Can you check it out ?

> But maybe there'll be a real intersection Operator in the future, that
> makes use of the bbox index in the first stage.

Having a *real* intersection operator would probably just
require another strategy number in the gist_geometry_ops
operator class, the RECHECK clause would then make use
of the full geometry.

--strk;