Indices and user defined operators

From: "Dmitri Bichko" <dbichko(at)aveopharma(dot)com>
To: "Sql-Postgre" <pgsql-sql(at)postgresql(dot)org>
Subject: Indices and user defined operators
Date: 2005-06-09 02:23:42
Message-ID: F18A6F7CF1661F46920F2CF713122FED46CBAF@mail.aveo.aveopharma.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2005-06-09 04:01:56 Re: Indices and user defined operators
Previous Message Alain 2005-06-09 01:13:25 Re: SELECT very slow