From: | Antti Salmela <asalmela(at)iki(dot)fi> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Index used incorrectly with regular expressions on 7.4.6 |
Date: | 2004-12-01 17:24:15 |
Message-ID: | vab182-r7r.ln1@asalmela.iki.fi |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Index is used incorrectly if constant part of the string ends with \d,
probably also with other escapes.
foo=# explain select count(*) from loc where url ~
'^http://www\\.7-eleven\\.com/newsroom/articles\\.asp\\?p=\\d+'; QUERY PLAN
Aggregate (cost=3.46..3.46 rows=1 width=0)
-> Index Scan using url_index on loc (cost=0.00..3.46 rows=1 width=0)
Index Cond: ((url >=
'http://www.7-eleven.com/newsroom/articles.asp?p=d'::text) AND
(url < 'http://www.7-eleven.com/newsroom/articles.asp?p=e'::text))
Filter: (url ~
'^http://www\\.7-eleven\\.com/newsroom/articles\\.asp\\?p=\\d+'::text)
(4 rows)
foo=# select count(*) from loc where url ~
'^http://www\\.7-eleven\\.com/newsroom/articles\\.asp\\?p=\\d+';
count
-------
0
(1 row)
foo=# set enable_indexscan = off;
SET
foo=# explain select count(*) from loc where url ~
'^http://www\\.7-eleven\\.com/newsroom/articles\\.asp\\?p=\\d+';
QUERY PLAN
Aggregate (cost=3056.41..3056.41 rows=1 width=0)
-> Seq Scan on loc (cost=0.00..3056.40 rows=1 width=0)
Filter: (url ~
'^http://www\\.7-eleven\\.com/newsroom/articles\\.asp\\?p=\\d+'::text)
(3 rows)
foo=# select count(*) from loc where url ~
'^http://www\\.7-eleven\\.com/newsroom/articles\\.asp\\?p=\\d+';
count
-------
281
(1 row)
--
Antti Salmela
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Treat | 2004-12-01 18:12:06 | Re: Increasing the length of |
Previous Message | Bruce Momjian | 2004-12-01 16:47:12 | Re: Increasing the length of |