How to rebuild a corrupt postgres primary key index

Question

I have a table where I have a row that cannot be found by id. If I go around the PK index, the row is found. If I drop the index, the row is found. If I add a new index, row not found.

d5toqvrtbm8bbu=> SELECT id, created_at FROM widgets WHERE id = 1155301;
   id    |         created_at         
---------+----------------------------
 1155301 | 2014-01-10 02:59:47.856214
(1 row)

d5toqvrtbm8bbu=> CREATE UNIQUE INDEX widgets_pkey ON widgets(id);
CREATE INDEX
d5toqvrtbm8bbu=> SELECT id, created_at FROM widgets WHERE id = 1155301;
 id | created_at 
----+------------
(0 rows)

d5toqvrtbm8bbu=> SELECT id, created_at FROM widgets WHERE id - 1 + 1 = 1155301;
   id    |         created_at         
---------+----------------------------
 1155301 | 2014-01-10 02:59:47.856214
(1 row)

d5toqvrtbm8bbu=> DROP INDEX widgets_pkey;
DROP INDEX
d5toqvrtbm8bbu=> SELECT id, created_at FROM widgets WHERE id = 1155301;
   id    |         created_at         
---------+----------------------------
 1155301 | 2014-01-10 02:59:47.856214
(1 row)

d5toqvrtbm8bbu=> CREATE UNIQUE INDEX widgets_pkey ON widgets(id);
CREATE INDEX
d5toqvrtbm8bbu=> SELECT id, created_at FROM widgets WHERE id = 1155301;
 id | created_at 
----+------------
(0 rows)

This is on postgres 9.3

Any suggestions?

Answer 1

I had a similar problem with my database. I tried REINDEX but it didn't help.

VACUUM FULL widgets;

Totally fixed the problem though.