Thread: Operator clasess and index
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.
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';
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 );
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');
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.
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 !