Port Bug Report: SELECT with LIKE on indexed field do not use index - Mailing list pgsql-ports

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               : alexander N Shulyak
Your email address      : alec@nikts.nk.ukrtel.net

Category                : unknown
Severity                : critical

Summary: SELECT with LIKE on indexed field do not use index

System Configuration
--------------------
  Operating System   : FreeBSD -3.1-RELEASE and linux RH-6.0

  PostgreSQL version : 6.5 RELEASE

  Compiler used      : gcc 2.7.2.1

Hardware:
---------
Celeron 300,64MB RAM,4.3GB HDD

Versions of other tools:
------------------------
gmake 3.77
flex 2.5.4


--------------------------------------------------------------------------

Problem Description:
--------------------
I have table with 166887 rows. Here is its structure:

sprav=> \d spr
Table    = spr
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| name                             | varchar()                        |     0 |
| strhousnum                       | int4                             |     4 |
| housind                          | char()                           |     1 |
| korpus                           | varchar()                        |     0 |
| flat                             | varchar()                        |     0 |
| phone                            | varchar()                        |     0 |
| id                               | int4                             |     4 |
| flag                             | varchar()                        |     0 |
| subname                          | varchar()                        |     0 |
| sstrhousnum                      | int4                             |     4 |
| shousind                         | char()                           |     1 |
| skorpus                          | varchar()                        |     0 |
| sflat                            | varchar()                        |     0 |
| subnote                          | varchar()                        |     0 |
| mod                              | int2                             |     2 |
+----------------------------------+----------------------------------+-------+
Index:    spr_phone
When I do request -
sprav=> explain select * from spr where phone like '3554__';
the result is fine -
NOTICE:  QUERY PLAN:

Index Scan using spr_phone on spr  (cost=3785.45 rows=1 width=146)

EXPLAIN
but when execute the result returns in about 10 secondes!!!
When the request is -
sprav=> sselect * from spr where phone between '355400' and '355499';
the result returns in less than 1 second.

SORRY!!! I can't use such improved end enhansed DBMS because
same of my applications use SELECT with LIKE in dialog.

--------------------------------------------------------------------------

Test Case:
----------


--------------------------------------------------------------------------

Solution:
---------


--------------------------------------------------------------------------