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
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 |