Stack Exchange network consists of 183 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
@Raj "ORDER BY requires all rows to be FETCHED before they can be ordered." That's not 100% true -- an index might be used to both select the rows required and to determine the required order.
Do you need to optimise this query in order to get the first rows back from it as soon as possible, or to get all rows back as soon as possible? If the former then you might benefit from an index-based order by at the expense of a longer total query time. If the latter then a full scan and sort is likely to be fastest, but it will take a while for the first rows to be returned.
"simple look-up" by what value(s)? "find mean" within each row, or across rows for particular columns? PostgreSQL will not manage that many columns anyway ... postgresql.org/about Also do you have to just load this data once, or does it also require incremental or complete updates?
I'm not convinced that it's the execution of such queries that is the primary problem that people have with such a design -- it's the writing of the query that they often object to.
And yet many systems do have synthetic integer primary keys on every table (almost every Ruby on Rails app ever written, for example), without suffering from such problems. They also never suffer from the problem of having to push changes to primary keys (that were never supposed to happen) to all of the foreign key tables.
@AaronBertrand I would say that the only way in which they might be more efficient is if a join was not needed at all. The only places I consider the use of natural keys is with standard code lists such as ISO4127 currency codes (which are human-recognisable), and I might use GBP, EUR etc as the foreign key to an primary or alternative key on the currency code table.
