From: | David Mansfield <david(at)cobite(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | index not used in like clause... |
Date: | 1998-10-19 18:01:10 |
Message-ID: | Pine.LNX.4.00.9810191344320.25222-100000@ariel.cobite.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)cobite(dot)com |
\==============================/
From | Date | Subject | |
---|---|---|---|
Next Message | Bryan White | 1998-10-19 19:31:29 | Defaulting to current date |
Previous Message | Tony.Curtis | 1998-10-19 14:58:58 | Re: [SQL] [SQL, regex, words] how to match word boundaries using regex's? |