From: Dmitri Bichko <dbichko@aveopharma.com>
To: Sql-Postgre <pgsql-sql@postgresql.org>
Subject: Indices and user defined operators
Date: Wed, 8 Jun 2005 22:23:42 -0400
Message-ID: <F18A6F7CF1661F46920F2CF713122FED46CBAF@mail.aveo.aveopharma.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dbichko@aveopharma.com
Subject: Re: Indices and user defined operators
In-Reply-To: <F18A6F7CF1661F46920F2CF713122FED46CBAF@mail.aveo.aveopharma.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox