[SQL] 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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] select using regexp does not use indexscan
[EMAIL PROTECTED] (Tom Lane) wrote in message news:<[EMAIL PROTECTED]>... > Stephan Szabo <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
