Re: partial index on varchar-coloumn in 7.4.1 - Mailing list pgsql-novice

From Tom Lane
Subject Re: partial index on varchar-coloumn in 7.4.1
Date
Msg-id 15958.1073596495@sss.pgh.pa.us
Whole thread Raw
In response to Re: partial index on varchar-coloumn in 7.4.1  (Bruno Wolff III <bruno@wolff.to>)
List pgsql-novice
Bruno Wolff III <bruno@wolff.to> writes:
> This implication relies on = being transitive. I was curious about how you
> test for that since there doesn't seem to be a direct way to know that.
> My guess would be that operators used in an opclass are assumed to be
> transitive, since I don't think indexing would work if they weren't.

Exactly.  The predicate tester uses operators that are in btree
opclasses.  The assumptions required for a working btree index are
AFAICS more than sufficient for the purposes here; in particular a
btree requires a total ordering on the data type, so this holds:

> You can do a bit better if you know that exactly one of the relations
> =, <, > is true for any ordered pair of operands.

> This same test would also work for other transitive operators with a negator.
> For example:
> a < x implies y >= a if y >= x

Right, but since all those things are already in opclasses, I don't
think it matters.  The problem with <> is that it is *not* part of
btree opclasses (an ancient Berkeley decision, presumably based on the
assumption that <> would never be a useful index scan constraint).
What I added to the code yesterday is the ability to look to see if the
given operator has a negator that is an "=" member of a btree opclass.
If so (and if the negator marking is semantically correct) then the
operator must behave as <>, and the other members of the opclass can be
used with it in these sorts of syllogisms.

            regards, tom lane

pgsql-novice by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: partial index on varchar-coloumn in 7.4.1
Next
From: Marcin Gil
Date:
Subject: Re: pgsql 7.0 recovery to 7.4