CODEWITHSUNDEEP
postgresqlindexingcorruptwal-e
Paul Zeng
Paul Zeng

Reputation: 11

Index corrupted after restore PostgreSQL database

We use WAL-E to backup and restore a PostgreSQL database (version 9.5.13), We found that a few indexes (not all) were broken after restored, most of them are related to String type fields.

For example, using analyze explain we can see the following query hits an existing index which relates to foo_name field, but no rows returns.

select * from foo where foo_name = 'xyz';

If we use like in the query, it is able to return the expected row because it performs full-table scan.

select * from foo where foo_name like '%xyz%';

The solution is to reindex the broken indexes. After we reindex it, the first query also returns the expected result.

Does anyone meet the same issue before? Does anyone have any idea on the root cause?

Upvotes: 1

Views: 687

Answers (0)

Related Questions