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 |
\==============================/