From: | "Darko Prenosil" <Darko(dot)Prenosil(at)finteh(dot)hr> |
---|---|
To: | "pgsql interfaces" <pgsql-interfaces(at)postgresql(dot)org> |
Subject: | Operator class and index |
Date: | 2001-12-04 07:47:33 |
Message-ID: | 003e01c17c97$f2b4b6c0$ef00000a@darko |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-interfaces |
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 !
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Meskes | 2001-12-04 07:48:06 | Re: ECPG ERROR FOR "exec sql BEGIN" |
Previous Message | alta | 2001-12-04 05:24:00 | DBD::Pg install problem with SuSE 7.3 |