Обсуждение: 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