[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [GENERAL] Hierarchy indicies
From:       Martijn van Oosterhout <kleptog () cupid ! suninternet ! com>
Date:       2000-04-28 14:14:02
[Download RAW message or body]

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/

[prev in list] [next in list] [prev in thread] [next in thread]