Peter Eisentraut <peter_e@gmx.net> writes:
> Bruce Momjian writes:
> A match in the index needs to be reevaluated in the heap tuple data,
> since a match in the index does not necessarily mean, that the heap tuple
> matches.
> AFAIK, this is true for all indexes in PostgreSQL, because index rows
> don't store the transactions status.
Not true at all. The tuple commit status needs to be rechecked, yes,
but with a normal index it is not necessary to recheck whether the index
key field actually satisfies the index qual conditions. With a lossy
index it *is* necessary to recheck --- the index may return more tuples
than the ones that match the given qual. For example, an r-tree index
applied to a "distance from point X <= D" query might return all the
tuples lying within a bounding box of the circle actually wanted.
The LIKE index optimization can also be thought of as using an index as
a lossy index: the index scan gives you all the tuples you want, plus
some you don't, so you have to evaluate the LIKE operator over again at
each returned tuple.
Basically, what this is good for is using an index for more kinds of
WHERE conditions than it could otherwise support. It is *not* a useless
abstraction. It occurs to me though that marking the index itself
as lossy is the wrong way to think about it --- the right way is to
associate the "lossy" flag with use of a particular operator with an
index. So maybe the flag should be in pg_amop or pg_amproc, instead.
Someday I'd also like to see those tables extended so that the LIKE
index optimization is described by the tables, rather than being
hard-wired into the planner as it is now.
regards, tom lane