Perfomance of CREATE INDEX vs CREATE INDEX CONCURRENTLY in PostgreSQL

I know CREATE INDEX CONCURRENTLY is slower, but how many longer it will takes to finish in comparison with the traditional CREATE INDEX? 40% more or much more than that?

I am creating an index in a table with 10000 registers, and in a column with 15 chars. It is taking 10 minutes using the traditional CREATE INDEX.

I know it is very difficulty to answer that, but there is a way to estimate?

2ndQuadrant says:


[CREATE INDEX CONCURRENTLY] must wait for all existing transactions to finish before starting the second phase on index build. This guarantees that no new broken HOT chains are created after the second phase begins.

I've seen CREATE INDEX CONCURRENTLY take over 90 seconds (before I aborted it) when creating the same index not CONCURRENTLY took 0.5 seconds. So it can be much slower.

There was no write activity on the table in the mean time, but other transactions were open on other tables. I think 2ndQuadrant mean that all transactions must end, not just those which have already written to the table in question.

StackExchange Database Administrators Q#139504, answer score: 3