pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRegular 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