0

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)

1 Answer 1

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.

2
  • Well, I have no access to OS metrics, as stated in the question. If I would have them, I would use utilities (eg iotop for IO) on the specific process id I can get from pg_stat_activity.backend_type= 'parallel worker'. All I have is CPU and memory consumption (no I/O) on the whole database, so I can't figure out which I caused by index build and which is related to other queries.
    – GradedJestRisk
    Dec 23, 2021 at 16:39
  • 1
    I didn't see that. Then you are out of luck. One of the disadvantages of hosted databases: you are at the mercy of what your provider lets you see. Dec 23, 2021 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy