The following bug has been logged online:

Bug reference: 1394
Logged by: Steve Nicolai
Email address: steven@netopia.com
PostgreSQL version: 7.4.6
Operating system: Fedora Core 2
Description: LIKE doesn't seem to use an index
Details:

I've inherited some software that generates random 27 character ids for the
objects and then uses the 28 character to indicate related objects.

The software uses the like operator to select these related objects. That
gets slower as the number of objects increase, even though there is an index
on id.

By adding some additional restrictions (that can be automatically generated
by postgresql) I was able to get postgresql to use the index, speeding the
query up.

npm=> \d objects
Table "public.objects"
Column | Type | Modifiers
--------------+-----------------------------+-----------
id | character varying(28) |
name | character varying(50) |
altname | character varying(50) |
type | character varying(3) |
domainid | character varying(28) |
status | smallint |
dbver | integer |
created | timestamp without time zone |
lastmodified | timestamp without time zone |
assignedto | character varying(28) |

Indexes:
"ix_objects_id" btree (id)

npm=> explain select * from objects where id like
'W7iM5uvo23pHqzckPWbuRPTSxDk_';
QUERY PLAN
----------------------------------------------------------------
Seq Scan on objects (cost=0.00..1681.59 rows=1 width=178)
Filter: ((id)::text ~~ 'W7iM5uvo23pHqzckPWbuRPTSxDk_'::text)
(2 rows)

npm=> explain select * from objects where id like
'W7iM5uvo23pHqzckPWbuRPTSxDk_' and id>'W7iM5uvo23pHqzckPWbuRPTSxDk' and
id<'W7iM5uvo23pHqzckPWbuRPTSxDl';
QUERY PLAN

----------------------------------------------------------------------------
-----------------------------------------------
Index Scan using ix_objects_id on objects (cost=0.00..6.02 rows=1
width=178)
Index Cond: (((id)::text > 'W7iM5uvo23pHqzckPWbuRPTSxDk'::text) AND
((id)::text < 'W7iM5uvo23pHqzckPWbuRPTSxDl'::text))
Filter: ((id)::text ~~ 'W7iM5uvo23pHqzckPWbuRPTSxDk_'::text)
(3 rows)

if there is an index and there is enough data before the first wildcard in
the string to give reasonable discrimination on that index, postgresql
should use an index scan.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Steve Nicolai wrote:
> The software uses the like operator to select these related objects.
> That gets slower as the number of objects increase, even though there
> is an index on id.

See this page for how to create correct indexes for pattern matching
operators:

http://www.postgresql.org/docs/7.4/s...s-opclass.html

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match