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
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 |