Problems with index-scan on regexp in 8.1

From: Lars Kanis <kanis(at)comcard(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Problems with index-scan on regexp in 8.1
Date: 2005-11-07 06:50:20
Message-ID: 200511070750.21070.kanis@comcard.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jeroen T. Vermeulen 2005-11-07 08:14:41 Re: Old interfaces directory in CVS tree?
Previous Message Gavin Sherry 2005-11-07 06:23:28 Re: PG 8.1 supported platforms list: IRIX is MIA