Problems with index-scan on regexp in 8.1

From: Lars Kanis <kanis(at)comcard(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problems with index-scan on regexp in 8.1
Date: 2005-11-07 10:00:29
Message-ID: 200511071100.29726.kanis@comcard.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Sorry, I accidentally posted this at pgsql-hackers already. Again:

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

Browse pgsql-bugs by date

  From Date Subject
Next Message Joel Stevenson 2005-11-07 14:40:59 BUG #2023: Assertion Failure: File: "slru.c", Line: 309
Previous Message li fei 2005-11-07 07:40:07 BUG #2022: template0 Fefresh error