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?
idcolumn and is there any custom code in your postgres installation?\d+for the tablewidgets, andexplainfor both with-index and without-index plans. Isidaninteger/bigint? Or something else? What's the exactSELECT version()? Can you post a self-contained database dump that demonstrates the problem.