PG won't use index on ORDER BY
| От | Andreas Joseph Krogh |
|---|---|
| Тема | PG won't use index on ORDER BY |
| Дата | |
| Msg-id | 200708092018.44111.andreak@officenet.no обсуждение |
| Ответы |
Re: PG won't use index on ORDER BY |
| Список | pgsql-sql |
Hi all! I have problems getting PG to use an index when sorting. I have a simple table create table person( id serial primary key, firstname varchar, lastname varchar ); I create an index: CREATE INDEX person_lowerfullname_idx ON person((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))) varchar_pattern_ops); And this query refuses to use that index: select id from person order by (lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))) ASC limit 1; If I add an index: CREATE INDEX person_lowerfirstname_idx ON person(lower(firstname)); The following query will use that index for sorting and cut-off: select id from person order by (lower(firstname) ) ASC limit 1; Any hints or explaination on why the "concat-index" won't be used? PS: I have tried to issue a "set enable_seqscan to off;" to ensure that it will use an index if one appropriate exists -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Tlf: +47 24 15 38 90 | | Fax: +47 24 15 38 91 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
В списке pgsql-sql по дате отправления: