Re: maintenance_work_mem impact?

On Tue, 2024-03-19 at 16:05 +0000, Adithya Kumaranchath wrote:
> I have a table approx. 20GB.
>
> 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 onlyone 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.
>    1. Any advice or thoughts?
>    2. Is there any other parameter that can accelerate index creation? 

It is safe as long as you have enough free memory on the machine.

You can verify with tools like "free" on Linux (look for "available" memory).

Yours,
Laurenz Albe