Yesterday I wrote about selects on int4 vs. texts.
One of the comments that caught my attention was question about index creation time. So, let's see…
Before I will comment on sensibility of this test, let's see the timings.
Created tables with single column – text or int4. Each table got 1 million records. Text based tables had values of always the same length 4, 6, 8, 10, .. 22 characters.
Integer storing table had values in range 960 – 1073741659 (randomly chosen from 1 – 230).
All values, in all tables, are unique, and indexes where created using simple:
CREATE INDEX xxx ON TABLE (COLUMN)
Each index was created 10 times in a row (with drops in between of course). From these 10 times, I took the 2nd worst (which represents 90 percentile of times). Results:
Clearly – integer based indexes are way faster to create. That's fact. But what exactly does it tell us?
Let's face it – if you are in a position when you can choose between using surrogate and natural primary key – you (very usually) will index the “natural" column anyway.
For example – if you have users table – if you choose to have surrogate primary key – this index is faster. But you still have index on username (at least – every database I've seen so far had one).
So, while int4 indexes are faster than text ones, int4 index + text index will be always slower than just text index.
I think that concludes the topic – I am not trying to convert people to using natural keys with these posts – just merely showing some numbers.