Track down which resource limits index creation in PostgreSQL

Question

This index creation is slow (2 million rows) :

CREATE UNIQUE INDEX foo_index_id ON foo (id);

I modified several parameters which did not significantly affect total time:

• max_parallel_maintenance_workers
• maintenance_work_mem

The manual states that I/O and CPU can be bottlenecks.

Increasing max_parallel_maintenance_workers may allow more workers to be used, which will reduce the time needed for index creation so long as the index build is not already I/O bound. Of course, there should also be sufficient CPU capacity that would otherwise lie idle.

How can I check which resource is actually a bottleneck ?

I tried to run an explain plan with I/O timings to guess, but it is not allowed for such queries.

(the database is hosted by a DBaaS, so I have no access to the server itself to get OS metrics)

Answer 1

Use top and see if the associated processes take 100% of a CPU core. If yes, you are CPU bound, if not, you should be I/O bound.

Note that building indexes has different stages, which may exhibit different characteristics. You can look at pg_stat_progress_create_index to see what it is doing and how far it is.