Re: Operator class and index

"Darko Prenosil" <Darko(dot)Prenosil(at)finteh(dot)hr> writes:
> I tried to write a "begins with" operator to use it instead of LIKE 'xx%',
> because LIKE 'xx%' can't use index.

Why not? It should be able to --- unless you're using a non-C locale,
in which case the problem is fundamental and not easily worked around
just by creating another operator.

> 3. Inserted record for begins_with in pg_ampop:

You can't just invent any old operator and then plop it into pg_amop
with a randomly-chosen strategy number. Btree indexes only know about
strategy numbers 1 to 5, and those numbers have very definite
implications about the semantics of the operator: the operator had
better behave as <, <=, =, >=, or > (not sure which one is which number)
with respect to the standard sort ordering of the indexed datatype.
The other index types also have preconceived notions about the meaning
of the strategy numbers that they understand.

> 5. When I try to get plan for this :
> EXPLAIN SELECT * from zemlje WHERE naziv<=3D=3D'A';=20
> =20
> I got :
> Index Scan using ix_zemlje_naziv on zemlje (cost=3D0.00..17.07 rows=3D5 w=
> idth=3D405)
> =20
> It seem that everything is ok, but when I execute the query I got empty rec=
> ordset.

I'm surprised you didn't get an Assert failure. The nbtree routines
have no idea what to do with strategy number 6.

regards, tom lane