Thread: match_special_index_operator don't work in 7.2.1
Your name : Andriy I Pilipenko Your email address : bamby@marka.net.ua System Configuration --------------------- Architecture : Intel Pentium Operating System : FreeBSD 4.5-STABLE PostgreSQL version : PostgreSQL-7.2.1 Compiler used : gcc 2.95.3 Please enter a FULL description of your problem: ------------------------------------------------ I've upgraded from 7.0.3 to 7.2.1 and noticed that queries like SELECT ... WHERE field LIKE 'something%' take too much time to execute now. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: ---------------------------------------------------------------------- bamby=# create table t (f varchar(100)); CREATE bamby=# create index i_t__f on t(f); CREATE bamby=# SET enable_seqscan TO 'off'; SET VARIABLE bamby=# EXPLAIN SELECT * from t where f = 'aa'; NOTICE: QUERY PLAN: Index Scan using i_t__f on t (cost=0.00..17.07 rows=5 width=68) EXPLAIN bamby=# EXPLAIN SELECT * from t where f LIKE 'aa%'; NOTICE: QUERY PLAN: Seq Scan on t (cost=100000000.00..100000022.50 rows=5 width=68) EXPLAIN If you know how this problem might be fixed, list the solution below: ---------------------------------------------------------------------
On Sun, 19 May 2002, Andriy I Pilipenko wrote: > Please enter a FULL description of your problem: > ------------------------------------------------ > > I've upgraded from 7.0.3 to 7.2.1 and noticed that queries like > > SELECT ... WHERE field LIKE 'something%' > > take too much time to execute now. Are you sure that you're running in C locale? If you're unsure, you can build pg_controldata in contrib and check. (From comments in recent sources: /* * We want test whether the database's LC_COLLATE setting is safe for * LIKE/regexp index optimization. * * The key requirement here is that given a prefix string, say "foo", * we must be able to generate another string "fop" that is greater * than all strings "foobar" starting with "foo". Unfortunately, a * non-C locale may have arbitrary collation rules in which "fop" > * "foo" is not sufficient to ensure "fop" > "foobar". Until we can * come up with a more bulletproof way of generating the upper-bound * string, the optimization is disabled in all non-C locales. * * (In theory, locales other than C may be LIKE-safe so this function * could be different from lc_collate_is_c(), but in a different * theory, non-C locales are completely unpredicable so it's unlikely * to happen.) */ )
On Sun, 19 May 2002, Stephan Szabo wrote: > > On Sun, 19 May 2002, Andriy I Pilipenko wrote: > > > Please enter a FULL description of your problem: > > ------------------------------------------------ > > > > I've upgraded from 7.0.3 to 7.2.1 and noticed that queries like > > > > SELECT ... WHERE field LIKE 'something%' > > > > take too much time to execute now. > > Are you sure that you're running in C locale? If you're unsure, > you can build pg_controldata in contrib and check. Thanks for the point - I missed that. I'm running KOI8 locale now and used C locale in old database. > (From comments in recent sources: > /* > * We want test whether the database's LC_COLLATE setting is safe for > * LIKE/regexp index optimization. > * > * The key requirement here is that given a prefix string, say "foo", > * we must be able to generate another string "fop" that is greater > * than all strings "foobar" starting with "foo". Unfortunately, a > * non-C locale may have arbitrary collation rules in which "fop" > > * "foo" is not sufficient to ensure "fop" > "foobar". Until we can > * come up with a more bulletproof way of generating the upper-bound > * string, the optimization is disabled in all non-C locales. > * > * (In theory, locales other than C may be LIKE-safe so this function > * could be different from lc_collate_is_c(), but in a different > * theory, non-C locales are completely unpredicable so it's unlikely > * to happen.) > */ > ) Kind regards, Andriy I Pilipenko PAI1-RIPE
Andriy I Pilipenko <bamby@marka.net.ua> writes: > I've upgraded from 7.0.3 to 7.2.1 and noticed that queries like > SELECT ... WHERE field LIKE 'something%' > take too much time to execute now. And your database locale is? regards, tom lane