Hmmm, yeah. But that's only for the INNER JOIN case. But I've seen many of these star join queries with LEFT JOIN too, and then the FKs are not needed. All you need is a PK / unique index on the other side.
Indeed, many installations specifically _remove_ foreign keys because of the dreaded RI check on delete. Basically, if you delete one or more rows in dim1 then the referencing fact1 must be scanned to ensure that it does not contain a reference to the deleted row. Often the referencing field on fact1 is not indexed, because the index is almost never useful in an actual select query, so even if you did index it several unused index metrics will identify it as a candidate for deletion. What you get is one sequential scan of fact1 for every row deleted from dim1. Now, we could get around this by changing how we do delete RI checks, either by moving to statement level triggers or bypassing triggers entirely, but until we do so, it is likely that many customers avoid otherwise useful FK references.
