B01NANLD8C EBOK by Unknown

B01NANLD8C EBOK by Unknown

Author:Unknown
Language: eng
Format: azw3
Tags: COM018000 - COMPUTERS / Data Processing
Publisher: Packt Publishing
Published: 0101-01-01T00:00:00+00:00


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 idx_id2 ON t_test (id);

CREATE INDEX

Time: 46.774 ms

The speed has now doubled just because sorting has been improved so much.

There are more administrative jobs which can benefit from more memory. The most prominent ones are the VACUUM clause (to clean out indexes) and ALTER TABLE clause. The rules for the maintenance_work_mem variable are the same as for the work_mem variable. The setting is per operation and only the required memory is allocated on the fly.



Download



Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.