PolarDB:Use Elastic Parallel Query to accelerate index creation

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 and flushes them all at once when the buffer is full, reducing frequent I/O scheduling overhead.

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL (Compatible with Oracle) cluster

• Access to run DDL statements on the target table

Procedure

The following example creates a table named test and builds an index using EPQ.

1. Create the test table.

CREATE TABLE test(id int, id2 int);

Verify the table schema:

\d test

Expected output:

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

2. Enable EPQ-accelerated index creation.

SET polar_px_enable_btbuild=on;

Verify the setting:

SHOW polar_px_enable_btbuild;

Expected output:

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

3. Create the index.

Add WITH (px_build=on) to the CREATE INDEX statement to use EPQ for this build:

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

polar_px_enable_btbuild=on alone does not activate EPQ for a specific index build. You must include WITH (px_build=on) in the CREATE INDEX statement. Without it, PolarDB uses the native (serial) index creation method, and the resulting index shows no px_build annotation.

4. Verify the index was created with EPQ.

\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 confirms that EPQ was used.