Re: Regular expressions and indexes

On Fri, 27 Apr 2001, [iso-8859-1] Hans-J�rgen Sch�nig wrote:

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

Couple of questions.  Have you run vacuum analyze (100000 is a large
number of rows for the estimate, although I believe the estimate is 
wierd for the regexp case)?  Also, what type is id?  You seem to be
treating it like an int in the first case and as a string in the
second. If it's an integer, then a regexp comparison is unlikely
to use the index, since an integer index isn't indexed in a way
that'll help in general (okay, technically the above is a single
value, but that's not a usual case)

On my 7.1 system, filling a table with 100000 varchars and vacuum
analyzing the table, bot queries give index scans.