You can use the cross-node parallel execution feature to accelerate the process of creating B-tree indexes. This topic describes why the feature can be used to accelerate the process of creating B-tree indexes. This topic also describes how to use the feature to create B-tree indexes.

How it works

When ApsaraDB PolarDB PostgreSQL-compatible edition creates indexes, it scans the heap table for which you want to create indexes and creates indexed entries. Then, it creates an index B-tree based on the indexed entries.

When you use this feature to accelerate the process of creating indexes, the system automatically creates a query coordinator (QC) process to scan the heap table in parallel. Then, the process used to create indexes receives the scan results sent by the QC process and creates the indexes.

Note

  • You can use this feature to accelerate the process of creating indexes only for general columns. You cannot use this feature to accelerate the process of creating indexes when you use the CONCURRENTLY or INCLUDE syntax to create indexes.
  • You cannot create indexes for columns that are specified by expressions.

Parameters

If you want to use the cross-node parallel execution feature to accelerate the process of creating indexes, configure the following parameters.

Parameter Description
polar_px_enable_btbuild Specifies whether to use this feature to accelerate the process of creating indexes. Valid values:
  • off: This value indicates that this feature is not used to accelerate the process of creating indexes. By default, this parameter is set to off.
  • on: This value indicates that this feature is used to accelerate the process of creating indexes.
polar_px_dop_per_node Specifies the degree of parallelism that is in effect when you use this feature to accelerate the process of creating indexes. The default value is 1. We recommend that you set the value to 8 or 16.

This parameter also specifies the degree of parallelism for cross-node parallel execution. For more information, see Use the cross-node parallel execution feature to run analytical queries.

polar_px_enable_replay_wait If you set polar_px_enable_btbuild to on, the polar_px_enable_replay_wait parameter automatically takes effect for the current session. This ensures that the latest entries of the table are indexed. After the indexes are created, the system resets the parameter to the default value in the database.

Examples

Background information

Execute the following statement to create a table named test:

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 |           |          |

To use cross-node parallel execution to accelerate the process of creating indexes for the test table, perform the following steps:

  1. Set the polar_px_enable_btbuild parameter to on to use the cross-node parallel execution feature to accelerate the process of creating indexes.
    Execute the following statement:
    SET polar_px_enable_btbuild=on;

    Check whether the configuration takes effect:

    SHOW polar_px_enable_btbuild;

    The following query result is returned:

      polar_px_enable_btbuild
    -------------------------
     on
    (1 row)
  2. Execute the following statement to create indexes:
    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 If you want to use the cross-node parallel execution feature to accelerate the process of creating indexes, add the px_build option in the CREATE INDEX statement.

After the indexes are created, the index type information contains the (px_build=finish) field. This field indicates that the indexes are created by using the cross-node parallel execution feature.

If polar_px_enable_btbuild is set to on but the px_build option is not added in the CREATE INDEX statement, the indexes are created by using the native method provided by ApsaraDB PolarDB PostgreSQL-compatible edition. The following code provides an 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

It is approximately five times faster to create indexes for a large table by using the cross-node parallel execution feature than by using the native method provided by ApsaraDB PolarDB PostgreSQL-compatible edition.