function indexes, index only scan and sorting

Поиск
Список
Период
Сортировка
Искать
От
Jonathan Vanasco
Тема
function indexes, index only scan and sorting
Дата
Msg-id
364A5B38-09E1-49D1-9CFB-DB5751A08789@2xlp.com
Список
Дерево обсуждения
function indexes, index only scan and sorting Jonathan Vanasco <postgres@2xlp.com>
Re: function indexes, index only scan and sorting Tom Lane <tgl@sss.pgh.pa.us>
Re: function indexes, index only scan and sorting Jonathan Vanasco <postgres@2xlp.com>
Re: function indexes, index only scan and sorting Tom Lane <tgl@sss.pgh.pa.us>

Can someone confirm a suspicion for me ?

I have a moderately sized table (20+ columns, 3MM rows) that tracks "tags".

I have a lower(column) function index that is used simplify case-insensitive lookups.

	CREATE INDEX idx_tag_name_lower ON tag(lower(name));

I have a few complex queries that need to join back to this table (via the `id` primary key) and sort on `lower(name)`.

I'm not selecting `lower(name)`, just using it for an order-by.

The only way I seem to be able to avoid a Sequential Scan and run an index-only scan is with another index -- this one specifically (and I've run queries against 8 index permutations):

	CREATE INDEX idx_tag_joins ON tag(id, name_display); 

Am I correct in observing that the value of a function index can't be used for sorting ?
В списке pgsql-general по дате отправления
От: Daniel Begin
Дата:
От: Jonathan Vanasco
Дата:
FAQ