Hi everyone,

I'm trying to mark a GiST index as lossy using the RECHECK operator as
part of some work on PostGIS, but what happens is that the original
operator function is never reapplied to the results of the index scan.
The operator class and operator definitions looks like this:

CREATE OPERATOR && (
   LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE =
geometry_overlap,
   COMMUTATOR = '&&',
   RESTRICT = postgis_gist_sel, JOIN = positionjoinsel
);

and:

CREATE OPERATOR CLASS gist_geometry_ops
	DEFAULT FOR TYPE geometry USING gist AS
	OPERATOR	1	<<	RECHECK,
	OPERATOR	2	&<	RECHECK,
	OPERATOR	3	&&	RECHECK,
	OPERATOR	4	&>	RECHECK,
	OPERATOR	5	>>	RECHECK,
	OPERATOR	6	~=	RECHECK,
	OPERATOR	7	~	RECHECK,
	OPERATOR	8	@	RECHECK,
	FUNCTION	1	ggeometry_consistent (internal,
geometry, int4),
	FUNCTION	2	gbox_union (bytea, internal),
	FUNCTION	3	ggeometry_compress (internal),
	FUNCTION	4	rtree_decompress (internal),
	FUNCTION	5	gbox_penalty (internal, internal,
internal),
	FUNCTION	6	gbox_picksplit (internal, internal),
	FUNCTION	7	gbox_same (box, box, internal);


What I'm expecting is that since RECHECK is specified, PostgreSQL will
identify the index entries using the && operator and then call
geometry_overlap() function with the full tuples from the heap. However,
it seems geometry_overlap() is never called from an index scan made
using the && operator :( Can anyone point out where I'm going wrong?


Many thanks,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.



Sun, 13 Jun 2004 22:23:10 +0100
"Mark Cave-Ayland" <m.cave-ayland@webbased.co.uk>
Can get GiST RECHECK clause to work
"Mark Cave-Ayland" <m.cave-ayland@webbased.co.uk> writes:
> I'm trying to mark a GiST index as lossy using the RECHECK operator as
> part of some work on PostGIS, but what happens is that the original
> operator function is never reapplied to the results of the index scan.

You sure?  I'm pretty sure that a number of the contrib gist index
opclasses would fail their regression tests if this were broken.

As of 7.5 you cannot see the reapplication in the generated plan's
filter condition; perhaps that got you confused?

2004-01-05 23:31  tgl

	* src/: backend/executor/nodeIndexscan.c,
	backend/nodes/copyfuncs.c, backend/nodes/outfuncs.c,
	backend/optimizer/path/costsize.c,
	backend/optimizer/plan/createplan.c,
	backend/optimizer/plan/setrefs.c, include/nodes/execnodes.h,
	include/nodes/plannodes.h: Instead of rechecking lossy index
	operators by putting them into the regular qpqual ('filter
	condition'), add special-purpose code to nodeIndexscan.c to recheck
	them.  This ends being almost no net addition of code, because the
	removal of planner code balances out the extra executor code, but
	it is significantly more efficient when a lossy operator is
	involved in an OR indexscan.  The old implementation had to recheck
	the entire indexqual in such cases.


			regards, tom lane

Sun, 13 Jun 2004 18:08:45 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Can get GiST RECHECK clause to work 