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
