Why openGauss Misses Index‑Only Scan and How USTORE Fixes It

Background

OpenGauss 2.0 and 3.0 inherit PostgreSQL 9.2.4’s optimizer. A frequent‑execution query that only needs columns covered by an index should be able to use an Index‑Only Scan to avoid heap (back‑table) accesses.

Problem

In openGauss 2.0 the INCLUDE clause for covering indexes is not supported, and the same limitation appears in openGauss 3.0 (different error wording). Consequently, creating a simple index on (id, val) does not lead the planner to choose an Index‑Only Scan; the plan still requires a heap fetch.

Experiment on openGauss 2.0/3.0

drop table test_covering;</code>
<code>create table test_covering (id serial, name text, val int);</code>
<code>create index idx_test_covering on test_covering(id, val);</code>
<code>insert into test_covering(name, val)
  select 'test' || generate_series(1,10000), (random()*100)::int % 100;</code>
<code>analyze test_covering;</code>
<code>explain (analyze true, buffers true) 
  select val from test_covering where id >= 10 and id < 100;</code>
<code>vacuum test_covering;

The execution plan never switches to an Index‑Only Scan, even after a manual VACUUM. The planner treats the query as needing a heap fetch.

Visibility Map Theory

PostgreSQL records page‑level visibility in a visibility map (VM) . An Index‑Only Scan is possible only when the VM marks all pages referenced by the query as “all‑visible”. If the VM is stale, the planner must fetch the heap rows to verify tuple visibility.

Verification on PostgreSQL 11

Running the same script on PostgreSQL 11 produces an Index‑Only Scan after a VACUUM. Before vacuum the plan shows HEAP FETCHES = 180; after vacuum the fetch count drops to 0 and execution time improves from ~0.203 ms to ~0.037 ms.

Effect of VACUUM on openGauss

In openGauss 3.0 a VACUUM does not immediately update the VM, so the planner continues to avoid Index‑Only Scans. After a delay the VM is refreshed, the plan switches, and the query benefits from the covering index.

Using USTORE to Enable Covering Indexes

openGauss 3.0 supports covering indexes on tables created with the USTORE storage engine, which bypasses the VM requirement.

drop table test_covering;</code>
<code>create table test_covering (
  id serial,
  name text,
  val int) with (STORAGE_TYPE=USTORE);</code>
<code>create index idx_test_covering on test_covering using ubtree(id) include (val);</code>
<code>insert into test_covering(name, val)
  select 'test' || generate_series(1,10000), (random()*100)::int % 100;</code>
<code>explain (analyze true, buffers true)
  select val from test_covering where id >= 10 and id < 100;</code>
<code>analyze test_covering;</code>
<code>update test_covering set val = val + 1;</code>
<code>explain (analyze true, buffers true)
  select val from test_covering where id >= 10 and id < 100;

The planner selects an Index‑Only Scan both before and after the update, confirming that USTORE tables do not depend on the VM for covering indexes.

Takeaways

openGauss inherits PostgreSQL’s visibility‑map‑based decision for Index‑Only Scans; a stale VM forces back‑table accesses.

Unlike PostgreSQL, a manual VACUUM in openGauss may not instantly refresh the VM, so the optimizer can continue to avoid Index‑Only Scans.

The USTORE storage engine provides a practical workaround: covering indexes created with INCLUDE (or using ubtree ... include) are usable without VM support.

When migrating workloads that rely on Index‑Only Scans, test the VM freshness and consider USTORE tables if immediate performance gains are required.