Skip site navigation (1) Skip section navigation (2)

Re: Regular expressions and indexes

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Hans-Jürgen Schönig <hs(at)cybertec(dot)at>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Regular expressions and indexes
Date: 2001-04-27 16:50:26
Message-ID: Pine.BSF.4.21.0104270926180.26398-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-sql
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.




In response to

pgsql-sql by date

Next:From: Scott David WalterDate: 2001-04-27 17:15:35
Subject: Alter Table problems
Previous:From: clayton cottinghamDate: 2001-04-27 16:18:51
Subject: anyone use contrib/rserv?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group