maintenance_work_mem impact?

2024-03-19 Thread Adithya Kumaranchath 
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.


  1.  Any advice or thoughts?
  2.  Is there any other parameter that can accelerate index creation?

Thanks,
Ad

Re: maintenance_work_mem impact?

2024-03-19 Thread Laurenz Albe 
On Tue, 2024-03-19 at 16:05 +, 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