Thread

Hierarchy indicies

Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2000-04-28T14:14:02Z

I have a single base table from which I inherit various
other tables but, especially when doing joins, this is
not very efficient. For example whenever you do a query like:

select * from table* where fieldid = 2345;

the plan ends up doing scans across all the tables that
inherit "table". What I want is to be able to create an
index over the whole hierarchy, like:

create index table_index on table*(fieldname)

and then have the index used when appropriate. There are
some situations where I have to do a join between two
hierarchies. Currently it ends up with many, many 
nested loops and sequential scans, whereas with an index
like above would reduce it to a single Hash Join (or
equivalent).

From looking at the source you can't have a single index
reference multiple tables...

Any ideas?
-- 
Martijn van Oosterhout <kleptog@cupid.suninternet.com>
http://cupid.suninternet.com/~kleptog/