index not used in like clause...

Please reply directly, cc to list as approp.  I am not on the list.

I have a query which joins a table to itself.  The reason is this: a field
in the table, called hier_key, contains a string which represents an items
location in a tree structure, values are like this:

01  /* top node */
0001  /* main branch */
000101  /* sub-branch */
000102  /* a sibling sub-branch */
0002  /* another main branch */
.
.
.

I want to find all items 'below' a given branch, and all items have a
unique id, called item_id.  So to get the branches for item_id 999999:

select
     b.hier_key. b.descr
from
     items a, items b
where
     a.item_id = 999999 and
     b.hier_key like a.hier_key||'%'
;

This works fine, but the 'explain' is wierd.  The index on item_id (did I
mention there are two indices: on hier_key and item_id ) is used on the
'a' table but the hier_key index is not used for the 'b' table, instead it
is seq. scan. Now, if I change the 'like' to '=' (removing the percent of
course) it DOES use the index for the join, but this doesn't get the
desired results of course :-(.  I have also tried combinations using the
'~' operator, to no avail.  The explain always shows a seq. scan on the
'b' table.  I know that indexes can be used for like clauses, because if I
break this into two queries, it uses the index, e.g.

select hier_key from items where item_id = 999999;
(returns)
010514      /* this means fifth main branch, fourteenth sub-branch */

Then:
explain select * from items where hier_key like '010514%'
(returns)
Index Scan on items  (cost=6.34 size=1 width=216)

Any ideas???


--
/==============================\
| David Mansfield              |
| david@cobite.com             |
\==============================/