Home • Explore • Blog • CI
☰



postgresql
12th chunk of `doc/src/sgml/ref/create_index.sgml`
3602aa8f067511573e688685d60c154a15432771c9acd30c0000000100000d9b
 would drive the machine into swapping.
  </para>

  <para>
   <productname>PostgreSQL</productname> can build indexes while
   leveraging multiple CPUs in order to process the table rows faster.
   This feature is known as <firstterm>parallel index
   build</firstterm>.  For index methods that support building indexes
   in parallel (currently, B-tree and BRIN),
   <varname>maintenance_work_mem</varname> specifies the maximum
   amount of memory that can be used by each index build operation as
   a whole, regardless of how many worker processes were started.
   Generally, a cost model automatically determines how many worker
   processes should be requested, if any.
  </para>

  <para>
   Parallel index builds may benefit from increasing
   <varname>maintenance_work_mem</varname> where an equivalent serial
   index build will see little or no benefit.  Note that
   <varname>maintenance_work_mem</varname> may influence the number of
   worker processes requested, since parallel workers must have at
   least a <literal>32MB</literal> share of the total
   <varname>maintenance_work_mem</varname> budget.  There must also be
   a remaining <literal>32MB</literal> share for the leader process.
   Increasing <xref linkend="guc-max-parallel-maintenance-workers"/>
   may allow more workers to be used, which will reduce the time
   needed for index creation, so long as the index build is not
   already I/O bound.  Of course, there should also be sufficient
   CPU capacity that would otherwise lie idle.
  </para>

  <para>
   Setting a value for <literal>parallel_workers</literal> via <link
   linkend="sql-altertable"><command>ALTER TABLE</command></link> directly controls how many parallel
   worker processes will be requested by a <command>CREATE
   INDEX</command> against the table.  This bypasses the cost model
   completely, and prevents <varname>maintenance_work_mem</varname>
   from affecting how many parallel workers are requested.  Setting
   <literal>parallel_workers</literal> to 0 via <command>ALTER
   TABLE</command> will disable parallel index builds on the table in
   all cases.
  </para>

  <tip>
   <para>
    You might want to reset <literal>parallel_workers</literal> after
    setting it as part of tuning an index build.  This avoids
    inadvertent changes to query plans, since
    <literal>parallel_workers</literal> affects
    <emphasis>all</emphasis> parallel table scans.
   </para>
  </tip>

  <para>
   While <command>CREATE INDEX</command> with the
   <literal>CONCURRENTLY</literal> option supports parallel builds
   without special restrictions, only the first table scan is actually
   performed in parallel.
  </para>

  <para>
   Use <link linkend="sql-dropindex"><command>DROP INDEX</command></link>
   to remove an index.
  </para>

  <para>
   Like any long-running transaction, <command>CREATE INDEX</command> on a
   table can affect which tuples can be removed by concurrent
   <command>VACUUM</command> on any other table.
  </para>

  <para>
   Prior releases of <productname>PostgreSQL</productname> also had an
   R-tree index method.  This method has been removed because
   it had no significant advantages over the GiST method.
   If <literal>USING rtree</literal> is specified, <command>CREATE INDEX</command>
   will interpret it as <literal>USING gist</literal>, to simplify conversion
   of old databases to GiST.
  </para>

  <para>
    Each backend running <command>CREATE INDEX</command> will report
Title: Parallel Index Builds: Memory, Workers, and Control
Summary
PostgreSQL can use multiple CPUs for faster index creation (parallel index build). `maintenance_work_mem` sets the maximum memory for each index build. A cost model determines the number of worker processes. Parallel builds benefit from increased `maintenance_work_mem`. Parallel workers need at least a 32MB share of `maintenance_work_mem`, with 32MB remaining for the leader process. Increasing `max_parallel_maintenance_workers` may allow more workers, reducing creation time if not I/O bound. The `parallel_workers` setting via `ALTER TABLE` directly controls worker processes for `CREATE INDEX`, bypassing the cost model. Setting `parallel_workers` to 0 disables parallel builds. `CREATE INDEX CONCURRENTLY` supports parallel builds, but only the first table scan is parallel. Use `DROP INDEX` to remove an index. Prior PostgreSQL releases had an R-tree index method that has been removed, with `USING rtree` interpreted as `USING gist`.