pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Regular expressions and indexes
2+ messages / 2 participants
[nested] [flat]

* Regular expressions and indexes
@ 2001-04-27 09:04  Hans-Jürgen Schönig <hs@cybertec.at>
  0 siblings, 1 reply; 2+ messages in thread

From: Hans-Jürgen Schönig @ 2001-04-27 09:04 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Regular expressions and indexes
@ 2001-04-27 16:50  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Hans-Jürgen Schönig <hs@cybertec.at>
  0 siblings, 0 replies; 2+ messages in thread

From: Stephan Szabo @ 2001-04-27 16:50 UTC (permalink / raw)
  To: Hans-Jürgen Schönig <hs@cybertec.at>; +Cc: pgsql-sql

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.






^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-04-27 16:50 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-27 09:04 Regular expressions and indexes Hans-Jürgen Schönig <hs@cybertec.at>
2001-04-27 16:50 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox