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 <[EMAIL PROTECTED]> 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 | | ------------------------+---------------------------------------------+ ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate