Re: Regular expressions and indexes

On Fri, 27 Apr 2001, [iso-8859-1] Hans-Jrgen Schnig 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.