Accelerate index creation using cross-node parallel execution-阿里云帮助中心

You can use cross-node parallel execution to accelerate the creation of B-tree indexes, including GLOBAL indexes. This topic describes how this feature works and how to use it.

How it works

To build an index, PolarDB for PostgreSQL first scans the base table to construct index entries. Then, it builds the entire index tree from these entries.

When you use cross-node parallel execution to accelerate B-tree index building, the system automatically creates a Query Coordinator (QC) process. This process performs a parallel scan of the base table. The index building process then receives the scan results from the QC to complete the index creation.

Architecture of accelerated index creation by using cross-node parallel execution

Precautions

This feature supports building indexes only on columns of common data types. Index building syntax such as `CONCURRENTLY` or `INCLUDE` is not supported.
Expressions cannot be used as index columns.

Parameters

To accelerate index creation using cross-node parallel execution, you can use the following parameters:

Parameter	Description
polar_px_enable_btbuild	Specifies whether to use cross-node parallel execution to accelerate index creation. Valid values: off: The feature is disabled. This is the default value. on: The feature is enabled.
polar_px_dop_per_node	Specifies the degree of parallelism for accelerating index building with cross-node parallel execution. The default value is 1. The recommended value is 8 or 16. This parameter also specifies the degree of parallelism for cross-node parallel execution. For more information, see Use cross-node parallel execution for analytical queries. You can set this parameter for specific database roles.
polar_px_enable_replay_wait	When you use cross-node parallel execution to accelerate index building, you do not need to enable polar_px_enable_replay_wait in the current session. This parameter is automatically enabled to ensure that the most recent data entries are added to the index, which maintains the integrity of the index table. After the index is created, this parameter is reset to the database default setting.
polar_bt_write_page_buffer_size	Specifies the write I/O policy during index building. The default value is 0, which disables the policy. The unit is blocks. The maximum value is 8192. A value of 4096 is recommended. If this parameter is disabled, full index pages are written to the disk one block at a time during index creation. If this parameter is enabled, a buffer of the size specified by polar_bt_write_page_buffer_size is cached in the kernel. Index pages that need to be written to the disk are merged in this buffer and then written together. This method avoids the performance overhead of frequent I/O scheduling. This parameter can provide an additional 20% performance improvement for index creation.

Example

Example: Background

Run the following command to create a test table.

CREATE TABLE test(id int,id2 int);

Query the table schema:

\d test
               Table "public.test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
 id2    | integer |           |          |

Follow these steps to build an index on the test table using cross-node parallel execution.

Enable the feature that accelerates index creation using cross-node parallel execution.
Command:
```
SET polar_px_enable_btbuild=on;
```
Check the setting status:
```
SHOW polar_px_enable_btbuild;
```
The following result is returned:
```
  polar_px_enable_btbuild
-------------------------
 on
(1 row)
```

Use the following syntax to create the index.

CREATE INDEX t ON test(id) WITH(px_build=on);

Query the table schema:

\d test
               Table "public.test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
 id2    | integer |           |          |
Indexes:
    "t" btree (id) WITH (px_build=finish)

Note To accelerate index creation using cross-node parallel execution, add the `px_build` option to the `CREATE INDEX` syntax.

After the index is built, the index information for the table includes the `(px_build=finish)` field. This field indicates that the index was built using cross-node parallel execution.

If you enable the polar_px_enable_btbuild parameter but do not add the `px_build` option to the `CREATE INDEX` syntax, the index is built using the native method of PolarDB for PostgreSQL. For example:

CREATE INDEX t ON test(id);

Query the table schema:

\d test
                Table "public.test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
Indexes:
    "t" btree (id)

Performance data

Using cross-node parallel execution to accelerate index building can reduce the time required to create an index on a large table by a factor of nearly five compared with the native index creation method of PolarDB for PostgreSQL.

The following figure compares the index building performance on a 500 GB data table. The x-axis represents the number of composite indexes built. The y-axis represents the index building duration in milliseconds (ms).

Index building time (cross-node parallel execution)