Mastering PostgreSQL 9.6

Speedup administrative tasks

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

test=# SET maintenance_work_mem TO '1 MB'; SET test=# \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 speedup sorting, which is essentially what the CREATE INDEX clause does:

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