Mastering PostgreSQL 12 - Third Edition

Speeding up administrative tasks

There are more operations that actually have to do some sorting or memory allocation of some kind. The administrative ones such as the CREATE INDEX clause don't rely on the work_mem variable and use the maintenance_work_mem variable instead. Here is how it works:

test=# SET maintenance_work_mem TO '1 MB'; 
SETtest=# \timing
Timing is on.test=# CREATE INDEX idx_id ON t_test (id);CREATE INDEX
Time:  104.268 ms

As you can see, creating an index on 2 million rows takes around 100 milliseconds, which is really slow. Therefore, the maintenance_work_mem variable can be used to speed up sorting, which is essentially what the CREATE INDEX clause does:

test=# SET maintenance_work_mem TO '1 GB'; 
SETtest=# CREATE INDEX ...