select using regexp does not use indexscan

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.

Re: select using regexp does not use indexscan

On Tue, 9 Nov 2004, carex wrote:

> And it works also perfectly with Gentoo.
>
> So,is this a typical "Redhat Enterprise" problem ?
> Or do I overlook something ??

IIRC, in 7.3.x, index scans are only considered in "C" locale for
regexp/LIKE. In 7.4.x, non-"C" locale databases can use a special
index of a different opclass (_pattern_ops I believe).


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: select using regexp does not use indexscan

Stephan Szabo writes:
> On Tue, 9 Nov 2004, carex wrote:
>> And it works also perfectly with Gentoo.
>> So,is this a typical "Redhat Enterprise" problem ?
>> Or do I overlook something ??

> IIRC, in 7.3.x, index scans are only considered in "C" locale for
> regexp/LIKE. In 7.4.x, non-"C" locale databases can use a special
> index of a different opclass (_pattern_ops I believe).

Not sure if this answer was explicit enough, so: evidently the database
was initdb'd in "C" locale on Gentoo, but in some other locale on Red Hat.
The only "typical Red Hat problem" is that they are more enthusiastic
about setting up non-C default locales than some other distros.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: select using regexp does not use indexscan

tgl@sss.pgh.pa.us (Tom Lane) wrote in message news:<3972.1100390524@sss.pgh.pa.us>...
> Stephan Szabo writes:
> > On Tue, 9 Nov 2004, carex wrote:
> >> And it works also perfectly with Gentoo.
> >> So,is this a typical "Redhat Enterprise" problem ?
> >> Or do I overlook something ??
>
> > IIRC, in 7.3.x, index scans are only considered in "C" locale for
> > regexp/LIKE. In 7.4.x, non-"C" locale databases can use a special
> > index of a different opclass (_pattern_ops I believe).
>
> Not sure if this answer was explicit enough, so: evidently the database
> was initdb'd in "C" locale on Gentoo, but in some other locale on Red Hat.
> The only "typical Red Hat problem" is that they are more enthusiastic
> about setting up non-C default locales than some other distros.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------

Thank you so much.
It is indeed clearer now.

So I did an initdb --locale=C -D /path/to/data
rebuild my database and started my "select" again.
I could see my index was used even when host~'^tna2';

Thanks again.