Indices and user defined operators

Поиск
Список
Период
Сортировка
От Dmitri Bichko
Тема Indices and user defined operators
Дата
Msg-id F18A6F7CF1661F46920F2CF713122FED46CBAF@mail.aveo.aveopharma.com
обсуждение исходный текст
Ответы Re: Indices and user defined operators
Список 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



В списке pgsql-sql по дате отправления:

Предыдущее
От: Alain
Дата:
Сообщение: Re: SELECT very slow
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Indices and user defined operators