Use Elastic Parallel Query to accelerate index creation - PolarDB

Building B-tree indexes on large tables is time-consuming with the native single-process method. Elastic Parallel Query (EPQ) parallelizes the heap scan phase of index creation, reducing build time by approximately 5x on large tables. This topic describes how EPQ works, the parameters to configure, and the steps to create a B-tree index or global B-tree index using EPQ on PolarDB for PostgreSQL (Compatible with Oracle).

How it works

When PolarDB for PostgreSQL (Compatible with Oracle) builds an index, it scans the heap table to collect index entries, then constructs a B-tree from those entries.

With EPQ enabled, the database spawns a query coordinator (QC) process that scans the heap table in parallel. The index-building process receives the scan results from the QC process and constructs the index. This parallel scan reduces index creation time on large tables.

Limitations

EPQ-accelerated index creation applies only to B-tree indexes on general columns. The CONCURRENTLY and INCLUDE clauses are not supported.
Indexes on expression columns are not supported.

Parameters

Configure the following parameters to use EPQ for index creation.

Parameter	Default	Description
`polar_px_enable_btbuild`	`off`	Enables EPQ-accelerated index creation. Set to `on` to activate.
`polar_px_dop_per_node`	`1`	Sets the degree of parallelism (DOP) per node. Set to `8` or `16` for large tables. This parameter also controls the DOP for EPQ analytical queries. For details, see Run analytical queries.
`polar_px_enable_replay_wait`	(database default)	Ensures the most recent table entries are included in the index. When `polar_px_enable_btbuild` is set to `on`, this parameter activates automatically for the current session and resets to the database default after the index is created.
`polar_bt_write_page_buffer_size`	`0`	Controls the write I/O policy during index creation. The unit is block. The maximum value is `8192`. Set to `4096` for optimal performance.

`polar_bt_write_page_buffer_size` behavior:

`0` (default): Flushes index entries to disk block by block as each index page fills up.
Non-zero value: Buffers index entries in a kernel buffer of the specified size. When the buffer is full, all entries flush to disk in a single I/O operation, reducing I/O scheduling overhead. This can reduce index creation time by 20%.

Parameter dependencies:

Setting polar_px_enable_btbuild=on automatically activates polar_px_enable_replay_wait for the current session. After the index is created, polar_px_enable_replay_wait resets to its database default.

Create a B-tree index with EPQ

The following steps use a sample table to show how to enable EPQ and create a B-tree index.

Before you begin, ensure that you have:

A table in PolarDB for PostgreSQL (Compatible with Oracle)

Set up the sample table

Create a table named test:

CREATE TABLE test(id int, id2 int);

Verify the table structure:

\d test

Expected output:

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

Create an index

Enable EPQ-accelerated index creation for the current session:

SET polar_px_enable_btbuild = on;

Verify the setting:

SHOW polar_px_enable_btbuild;

Expected output:

polar_px_enable_btbuild
-------------------------
 on
(1 row)

Create the index with the px_build=on option:

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

Verify the index:

\d test

Expected output:

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

The px_build=finish field in the index definition confirms that EPQ was used to build the index.

The px_build=on option in the CREATE INDEX statement is required. If polar_px_enable_btbuild is set to on but px_build=on is omitted, PolarDB uses its native index creation method instead: In this case, the index definition shows no px_build field:

CREATE INDEX t ON test(id);

Indexes:
    "t" btree (id)

Performance data

EPQ builds B-tree indexes on large tables approximately 5 times faster than the native method.

The following figure compares global B-tree index creation performance before and after enabling EPQ, using a 130 GB dataset.