Can a database index span tables? I'm on Postgres

Question

Is it possible to create an index that has as one of its columns values from another table?

Example:

model Pet
  primary_key id
  foreign_key Species
end
model Species
  primary_key id
  int genus
end

Assume there's a lot of kinds of species, with fewer types of genuses. I'd like to create an index on the Pets table by Genus. Can it be done?

If so, I'd be extra grateful if you could point me in the right direction on how to do it in Rails migration.

I don't know postgres but I do not believe that is possible. You can definitely make an index by using the Species FK but that won't help you as really the genus column should be a FK to a genus table. — Ben Hoffman
– Ben Hoffman, Commented Mar 5, 2010 at 22:56

James Kingsbery · Accepted Answer · 2010-03-05 23:02:43Z

2

No. In any relational database technology, "index" means an index of the table. You could instead combine the two like this:

ANIMAL_CLASS
 + name
 + id
 + LEVEL
 + parent_id

Where level is logically an enumeration of {SPECIES, GENUS, .... }

answered Mar 5, 2010 at 23:02

James Kingsbery

7,5262 gold badges40 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adam Musch Over a year ago

Not exactly. Oracle supports bitmap join indexes -- where you are indexing an attribute of a parent table on the child table. It's designed to eliminate IO against star/snowflake schemas in star transformation queries. So Oracle could, in this case, index Species.Genus against the Pet table, as each Pet has at most one Genus.

Collectives™ on Stack Overflow

Can a database index span tables? I'm on Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related