Home > mailing lists

Thread: Operator class and index

Operator class and index

From

"Darko Prenosil"

Date:

04 December 2001, 05:56:45

Maybe this is not right thread to ask this question, and I wrote to pgsql-general too,

but I'll ask anyway.

I have question for someone who is familiar with indexes and index classes.

I tried to write a "begins with" operator to use it instead of LIKE 'xx%',
because LIKE 'xx%' can't use index.

These are steps I did:

1. Created function that returns true if "strInput" begins with "strMatch":

CREATE OR REPLACE FUNCTION begins_with(varchar, varchar) RETURNS boolean AS '
     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
         RETURN false;
     END IF;
     IF substr(strInput,1,nLenMatch)=strMatch THEN
         RETURN true;
     END IF;
     RETURN false;
END;' LANGUAGE 'plpgsql';

2. Created operator <==
DROP OPERATOR <== (varchar,varchar);
CREATE OPERATOR <==
  ( leftarg = varchar,
  rightarg = varchar,
  procedure = begins_with , restrict = eqsel, join = eqjoinsel );

3. Inserted record for begins_with in pg_ampop:
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_ops'),
       6,
       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');

4. Created index on table:
CREATE INDEX ix_zemlje_naziv ON zemlje USING btree(naziv);

5. When I try to get plan for this :
EXPLAIN SELECT * from zemlje WHERE naziv<=='A';

I got :
Index Scan using ix_zemlje_naziv on zemlje (cost=0.00..17.07 rows=5 width=405)

It seem that everything is ok, but when I execute the query I got empty recordset.
Function begins_with returns true, I did check it, but recordset is empty.

When I skip step 3, results are fine, but operator is not using index !

Is there way to do this ?

Best regards !

Re: Operator class and index

From

Tom Lane

Date:

04 December 2001, 13:26:47

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

> 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

Re: Operator class and index

From

"Darko Prenosil"

Date:

07 December 2001, 19:17:57

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

getting value of just inserted rows

From

lexx_h@chat.ru

Date:

19 April 2002, 12:27:03

Hello all.
Please, help me,

How can I get single field (attribute) value just inserted new row
into the table  (by not using select query to find this row)?

(type of that field is serial),

---
lexx