Обсуждение: What order in primary key definition ?
Hi, I am wondering if the order in which primary key fields are ordered has an impact on performance... If my primary key has, say, two fields, my spontaneous guess would be to put them from the most general to the most specific : primary key ( generalKey, specificKey) generalKey may have 2 to 10 different values in the table (say it is a society identifier in an accounting software for example). SpecificKey, on the contrary, might have 100 to 1000 different values (say it is the account identifier for example). But on second thought, I wonder if it would be better to define primary key (specificKey, genericKey) as it could make index access more discriminent. Is this the way to go ? Thanks, Franck
franck.routier@axege.com writes: > I am wondering if the order in which primary key fields are ordered has an > impact on performance... Putting the more specific key first would make for a small improvement in the performance of the index, because key comparisons would more often be able to make a decision after comparing the first column and not have to compare the second. This is not a big deal though. What I'd counsel asking yourself is whether you're ever going to search the table with only one of the columns specified, and if so put that one first. This will save needing a second index. If you need to search by *both* columns independently, then you will need two indexes anyway, one on (a,b) and one on (b) --- see http://www.postgresql.org/docs/8.1/static/indexes-multicolumn.html regards, tom lane