parallel index creation: maintenance_work_mem not honored?

From: Fabio Pardi <f(dot)pardi(at)portavita(dot)eu>
To: "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: parallel index creation: maintenance_work_mem not honored?
Date: 2022-06-20 09:42:44
Message-ID: 13fa6c59-16bc-9945-f913-2e0c58d34d12@portavita.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all,

I noticed that after an upgrade from 9.6 to 14.x, Postgres started producing temp files.

I narrowed down the behavior to parallel operations.

I was able to reproduce the following on a test machine running latest Postgres 14.4 on Ubuntu 18.04

CREATE TABLE test_parallel (i int);
INSERT INTO test_parallel VALUES (generate_series(0,10000000));

table size: 346 MB
maintenance_work_mem = '5GB' (but the same would happen if we set much higher values)

CREATE INDEX test_parallel_i_idx ON test_parallel (i);

... LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp4611.1.sharedfileset/2.0", size 37879808
... STATEMENT:  CREATE INDEX test_parallel_i_idx ON test_parallel (i);
... LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp4611.1.sharedfileset/0.0", size 59326464
... STATEMENT:  CREATE INDEX test_parallel_i_idx ON test_parallel (i);
... LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp4611.1.sharedfileset/1.0", size 103194624
... STATEMENT:  CREATE INDEX test_parallel_i_idx ON test_parallel (i);

After disabling parallelism with:

ALTER TABLE test_parallel SET (parallel_workers = 0);

The same 'CREATE INDEX' statement does not produce temp files.

Is this behavior expected? I cannot find mentions on the documentation nor on the literature available to me.

regards,

fabio pardi

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Peter Geoghegan 2022-06-20 14:47:21 Re: parallel index creation: maintenance_work_mem not honored?
Previous Message Kenny Bachman 2022-06-20 04:54:34 Re: postrgesql query planner wrong desicion