Is there any possibility to make PostgreSQL use indexes when working
with regular expressions?
performance=# EXPLAIN SELECT * FROM perftest WHERE id=100;
NOTICE: QUERY PLAN:
Index Scan using idx_id_perftest on perftest (cost=0.00..4.98 rows=1
width=20)
EXPLAIN
performance=# EXPLAIN SELECT * FROM perftest WHERE id ~ '^100$';
NOTICE: QUERY PLAN:
Seq Scan on perftest (cost=100000000.00..100218966.00 rows=100000
width=20)
EXPLAIN
It is clear that complex regular expressions can possibly never use an
index but is it possible to use it when looking for the beginning of a
string (e.g.: ^100).
Hans