To: "Dmitri Bichko" <dbichko@aveopharma.com>
Cc: "Sql-Postgre" <pgsql-sql@postgresql.org>
Subject: Re: Indices and user defined operators 
In-reply-to: <6338.1118289716@sss.pgh.pa.us> 
References: <F18A6F7CF1661F46920F2CF713122FED46CBAF@mail.aveo.aveopharma.com>
	<6338.1118289716@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Thu, 09 Jun 2005 00:01:56 -0400"
Date: Thu, 09 Jun 2005 01:27:04 -0400
Message-ID: <7273.1118294824@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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.68 rows=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