Re: PG won't use index on ORDER BY
От | Andreas Joseph Krogh |
---|---|
Тема | Re: PG won't use index on ORDER BY |
Дата | |
Msg-id | 200708092310.53309.andreak@officenet.no обсуждение исходный текст |
Ответ на |
Re: PG won't use index on ORDER BY |
Ответы |
Re: PG won't use index on ORDER BY |
Список | pgsql-sql |
On Thursday 09 August 2007 22:57:35 Rodrigo De León wrote: > On 8/9/07, Andreas Joseph Krogh <andreak@officenet.no> 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 <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 по дате отправления: