On Thursday 09 August 2007 22:57:35 Rodrigo De León wrote: > On 8/9/07, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > > Ooops, just fugured that out. But - it still doesn't use the index if I > > remove the "varchar_pattern_ops". > > Huh? > > CREATE INDEX person_lowerfullname_idx > ON person > ((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, '')))); > > > EXPLAIN ANALYZE select id from person order by > (lower(COALESCE(firstname, '')) || > lower(COALESCE(lastname, ''))) ASC limit 1; > > Limit (cost=0.00..0.08 rows=1 width=68) (actual time=0.030..0.030 > rows=0 loops=1) > -> Index Scan using person_lowerfullname_idx on person > (cost=0.00..62.25 rows=800 width=68) (actual time=0.018..0.018 rows=0 > loops=1) > Total runtime: 0.318 ms > > > EXPLAIN ANALYZE select id from person > where (lower(COALESCE(firstname, '')) || > lower(COALESCE(lastname, ''))) like 'A%' > order by (lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))) > ASC limit 1; > > Limit (cost=0.01..5.10 rows=1 width=68) (actual time=0.038..0.038 > rows=0 loops=1) > -> Index Scan using person_lowerfullname_idx on person > (cost=0.01..20.34 rows=4 width=68) (actual time=0.032..0.032 rows=0 > loops=1) > Index Cond: (((lower((COALESCE(firstname, ''::character > varying))::text) || lower((COALESCE(lastname, ''::character > varying))::text)) >= 'A'::text) AND ((lower((COALESCE(firstname, > ''::character varying))::text) || lower((COALESCE(lastname, > ''::character varying))::text)) < 'B'::text)) > Filter: ((lower((COALESCE(firstname, ''::character > varying))::text) || lower((COALESCE(lastname, ''::character > varying))::text)) ~~ 'A%'::text) > Total runtime: 0.138 ms > > > Works for me.
I forgot to mention: I use UTF-8. IIRC, it works fine with C-locale without the "varchar_pattern_ops", which is why it works for you I think. -- 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 5: don't forget to increase your free space map settings
