FAQ-PolarDB(PolarDB)-阿里云帮助中心

This topic covers common issues when using the vectorized engine and its In-Memory Columnar Index (IMCI) feature in PolarDB for PostgreSQL, along with solutions.

Why do I get `ERROR: access method "csi" does not exist` when creating an IMCI?

The polar_csi extension is not installed. Run the following statement to install it:

CREATE EXTENSION polar_csi;

Why does IMCI creation take so long?

Adjust the relevant parameter values to speed up index creation. For details, see Performance optimization: accelerate the index creation.

Why does IMCI creation block writes to the original table?

Use one of the following approaches:

Cancel the index creation: Run pg_cancel_backend or pg_terminate_backend to stop the index build and unblock your workload. For details, see Cancel the index creation.
Create the index concurrently: Use CREATE INDEX CONCURRENTLY to build the IMCI without blocking writes. For details, see Create an IMCI.

Why does my IMCI not return the latest data?

By default, the vectorized engine uses eventual consistency, so queries may return slightly stale data. Work through the following checks:

Confirm the query is using the IMCI. For details, see Check whether a SQL statement uses an IMCI.
If the query uses the IMCI, the engine may be lagging behind on synchronizing row store data. Tune the related parameters to improve sync performance. For details, see Real-time performance of an IMCI.
Enable strong consistency read to ensure the IMCI is fully synchronized before results are returned. For details, see Query consistency level.

Enabling strong consistency can reduce query performance under heavy write loads. See Why does query performance decrease after enabling strong consistency? for how to handle this trade-off.

Why does query performance decrease after enabling strong consistency?

Strong consistency requires the IMCI to be fully synchronized with the row store before returning results. If the row store table has heavy write loads and sync cannot keep up, queries wait longer.

Confirm the query is using the IMCI. For details, see Check whether a SQL statement uses an IMCI.
Tune the synchronization parameters to improve sync throughput. For details, see Real-time performance of an IMCI.
If write pressure is too high and sync performance still cannot meet your requirements, switch back to eventual consistency:
```
SET polar_csi.forward_replay_wait = off;
```

Why can't I use the vectorized engine and IMCI to accelerate my queries?

The query optimizer automatically skips IMCI acceleration when it estimates the query cost is too low to benefit—this is expected for simple queries. If you believe your query should benefit from IMCI acceleration, work through the following checks:

Check whether the IMCI is enabled for your PolarDB for PostgreSQL version. For details, see Enable the IMCI feature.
Check that an IMCI exists for the table. For details, see Create an IMCI.
Check that all columns in the query are included in the IMCI. The IMCI can only accelerate a query if every column referenced by that query is part of the index. For example, if you created the index with CREATE INDEX csi_idx_t ON t USING CSI(id,name), the query SELECT id,name,age FROM t cannot use the IMCI because age is not included.
Check that the vectorized engine is enabled for queries. Run SHOW polar_csi.enable_query. If the result is off, the engine is disabled. For details on how to enable it, see Parameter configuration.
Check the query cost threshold. The row store execution engine performs better for simple, low-cost queries. Run EXPLAIN or EXPLAIN ANALYZE to inspect the query plan and estimated cost. To force the query to use the IMCI regardless of cost, set polar_csi.cost_threshold to 0. For details, see Parameter configuration.
Enable debug logging if the issue persists:
```
SET client_min_messages = debug5;
```

How does the vectorized engine relate to the ePQ feature?

They use different technologies and serve different purposes.

The Elastic Parallel Query (ePQ) feature of PolarDB for PostgreSQL is built on the cloud-native one-primary-multiple-read-only-node architecture. It distributes a single query across all read-only nodes for execution, so the ePQ feature of PolarDB for PostgreSQL has stronger scale out capabilities than native PostgreSQL, which is limited to the computing resources of one node.

The IMCI of PolarDB for PostgreSQL and ePQ are not the same technology. The IMCI improves computing efficiency on the same hardware, while ePQ aims to fully utilize all resources of the database cluster. The two features complement each other.

Why do I get ERROR: access method "csi" does not exist when creating an IMCI?