5

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?

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
1

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

Improve this answer
2
  • "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?
    – Luciano Andress Martini
    May 25, 2016 at 14:51
  • 1
    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.
    – o0x258
    May 25, 2016 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.