Skip site navigation (1) Skip section navigation (2)

Re: Operator class and index

From: "Darko Prenosil" <Darko(dot)Prenosil(at)finteh(dot)hr>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "pgsql interfaces" <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: Operator class and index
Date: 2001-12-07 18:50:24
Message-ID: 007a01c17f64$591cc780$ef00000a@darko (view raw or flat)
Thread:
Lists: pgsql-interfaces
----- 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


In response to

Responses

pgsql-interfaces by date

Next:From: Tim BoringDate: 2001-12-10 03:56:31
Subject: Problem with libpq++
Previous:From: Tim BarnardDate: 2001-12-07 15:58:21
Subject: Re: C interface libpq.so.2 problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group