From: carex@skynet.be
To: pgsql-sql@postgresql.org
Subject: select using regexp does not use indexscan
Date: 9 Nov 2004 12:02:26 -0800
Message-ID: <3a0a211c.0411091202.223f1c02@posting.google.com> (raw)
This is what I get with postgres-7.3 (from Redhat Enterprise !!)
Here below a select with a regexp
ansroc=# explain select * from s12hwdb where host~'^tna2582t';
QUERY PLAN
-------------------------------------------------------------
Seq Scan on s12hwdb (cost=0.00..30660.35 rows=1 width=128)
Filter: (host ~ '^tna2582t'::text)
(2 rows)
Here below the same select without regexp
ansroc=# explain select * from s12hwdb where host='tna2582t';
QUERY PLAN
---------------------------------------------------------------------------------------------
Index Scan using s12hwdb_host_rit_idx on s12hwdb
(cost=0.00..18123.85 rows=4828 width=128)
Index Cond: (host = 'tna2582t'::bpchar)
(2 rows)
ansroc=#
As you can see, the index is not use when a regexp is used in the
select.
I did the same test with postgres-7.3.6 & postgres-7.4.6 (compiled
from sources) but the results where the same.
(index is NEVER used with regexp on a RHE)
I even tried with a 'set enable_seqscan to off', but the result is the
same.
BUT, with Debian (woody & sarge) everything is ok.
(has always been with debian-:)
I did try with a postgres debian pachage, and also with a postgres
compiled from source, and even with different version (7.3.4, 7.4.6).
Index is always used !
ansroc=# explain SELECT * FROM s12hwdb where host~'^tna2582t';
QUERY PLAN
--------------------------------------------------------------------------------------
Index Scan using s12hwdb_host_rit_idx on s12hwdb (cost=0.00..4.41
rows=1 width=128)
Index Cond: ((host >= 'tna2582t'::bpchar) AND (host <
'tna2582u'::bpchar))
Filter: (host ~ '^tna2582t'::text)
(3 rows)
ansroc=# explain SELECT * FROM s12hwdb where host='tna2582t';
QUERY PLAN
----------------------------------------------------------------------------------------
Index Scan using s12hwdb_host_rit_idx on s12hwdb (cost=0.00..76.02
rows=17 width=128)
Index Cond: (host = 'tna2582t'::bpchar)
(2 rows)
ansroc=#
And it works also perfectly with Gentoo.
So,is this a typical "Redhat Enterprise" problem ?
Or do I overlook something ??
Has someone experienced the same problem ??
Thanks.
carex.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: carex@skynet.be
Subject: Re: select using regexp does not use indexscan
In-Reply-To: <3a0a211c.0411091202.223f1c02@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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