[HACKERS] Problems with index-scan on regexp in 8.1

[Lars Kanis]

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match