Thread: Indices and user defined operators

Being lazy, I've created a set of case incensitive text comparison
operators: =*, <*, >*, and !=*; the function for each just does an
UPPER() on both arguments and then uses the corresponding builtin
operator.

What would make these REALLY useful, is if when running something like:

SELECT * FROM foo WHERE bar =* 'baz';

postgres would know to use an index defined as:

CREATE INDEX idx_foo_bar ON foo (UPPER(bar));

Currently, the explain I get for the above situation is:

Seq Scan on foo  (cost=0.00..8696.81 rows=1324 width=34)  Filter: (upper(upper((bar)::text)) = 'BAZ'::text)

I am vaguely aware of the concept of operator classes, but from what I
understand, the op class has to be specified at index creation time;
seeing how this is just to save a little typing, I'd rather not have to
rely on these ops and opclasses being defined for the rest of the
database to work.  Also I need to still be able to do SELECT * FROM foo
WHERE UPPER(bar) = UPPER('baz') and have it use the same index.

So, is there any way to make these operators use an index defined as
above?

Thanks,
Dmitri

"Dmitri Bichko" <dbichko@aveopharma.com> writes:
> So, is there any way to make these operators use an index defined as
> above?

If you've set things up so that the operators are defined by inline-able
SQL functions, I'd sort of expect it to fall out for free ...
        regards, tom lane

I wrote:
> "Dmitri Bichko" <dbichko@aveopharma.com> writes:
>> So, is there any way to make these operators use an index defined as
>> above?

> If you've set things up so that the operators are defined by inline-able
> SQL functions, I'd sort of expect it to fall out for free ...

Here's a quick proof-of-concept:

regression=# create function iequal(text,text) returns bool as
regression-# 'select upper($1) = upper($2)' language sql strict immutable;
CREATE FUNCTION
regression=# create operator *= (procedure = iequal, leftarg = text,
regression(# rightarg = text , commutator = *= );
CREATE OPERATOR
regression=# explain select * from text_tbl where f1 *= 'foo';                      QUERY PLAN                        
---------------------------------------------------------Seq Scan on text_tbl  (cost=0.00..1.03 rows=1 width=32)
Filter:(upper(f1) = 'FOO'::text)
 
(2 rows)

regression=# create index fooi on text_tbl(upper(f1));
CREATE INDEX
regression=# set enable_seqscan TO 0;  -- because my test table is tiny
SET
regression=# explain select * from text_tbl where f1 *= 'foo';                             QUERY PLAN
          
 
----------------------------------------------------------------------Index Scan using fooi on text_tbl
(cost=0.00..4.68rows=1 width=32)  Index Cond: (upper(f1) = 'FOO'::text)
 
(2 rows)

This is with CVS tip, but I'm pretty sure it works as far back as 7.4.
        regards, tom lane

Tom Lane wrote:
> I wrote:
> 
>>"Dmitri Bichko" <dbichko@aveopharma.com> writes:
>>
>>>So, is there any way to make these operators use an index defined as
>>>above?
> 
> 
>>If you've set things up so that the operators are defined by inline-able
>>SQL functions, I'd sort of expect it to fall out for free ...

Now *that* is very nice. Thanks for the example Tom - nice to know the 
sql list is still teaching me things within the first 5 messages I read.

--   Richard Huxton  Archonet Ltd