Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ubNsH-000CH8-PW for pgsql-general@arkaria.postgresql.org; Mon, 14 Jul 2025 18:26:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ubNsF-00B0AG-N2 for pgsql-general@arkaria.postgresql.org; Mon, 14 Jul 2025 18:26:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ubNsF-00B0A8-BG for pgsql-general@lists.postgresql.org; Mon, 14 Jul 2025 18:26:00 +0000 Received: from mail-lf1-x12f.google.com ([2a00:1450:4864:20::12f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ubNsD-007gGl-24 for pgsql-general@lists.postgresql.org; Mon, 14 Jul 2025 18:25:59 +0000 Received: by mail-lf1-x12f.google.com with SMTP id 2adb3069b0e04-555024588a8so4144952e87.0 for ; Mon, 14 Jul 2025 11:25:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1752517556; x=1753122356; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=k16IUrCszlEMG5hFBGl4F0soZUXNRjMn7PUOSMWojUs=; b=j8U3UYwTAnIlcd7dA9Y/d63rYp/aR9CTW+hCDANO8cH9SELIHbGbjtclr29BAGqYAN Y1MHVdQCfKnv6Mn15ds2VirfxuV9vycVyDV5s8SEqSSd6qLS9KIQq1KrK53N+2LEKg2H OrSSIQnC9n7U0xZJUCqUh2m33+1reRKzRcYbkq1+jvSV0Nj9sQ372VbglWKUuK0Erp/V RjtSt7GemJGloFvYev3ol0DbC5o4cGdscAfYgG8+iqDOOLvtUFUvBDaSlJi2eYAwMMXM wcvn6ivp7mE4R81+VqLl8aN2DwiiaqzLLKni4ua9mIQO6eTV8awbSd7I7+Mub/3jjBZ4 8ozQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1752517556; x=1753122356; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=k16IUrCszlEMG5hFBGl4F0soZUXNRjMn7PUOSMWojUs=; b=ii6gdKZmHdpQGbKtUe/sYmFT3b0iJYIwJmZUJUxT9cGDEv+ArDoBT9Ty+u6j2OWEpC 8iigC7Wr/kdSp8UCxiXgukp0gMVNAvgCvbS/ohJF7oWu0OftLAg8iklOd7KlU1OWc0Sf 2azoz46vJObZPy4W3O1Op+O4DwxiyHj2uKQOMovuvQvHDhACU2OcDVKPIhSXp+3zMYnB iD+tgI99k9vQeyDrLSElfHMKxae0a7DHNwcZ+HnEUyEohhgis2DOehI1PJTdk6//MGSG DFbASQ2UW7qnKnyltyW+b9EGfYAb3eQdWH3jCZSxK6i/qFwXaphFlJYi1ZF2mG8yBQXa 1y3Q== X-Gm-Message-State: AOJu0YzG615ctbIC8VJrJI0hhDpRCoITkWIuMG103s0Jxf9syBcfSEwv Sww0W8yJL0n/qi/T8HQ5cl3M33gbESLvqmsGgdtA6jMnFYBuwkxSxsIggfLOC1ECOvDUAh0+tCE EvZ3PyPHz0/aXzGe/Uhtm4K7h/K2iV8jFjw== X-Gm-Gg: ASbGncsEH65VRD0I6/0vX/Mivpsh/KrkVWVRPECZcTfLSuNWXGdL8KEnFMN2UK1DjnQ JACFwFa9P5XrI8sYLpP9HPgkU8rNk1+wuTaSOtf74k2nK8sT9fw7LgXzpnmvP6czMQdZRUel73l XkyY7isn4EbvrqPdOoDSadD9drdBSJEf7817wYH3Mr78aaOMJHwXShrcnpSZE5LwqZcNyFl8B+A P5FzIwRiEAPxxxl X-Google-Smtp-Source: AGHT+IGRWMqGOvQVZfMw48eqJC+hwgA7LmXzmdO8Bbwv3bdb79bFiode3q6haQGN5HpUsd5gs037YKp9KxIIW3lmnow= X-Received: by 2002:a05:6512:4154:b0:553:522f:61b6 with SMTP id 2adb3069b0e04-55a04603d4cmr3070078e87.33.1752517555516; Mon, 14 Jul 2025 11:25:55 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Greg Hennessy Date: Mon, 14 Jul 2025 14:25:19 -0400 X-Gm-Features: Ac12FXwPSlJwm--Od_fehq-FQSlOdgkAzZwyXGVsGmKYsp98UDYOcEdxshZR2Lg Message-ID: Subject: Re: optimizing number of workers To: "Weck, Luis" Cc: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000005bfd560639e7ca46" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005bfd560639e7ca46 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Setting those values to zero (not something I'd want to do in production) changes the number of workes from 10 to 13. At least something, but if anyone knows where discussion about how to use large numbers of CPU's in postgresql are being held I'd appreciate learning about it. Greg On Fri, Jul 11, 2025 at 2:11=E2=80=AFPM Weck, Luis wro= te: > *From: *Greg Hennessy > *Date: *Thursday, July 10, 2025 at 4:40=E2=80=AFPM > *To: * pgsql-general@lists.postgresql.org < > pgsql-general@lists.postgresql.org> > *Subject: *optimizing number of workers > > Having just received a shiny new dual CPU machine to use as a postgresql > server, I'm trying to do some reasonable efforts to configure it > correctly. The hard > ware has 128 cores, and I am running a VM with Redhat 9 and Postgresql > 16.9. > > In postgresql.conf I have: > max_worker_processes =3D 90 # (change requires restart) > max_parallel_workers_per_gather =3D 72 # gsh 26 oct 2022 > max_parallel_maintenance_workers =3D 72 # gsh 12 jun 2025 > max_parallel_workers =3D 72 # gsh 12 jun 2025 > max_logical_replication_workers =3D 72 # gsh 12 jun 2025 > max_sync_workers_per_subscription =3D 72 # gsh 12 jun 2025 > autovacuum_max_workers =3D 12 # max number of autovacuum > subprocesses > > When I do a simple count of a large (large being 1.8 Billion entries), I > get > about 10 workers used. > > prod_v1_0_0_rc1=3D# explain (analyze, buffers) select count(*) from > gaiadr3.gaia_source; > > QUERY PLAN > > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------- > Finalize Aggregate (cost=3D14379796.81..14379796.82 rows=3D1 width=3D8) > (actual time=3D16702.806..16705.479 rows=3D1 loops=3D1) > Buffers: shared hit=3D2507481 > -> Gather (cost=3D14379795.78..14379796.79 rows=3D10 width=3D8) (act= ual > time=3D16702.513..16705.470 rows=3D11 loops=3D1) > Workers Planned: 10 > Workers Launched: 10 > Buffers: shared hit=3D2507481 > -> Partial Aggregate (cost=3D14379785.78..14379785.79 rows=3D1 > width=3D8) (actual time=3D16691.820..16691.821 rows=3D1 loops=3D11) > Buffers: shared hit=3D2507481 > -> Parallel Index Only Scan using gaia_source_nest128 on > gaia_source (cost=3D0.58..13926632.85 rows=3D181261171 width=3D0) (actua= l > time=3D0.025..9559.644 rows=3D164700888 loops=3D11) > Heap Fetches: 0 > Buffers: shared hit=3D2507481 > Planning: > Buffers: shared hit=3D163 > Planning Time: 14.898 ms > Execution Time: 16705.592 ms > > Postgres has chosen to use only a small fraction of the CPU's I have on > my machine. Given the query returns an answer in about 8 seconds, it may = be > that Postgresql has allocated the proper number of works. But if I wanted > to try to tweak some config parameters to see if using more workers > would give me an answer faster, I don't seem to see any obvious knobs > to turn. Are there parameters that I can adjust to see if I can increase > throughput? Would adjusting parallel_setup_cost or parallel_tuple_cost > likely to be of help? > > I believe you can decrease min_parallel_table_scan_size (default is 8MB) > and min_parallel_index_scan_size (default 5112kB). The number of workers > depends also on a multiple of these settings. > > --0000000000005bfd560639e7ca46 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Setting those values to zero (not something I'd want t= o do in production) changes the number of workes
from 10 to 13. At leas= t something, but if anyone knows where discussion about how to use
large numbers of CPU's in postgresql are being held I'd appreciat= e learning about it.

Greg


On Fri, Jul 11, 2025 at 2:11=E2=80=AFPM Weck, Luis <luis.weck@pismo.io> wrote:
= From: Greg Hennessy <greg.hennessy@gmail.com>
= Date: Thursday, July 10, 2025 at 4:40=E2=80=AFPM
= Subject: optimizing number of workers
Having just received a shiny new dual CPU machine=C2=A0to = use as a postgresql
server, I'm trying to do some reasonable efforts to co= nfigure it correctly. The hard
ware has 128 cores, and I am running a VM with Redhat 9 an= d Postgresql=C2=A0 16.9.

In postgresql.conf I have:
max_worker_processes =3D 90 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 # (change requires restart)
max_parallel_workers_per_gather =3D 72 =C2=A0 =C2=A0# gsh = 26 oct 2022
max_parallel_maintenance_workers =3D 72 =C2=A0 # gsh 12 ju= n 2025
max_parallel_workers =3D =C2=A072 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0# gsh 12 jun 2025
max_logical_replication_workers =3D 72 =C2=A0 =C2=A0# gsh = 12 jun 2025
max_sync_workers_per_subscription =3D 72 =C2=A0 # gsh 12 j= un 2025
autovacuum_max_workers =3D 12 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 # max number of autovacuum subprocesses

When I do a simple count of a large (large being 1.8 Billi= on entries), I get
about 10 workers used.

prod_v1_0_0_rc1=3D# explain (analyze, buffers) select coun= t(*) from gaiadr3.gaia_source;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN
----------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------
=C2=A0Finalize Aggregate =C2=A0(cost=3D14379796.81..143797= 96.82 rows=3D1 width=3D8) (actual time=3D16702.806..16705.479 rows=3D1 loop= s=3D1)
=C2=A0 =C2=A0Buffers: shared hit=3D2507481
=C2=A0 =C2=A0-> =C2=A0Gather =C2=A0(cost=3D14379795.78.= .14379796.79 rows=3D10 width=3D8) (actual time=3D16702.513..16705.470 rows= =3D11 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Workers Planned: 10
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Workers Launched: 10
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D25= 07481
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Partial Aggr= egate =C2=A0(cost=3D14379785.78..14379785.79 rows=3D1 width=3D8) (actual ti= me=3D16691.820..16691.821 rows=3D1 loops=3D11)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buf= fers: shared hit=3D2507481
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&g= t; =C2=A0Parallel Index Only Scan using gaia_source_nest128 on gaia_source = =C2=A0(cost=3D0.58..13926632.85 rows=3D181261171 width=3D0) (actual time=3D= 0.025..9559.644 rows=3D164700888 loops=3D11)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Heap Fetches: 0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D2507481
=C2=A0Planning:
=C2=A0 =C2=A0Buffers: shared hit=3D163
=C2=A0Planning Time: 14.898 ms
=C2=A0Execution Time: 16705.592 ms

Postgres has chosen to use only a small fraction of the CP= U's I have on
my machine. Given the query returns an answer in about 8 s= econds, it may be
that Postgresql has allocated the proper number of works. = But if I wanted
to try to tweak some config parameters to see if using mor= e workers
would give me an answer faster, I don't seem to see an= y obvious knobs
to turn. Are there parameters that I can adjust to see if = I can increase
throughput? Would adjusting parallel_setup_cost or paralle= l_tuple_cost
likely to be of help?=C2=A0
I believe you can decreas= e min_parallel_table_scan_size (default is 8MB) and min_parallel_index_scan= _size (default 5112kB). The number of workers depends also on a multiple of= these settings.

--0000000000005bfd560639e7ca46--