How to Do a Simple Index Consistency Check

Indexes are supposed to accurately reflect what’s in the base table. Newer versions of YugabyteDB include yb_index_check() to validate this, but if you’re on an older release, this simple method works well as a fallback.

How it works

This method asks a simple question:

● How many non-null values exist in the table?
● How many can be retrieved through the index?

If the index is healthy, those counts should match.

🔑 Key Insight

Compare a COUNT(indexed_column) using a forced Seq Scan with the same count using a forced Index Only Scan.

If the counts differ, that’s a red flag.

On newer versions, use yb_index_check() for full index validation.

👉 Catching Index Inconsistencies in YugabyteDB

Example

				
					CREATE TABLE orders (
    order_id     BIGINT PRIMARY KEY,
    customer_id  BIGINT,
    status       TEXT
);

CREATE INDEX orders_customer_id_idx ON orders(customer_id);

Step 1: Verify Seq Scan

				
					EXPLAIN
/*+ SeqScan(orders) */
SELECT COUNT(customer_id)
FROM orders;

Step 2: Verify Index Only Scan

				
					EXPLAIN
/*+ IndexOnlyScan(orders orders_customer_id_idx) */
SELECT COUNT(customer_id)
FROM orders;

🚨 Critical

Always run EXPLAIN first. If the expected scan type is not used, the comparison is invalid.

Step 3: Run and compare

				
					/*+ SeqScan(orders) */
SELECT COUNT(customer_id)
FROM orders;

				
					/*+ IndexOnlyScan(orders orders_customer_id_idx) */
SELECT COUNT(customer_id)
FROM orders;

● Same count → good sign
● Different count → potential inconsistency

Why use `COUNT(column)`?

Use:

				
					SELECT COUNT(customer_id) FROM orders;

Not:

				
					SELECT COUNT(*) FROM orders;

Because COUNT(column) only counts non-null values, which better aligns with how indexes behave.

Quick comparison

Method	Strength	Limitation
`yb_index_check()`	Full validation	Not available in older versions
Seq vs Index count	Simple and fast	Only a sanity check

What if counts don’t match?

That’s a red flag. A common next step is to rebuild the index:

				
					DROP INDEX orders_customer_id_idx;
CREATE INDEX orders_customer_id_idx ON orders(customer_id);

(In production, consider creating a new index first, then dropping the old one.)

Final Takeaway

Indexes should always reflect the base table, and even without yb_index_check(), you still have a practical way to sanity check them.

This method won’t catch every edge case, but it’s a fast and effective way to spot potential inconsistencies on older YugabyteDB versions.

● Compare Seq Scan vs Index Only Scan counts
● Use when yb_index_check() isn’t available
● Don’t skip EXPLAIN

Have Fun!

We went to meet our daughter’s dog, Maple, over the weekend and got to meet her for the first time. We brought her a frisbee toy. First throw… straight over the neighbor’s fence! Maple was not impressed.