Problems with index-scan on regexp in 8.1
От | Lars Kanis |
---|---|
Тема | Problems with index-scan on regexp in 8.1 |
Дата | |
Msg-id | 200511070750.21070.kanis@comcard.de обсуждение исходный текст |
Ответы |
Re: Problems with index-scan on regexp in 8.1
|
Список | pgsql-hackers |
We're using Postgres 8.0.2 on SuSE10.0 (64-Bit). Tests on 8.1 beta 4 have shown no problems but this one: SELECT * FROM mitglieder WHERE lower(vorname::text)='lars' does a bitmap-index-scan like this: Bitmap Heap Scan on mitglieder (cost=10.68..3770.52 rows=1051 width=226) Recheck Cond: (lower((vorname)::text) = 'lars'::text) -> Bitmap Index Scan on mitgl_lower_namen_idx (cost=0.00..10.68 rows=1051 width=0) Index Cond: (lower((vorname)::text) = 'lars'::text) but a regular expression always results in a seqscan: SELECT * FROM mitglieder WHERE lower(vorname::text)~'^lars' Seq Scan on mitglieder (cost=0.00..79703.73 rows=1 width=226) Filter: (lower((vorname)::text) ~ '^lars'::text) whereas V8.0.2 does a proper index-scan: Index Scan using mitgl_lower_namen_idx on mitglieder (cost=0.01..18.05 rows=4 width=225) Index Cond: ((lower((vorname)::text) >= 'lars'::text) AND (lower((vorname)::text) < 'lart'::text)) Filter: (lower((vorname)::text) ~ '^lars'::text) The use of indexes for regexp is quite important for the search in our interactive frontend. kind regards Lars Kanis
В списке pgsql-hackers по дате отправления: