Re: Operator class and index

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Darko Prenosil" <Darko(dot)Prenosil(at)finteh(dot)hr>
Cc: "pgsql interfaces" <pgsql-interfaces(at)postgresql(dot)org>
Sent: Tuesday, December 04, 2001 4:11 PM
Subject: Re: [INTERFACES] 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.
>

Yes, I'm using latin2 !!!
I did not try to write another LIKE operator, that can match pattern
anywhere in the search word,
but only at the begining of the word. Finaly I succed to write such operator
as You can see below.

> > 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.
>

O.K. I was foolish and did not read carefuly the documentation, I know now
that
for btree index strategies are:
1 <
2 <=
3 =
4 >=
5 >

When You explain to me that I can't make operators with strategy numbers at
my own will, I did create
new operator class called "varchar_begins_ops":

CREATE OR REPLACE FUNCTION varchar_begins_with(varchar, varchar) RETURNS
boolean AS '
--Operator greater or equal
DECLARE strInput ALIAS FOR $1;
strMatch ALIAS FOR $2;
nLenMatch INTEGER;
nLenStr INTEGER;
BEGIN
nLenStr=char_length(strInput);
nLenMatch=char_length(strMatch);
IF nLenStr<nLenMatch THEN
nLenMatch=nLenStr;
END IF;
IF substr(strInput,1,nLenMatch)=strMatch THEN
RETURN true;
END IF;
RETURN false;
END;
' LANGUAGE 'plpgsql';

CREATE OPERATOR |<
( leftarg = varchar,
rightarg = varchar,
procedure = varcharlt , restrict = eqsel, join = eqjoinsel );

CREATE OPERATOR |<=
( leftarg = varchar,
rightarg = varchar,
procedure = varcharle , restrict = eqsel, join = eqjoinsel );

CREATE OPERATOR |=
( leftarg = varchar,
rightarg = varchar,
procedure = varchareq , restrict = eqsel, join = eqjoinsel );

CREATE OPERATOR |>=
( leftarg = varchar,
rightarg = varchar,
procedure = varchar_begins_with , restrict = eqsel, join = eqjoinsel );

CREATE OPERATOR |>
( leftarg = varchar,
rightarg = varchar,
procedure = varchargt , restrict = eqsel, join = eqjoinsel );

INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype)
VALUES (
(SELECT oid FROM pg_am WHERE amname = 'btree'),
'varchar_begins_ops',
(SELECT oid FROM pg_type WHERE typname = 'varchar'),
true,
0);

INSERT INTO pg_amop
(amopclaid, amopstrategy, amopreqcheck, amopopr)
SELECT
(SELECT pg_opclass.oid FROM pg_opclass WHERE opcamid = (SELECT oid
FROM pg_am WHERE amname = 'btree') AND opcname = 'varchar_begins_ops'),
1,
false,
(SELECT o.oid FROM pg_operator o, pg_type t WHERE o.oprname = '|<'
and o.oprleft = t.oid and o.oprright = t.oid and t.typname = 'varchar');

INSERT INTO pg_amop
(amopclaid, amopstrategy, amopreqcheck, amopopr)
SELECT
(SELECT pg_opclass.oid FROM pg_opclass WHERE opcamid = (SELECT oid
FROM pg_am WHERE amname = 'btree') AND opcname = 'varchar_begins_ops'),
2,
false,
(SELECT o.oid FROM pg_operator o, pg_type t WHERE o.oprname = '|<='
and o.oprleft = t.oid and o.oprright = t.oid and t.typname = 'varchar');

INSERT INTO pg_amop
(amopclaid, amopstrategy, amopreqcheck, amopopr)
SELECT
(SELECT pg_opclass.oid FROM pg_opclass WHERE opcamid = (SELECT oid
FROM pg_am WHERE amname = 'btree') AND opcname = 'varchar_begins_ops'),
3,
false,
(SELECT o.oid FROM pg_operator o, pg_type t WHERE o.oprname = '|='
and o.oprleft = t.oid and o.oprright = t.oid and t.typname = 'varchar');

INSERT INTO pg_amop
(amopclaid, amopstrategy, amopreqcheck, amopopr)
SELECT
(SELECT pg_opclass.oid FROM pg_opclass WHERE opcamid = (SELECT oid
FROM pg_am WHERE amname = 'btree') AND opcname = 'varchar_begins_ops'),
4,
false,
(SELECT o.oid FROM pg_operator o, pg_type t WHERE o.oprname = '|>='
and o.oprleft = t.oid and o.oprright = t.oid and t.typname = 'varchar');

INSERT INTO pg_amop
(amopclaid, amopstrategy, amopreqcheck, amopopr)
SELECT
(SELECT pg_opclass.oid FROM pg_opclass WHERE opcamid = (SELECT oid
FROM pg_am WHERE amname = 'btree') AND opcname = 'varchar_begins_ops'),
5,
false,
(SELECT o.oid FROM pg_operator o, pg_type t WHERE o.oprname = '|>'
and o.oprleft = t.oid and o.oprright = t.oid and t.typname = 'varchar');

INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
SELECT opcl.oid, 1, p.oid
FROM pg_opclass opcl, pg_proc p
WHERE
opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') AND
opcname = 'varchar_begins_ops' AND
p.proname = 'varcharcmp';

As You can see I replaced only function for operator >=, and other operators
are set to equivalent functions
as for "varchar_ops" operator class.

I indexed table zemlje as follows:
CREATE index ix_b_zemlje on zemlje(naziv varchar_begins_ops);

here is execution plan for : explain select * from zemlje where naziv |>=
'A'

Index Scan using ix_b_zemlje on zemlje (cost=0.00..6.17 rows=1
width=405)

It is working just fine !!! Result sets are also O.K.

Thanks !!!

Darko