Skip to content

2.29.0.0-b154

@gauravk-in gauravk-in tagged this 07 Nov 15:40
Summary:
##Postgres Behavior
In PostgreSQL, when an index is created (either concurrently or
non-concurrently), `reltuples` for the new index and the base table are updated
to the actual number of rows in them. By doing this, we avoid having to run
ANALYZE after creating an index to update the reltuples for the index.

## Existing Behavior
Before this change, YugabyteDB had different inconsistent behavior for concurrent and
non-concurrent index creation.
* For concurrent, base table reltuples were not updated and index reltuples were set to 0.
* For non-concurrent, base table reltuples were not updated and index reltuples were set to
actual number of rows in the index.

## Problem
This issue did not affect the old cost model because index reltuples were not
used to estimate costs.

The new CBO enabled using `yb_enable_cbo` relies on index reltuples to
estimate the cost of index lookup. After concurrent index creation, if the index
reltuples are set to 0, the index scan costs are under-estimated leading to bad
plan choices. The work around is to call ANALYZE after index creation.

## New Behavior
With this change, we introduce a new runtime gflag
`ysql_yb_enable_update_reltuples_after_create_index`.

When this flag is `OFF`, we maintain the current behavior described above
to prevent plan regressions.

When the flag is `ON`, base table reltuples are updated and index reltuples
are set to the actual number of rows in the table and the index respectively
after CREATE INDEX. This is the same behavior as Postgres.

Currently, this flag is set to `OFF` by default, but in a future diff it will be
enabled automatically if the new CBO is enabled. Although this feature
can be enabled for the old cost model, we do not recommend this as this
can lead to plan regressions.

## Upgrade/Rollback Safety
If the concurrent index backfill is performed during a rolling upgrade, the
reltuples may not be correct. Concurrent Index backfill is performed in a
distributed manner, where each tserver is responsible to backfill the index for
the rows in the tablets hosted by this tserver. Each tserver must report the
number of rows it backfilled in the index. If a tserver is on an older version
before this change, it may not report this value. This would lead to the
reltuples being less than the actual number of rows that were backfilled.

`IndexInfoPB.num_rows_backfilled_in_index` is the only addition to the
permanent persistent data structure. This is only used during an active
backfill process or immediately after this, so this should not pose any risk
during rollback.

## Additional Change :
In case of unexpected error while getting the progress of the index backfill
for `pg_stat_progress_create_index`, the `tuples_done` maybe reported as `-1`,
instead of `0` as before.
Jira: DB-14623

Test Plan: ./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressIndex'

Reviewers: mihnea, mtakahara, jason, smishra

Reviewed By: jason

Subscribers: asrivastava, hsunder, ybase, bkolagani, fizaa, yql

Differential Revision: https://phorge.dev.yugabyte.com/D46332
Assets 2
Loading