Postgres index creation time

Question

Postgres is creating simple index more than 2 days. Where can I see a log, or something about status index creation? Before this situation I've created index couple times and it took about an hour or less. I have about 5.5m rows and execute next command:

CREATE INDEX collapse_url ON tablle (url, collapse)

First, check to see if the CREATE INDEX statement is actually being blocked by another process/transaction: wiki.postgresql.org/wiki/Lock_Monitoring. Please update your post with your findings — richyen
– richyen, Commented Oct 31, 2019 at 16:29

Jeremy · Accepted Answer · 2019-10-31 16:51:03Z

10

There are two possibilities:

The CREATE INDEX statement is waiting for a lock.

You should be able to see that in the pg_stat_activity view.

If that is your problem, end all concurrent long running transactions (e.g. using pg_terminate_backend).
The CREATE INDEX statement is truly taking very long (unlikely with a few million rows).

In that case, you can speed up processing by increasing maintenance_work_mem before you create the index.

There have been relevant improvements in this area in recent versions:

PostgreSQL v11 introduced parallel index builds.
PostgreSQL v12 introduced the view pg_stat_progress_create_index to monitor the progress of CREATE INDEX.
PostgreSQL v12 also introduced CREATE INDEX CONCURRENTLY to avoid a long ACCESS EXCLUSIVE lock on the table.

edited Oct 31, 2019 at 16:51

Jeremy

6,77322 silver badges21 bronze badges

answered Oct 31, 2019 at 16:34

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres index creation time

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related