1

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?

Paul Zeng
  • 11
  • 2
  • 1
    Something sounds fishy here. An index should _not_ have any bearing on the logical result of a SQL query, at least not in Postgres. – Tim Biegeleisen Jun 25 '18 at 11:28
  • What is "WAL-E"? How did you restore your database? If you have a problem with the indices, why not rebuild all of them? – Nico Haase Jun 25 '18 at 11:31
  • @a_horse_with_no_name the version is 9.5.13 – Paul Zeng Jun 25 '18 at 11:39
  • @NicoHaase see the link for wal-e: https://github.com/wal-e/wal-e . – Paul Zeng Jun 25 '18 at 11:41
  • 1
    Indexes are not portable across systems, because collations aren't, unless using `collate "C"` or ICU. Are you restoring into a different OS? – Daniel Vérité Jun 25 '18 at 13:45
  • @DanielVérité Sometimes it is enough to [restore to a different version of the same operating system to get index corruption](https://www.postgresql.org/message-id/CB4D1C6BAA80CF146CB0D4F2%40eje.credativ.lan). – Laurenz Albe Jun 25 '18 at 15:54

0 Answers0