Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Indices and user defined operators
Date: Wed, 8 Jun 2005 22:23:42 -0400
Message-ID: <F18A6F7CF1661F46920F2CF713122FED46CBAF@mail.aveo.aveopharma.com>
Thread-Topic: Indices and user defined operators
Thread-Index: AcVsmkGWttVXSPG7RxGBmV9BtSEqKA==
From: "Dmitri Bichko" <dbichko@aveopharma.com>
To: "Sql-Postgre" <pgsql-sql@postgresql.org>

Being lazy, I've created a set of case incensitive text comparison
operators: =3D*, <*, >*, and !=3D*; 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 =3D* 'baz';=20

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=3D0.00..8696.81 rows=3D1324 width=3D34)
   Filter: (upper(upper((bar)::text)) =3D '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) =3D 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