============================================================================
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:
---------
--------------------------------------------------------------------------