Thread: 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_idxon 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_idxon 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_idxon 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.
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).
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
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.