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. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster