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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
