Hi all,
I have a table approx. 20GB.
I have a create unique index statement.
CREATE UNIQUE INDEX testindex_v1 ON testtable1 (test_index);
My observations:
maintenance_work_mem = 2G
max_parallel_workers = '16'
The create index completes in 20 minutes.
When I change this:
maintenance_work_mem = 16G
max_parallel_workers = '16'
It completes in 9 minutes. So I can see that I can gain performance by changing this number.
So it is faster but the question I have is it safe to set it to such a high number? I am aware that only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, so it's safe to set this value significantly larger. I have 128GB memory.
- Any advice or thoughts?
- Is there any other parameter that can accelerate index creation?
Thanks,
Ad