Markus, Oh, so you want USEFUL answers. OK.
> Now when I want to search for a type in types or another table that > references types(type_id), under what circumstances is it advisable to > have a surrogate integer key and not use the unique type name? When using the actual name will be a performance problem. > Is > searching for an integer as fast as is searching for a string when both > have an index? Not usually, no. The index on the text values will simply be larger than the one on 4-byte INTs, which means it's "slower", assuming you run out of memory some of the time. If your whole DB fits in RAM, it's not worth worrying about. > How many records in the type table do I need to make a > surrogate key a not unsignificantly faster way to retrieve a row? It needs to be large enougth that the difference in data types makes a difference in whether or not it will fit into sort_mem, and how likely it is to be already cached in memory. > What > about joins? Double jeopardy; you're using the column twice so double the storage difference. Otherwise, it's just the same issue; does it still fit in RAM or not? > Are these the right questions? Also you'll want to consider the speed of CASCADE operations whenever a type_name changes. If these changes occur extremely infrequently, then you can ignore this as well. -- -Josh Berkus Aglio Database Solutions San Francisco ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
