pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect using regexp does not use indexscan
4+ messages / 3 participants
[nested] [flat]
* select using regexp does not use indexscan
@ 2004-11-09 20:02 carex <carex@skynet.be>
2004-11-13 21:39 ` Re: select using regexp does not use indexscan Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: carex@skynet.be @ 2004-11-09 20:02 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: select using regexp does not use indexscan
2004-11-09 20:02 select using regexp does not use indexscan carex <carex@skynet.be>
@ 2004-11-13 21:39 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-14 00:02 ` Re: select using regexp does not use indexscan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2004-11-13 21:39 UTC (permalink / raw)
To: carex <carex@skynet.be>; +Cc: pgsql-sql
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 (<typename>_pattern_ops I believe).
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: select using regexp does not use indexscan
2004-11-09 20:02 select using regexp does not use indexscan carex <carex@skynet.be>
2004-11-13 21:39 ` Re: select using regexp does not use indexscan Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-11-14 00:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-15 17:12 ` Re: select using regexp does not use indexscan carex <carex@skynet.be>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2004-11-14 00:02 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: carex <carex@skynet.be>; pgsql-sql
Stephan Szabo <sszabo@megazone.bigpanda.com> 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 (<typename>_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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: select using regexp does not use indexscan
2004-11-09 20:02 select using regexp does not use indexscan carex <carex@skynet.be>
2004-11-13 21:39 ` Re: select using regexp does not use indexscan Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-14 00:02 ` Re: select using regexp does not use indexscan Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-11-15 17:12 ` carex <carex@skynet.be>
0 siblings, 0 replies; 4+ messages in thread
From: carex@skynet.be @ 2004-11-15 17:12 UTC (permalink / raw)
To: pgsql-sql
tgl@sss.pgh.pa.us (Tom Lane) wrote in message news:<3972.1100390524@sss.pgh.pa.us>...
> Stephan Szabo <sszabo@megazone.bigpanda.com> 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 (<typename>_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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-11-15 17:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-09 20:02 select using regexp does not use indexscan carex <carex@skynet.be>
2004-11-13 21:39 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-11-14 00:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-15 17:12 ` carex <carex@skynet.be>
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