AnalyticDB:Performance tests

Specifications of the test instance

• Engine version: AnalyticDB for PostgreSQL V7.0.

• Minor version: V7.1.0.0.

• Edition: High-availability Edition.

• Node specifications: 4 cores and 32 GB memory.

• Number of compute nodes: 4.

• Node storage capacity: 200 GB.

• Disk type: PL1 Enterprise SSD (ESSD).

• Coordinator node resources: 8 compute units (CUs).

Test dataset

The test data is taken from a snapshot of the Wikipedia language database and contains 5 million rows of data. Download the packages "wiki-articles-500w-aa" to "wiki-articles-500w-az" from softhuafei/wiki-dataset on GitHub. In this example, the test data is successively imported 20 times to create a dataset that contains 0.1 billion rows of data.

• The dataset contains the url, title, and body fields.

• The dataset is in the JSON format.

• The dataset is 2.34 GB in size.

Performance test results

• The amount of time required to create a BM25 index is approximately 63% of the amount of time required to create a GIN(tsvector) index.

• In terms of single-term queries, the queries per second (QPS) performance of a BM25 index is six to seven times the QPS performance of a GIN(tsvector) index.

• In terms of multi-term queries, the QPS performance of a BM25 index is six to eleven times the QPS performance of a GIN(tsvector) index.

• In terms of single-term AND point queries, the QPS performance of a BM25 index is six to nine times the QPS performance of GIN(tsvector) and B-tree indexes.

• In terms of single-term AND range queries, the QPS performance of a BM25 index is one to nine times the QPS performance of GIN(tsvector) and B-tree indexes.

Create an index

Perform a single-term query

Query the top 20 results that contain a term in the body column. Example: body:Gabriel Basso. Among the top 20 results, the average occurrence of the term is 56,685. The following table describes the maximum QPS values and the corresponding response time (RT) values.

Perform a multi-term query

Query the top 20 results that contain one or more terms in the body column. Example: body:handsworth OR body:cern. All terms are extracted from the test dataset. The terms whose occurrence is less than 100 are not included. The number of terms contained in each query conforms to the weight distribution parameter [0.03, 0.15, 0.25, 0.25, 0.15, 0.08, 0.04, 0.03, 0.02]. In this case, 3% of queries contain one term and 15% of queries contain two terms.

Perform a single-term AND point query

Only a single row of data is matched based on the point query conditions.

SELECT * FROM articles ORDER BY body @@@ pgsearch.config('body:"David" AND id:80000000');
SELECT * FROM articles_gin WHERE  body_ts @@ to_tsquery('David') AND id = '80000000';

Perform a single-term AND range query

Ten thousand rows of data are matched and 99.99% of data is filtered out based on the range query conditions.

SELECT * FROM articles WHERE body @@ pgsearch.config('body:"David" AND id:[110000 TO 120000]');
SELECT * FROM articles_gin WHERE  body_ts @@ to_tsquery('David') AND id >= '110000' AND id <= '120000';