Perfomance of CREATE INDEX vs CREATE INDEX CONCURRENTLY in PostgreSQL

Question

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?

Answer 1

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.

Answer 2

It depends on the activity on the table. It there is no activity then there should be no difference.

• Luciano Andress Martini: "If there is no activity there should be no difference.." So what you're saying is that create index concurrently is always a good idea? Because if i have activity in the database i don't wanna to use the traditional create index im right? And in the other way(no activity) they will be identical so the traditional option will make no difference.... is that true?
• o0x258: Yes. In a production database you want to avoid locks on the table to ensure that the index creation does not disturb other users. Of course the creation will be slower but in prod the main goal is to keep things going then to finish admin tasks quicker. In dev or test you can chose what ever you like more.