>> Can you give an "explain (analyze, buffers)" for each query? Maybe you have a corrupted index, and one query uses the index and the other does not.
> > Index Scan using idx_attend_00 on attend (cost=0.29..627.20 rows=172 width=12) (actual time=5.158..10.179 rows=5 loops=1) > Index Cond: (sid = 325) > Filter: (lid = ANY ('{ABF0010,ABF0010,ABF0010,ABF0010,ABF0010 ... ABF0060,ABF0060,ABF0060,ABF0060}'::text[])) > Rows Removed by Filter: 414
...
> > Index Scan using index_attend_on_sid_and_lid on attend (cost=0.42..36.32 rows=3 width=12) (actual time=0.011..0.034 rows=6 loops=1) > Index Cond: ((sid = 325) AND (lid = ANY ('{ABF0010,ABF0020,ABF0030,ABF0040,ABF0050,ABF0060}'::text[]))) > Buffers: shared hit=24
Is this result aims idx_attend_00 corrupted? How to fix it? What countermeasure do I it?
Yes, almost certainly. You can fix it by rebuilding the index ("REINDEX INDEX idx_attend_00"). Whether this will completely fix the problem depends on what caused it. There could be a wider corruption issue of which this is only a symptom.
If you would like to preserve the ability to investigate the root cause, you should make a full file-level backup of the database *before* doing the re-index.
What type of index is it? (I'm now guessing btree, but maybe not)? Is there a defined time window during which you know the corruption occurred? If so, do you still have the server logs from that time window? The WAL logs?
Do you know if the sometimes-missing tuple actually belongs in the table or not? It could be that the row was marked deleted from the table, scrubbed from the index, and then inappropriately got "revived" like a zombie in the table, so that the "corrupt" index is correct and it is the table that is wrong.
And of course, if you are running with fsync=off or full_page_writes=off, don't do that.
