4 ms·

Postgresql advice from a mysql user. Foreign keys don't need indexes on the from side, and they should be pointing to a primary key on the to side so that is a

by asdasf 13y ago

Postgresql advice from a mysql user. Foreign keys don't need indexes on the from side, and they should be pointing to a primary key on the to side so that is already covered. In mysql you likely want an index on the from side because you are probably joining on it and mysql only knows how to do nested loop joins. But postgresql is a real database, so we have hash joins and merge joins, which don't need indexes on the join conditions, rather on the where clause (like you would want anyways). http://use-the-index-luke.com/sql/join/hash-join-partial-objects http://use-the-index-luke.com/sql/join/hash-join-partial-obj...

mfenniak 13y ago
> Foreign keys don't need indexes on the from side Yes, they're not a hard requirement. However, be aware that any field with a foreign key constraint will be searched during a DELETE that affects the related table, to check RESTRICT DELETE or CASCADE DELETE constraints. It can be very beneficial to performance to have an index on that field for those internal database searches. The same is true for an UPDATE that affects the PK, but that's relatively rare if you're using surrogate primary keys.
dspillett 13y ago
This is why FKs don't automatically get indexed in many DBMSs as some people expect (I find peopel assume FKs imply indexes due to the fact that PKs and unique constrains tend to). Of course if any code searches for rows matching a given key witohut joining to the target table of the FK and without other relavent filtering clauses, then an index definitely is useful, and in some circumstances the query planmner will determine (given the choice) that the hash join is not the most efficient way to go for a given query anyway, so it is sometimes worth indexing your FKs. Testing/benchmarking (with real or real-a-like data of realistic scale) is your friend here when you are not sure. Unless disk space and write performance are a significant issue (which for many applications they may not be) I tend to err on the side of indexing FKs.
philwelch 13y ago
In the case that you use your database for more than just a persistence backend to your Rails app (blasphemy, I know, but odds are your Rails app is generating transactional data that's worthy of analysis you need real SQL for and not just ActiveRecord), indexing foreign keys is still worthwhile because sometimes you'll join the same foreign key in two different tables without ever needing to join in the table they normally map to.
- asdasf 13y ago
  What you describe doesn't need an index on the foreign key, that's the point I was making.
  - philwelch 13y ago
    If you designate it as a foreign key in the schema it'll just use the PK index even if you don't join that table in at all? Wait, how does that even work? Does the PK index include the foreign key rows then? That's an awesome feature.

by asdasf 13y ago

mfenniak 13y ago

> Foreign keys don't need indexes on the from side Yes, they're not a hard requirement. However, be aware that any field with a foreign key constraint will be searched during a DELETE that affects the related table, to check RESTRICT DELETE or CASCADE DELETE constraints. It can be very beneficial to performance to have an index on that field for those internal database searches. The same is true for an UPDATE that affects the PK, but that's relatively rare if you're using surrogate primary keys.

dspillett 13y ago

This is why FKs don't automatically get indexed in many DBMSs as some people expect (I find peopel assume FKs imply indexes due to the fact that PKs and unique constrains tend to). Of course if any code searches for rows matching a given key witohut joining to the target table of the FK and without other relavent filtering clauses, then an index definitely is useful, and in some circumstances the query planmner will determine (given the choice) that the hash join is not the most efficient way to go for a given query anyway, so it is sometimes worth indexing your FKs. Testing/benchmarking (with real or real-a-like data of realistic scale) is your friend here when you are not sure. Unless disk space and write performance are a significant issue (which for many applications they may not be) I tend to err on the side of indexing FKs.

philwelch 13y ago

In the case that you use your database for more than just a persistence backend to your Rails app (blasphemy, I know, but odds are your Rails app is generating transactional data that's worthy of analysis you need real SQL for and not just ActiveRecord), indexing foreign keys is still worthwhile because sometimes you'll join the same foreign key in two different tables without ever needing to join in the table they normally map to.

asdasf 13y ago
What you describe doesn't need an index on the foreign key, that's the point I was making.
- philwelch 13y ago
  If you designate it as a foreign key in the schema it'll just use the PK index even if you don't join that table in at all? Wait, how does that even work? Does the PK index include the foreign key rows then? That's an awesome feature.