Re: Operator class and index - Mailing list pgsql-interfaces

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Darko Prenosil" <Darko.Prenosil@finteh.hr>
Cc: "pgsql interfaces" <pgsql-interfaces@postgresql.org>
Sent: Tuesday, December 04, 2001 4:11 PM
Subject: Re: [INTERFACES] Operator class and index


> "Darko Prenosil" <Darko.Prenosil@finteh.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;
nLenMatchINTEGER;           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
WHEREamname = '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_operatoro, 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_operatoro, 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_operatoro, 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_operatoro, 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_operatoro, 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_procp       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