Hi,
I had a strange problem this morning - I started a long-running UPDATE
on a heavily indexed table with about 8m rows last night to test a
trigger-based queue (PgQ):
UPDATE eintrag SET mtime=mtime;
This took about 12 hours, during this time our nightly REINDEX script
ran (successfully). After that however, queries completely ignored some
indexes, including newly created ones. I ran "vacuum analyze" on the
table several times, I set "enable_seqscan=false" and it still used
sequential scans for queries like
SELECT * FROM eintrag WHERE h_id = 1234;
while I had a freshly created (just before the query and with an ANALYZE
afterwards) index on the table:
"e_h" btree (h_id)
I also had an older index
"e_opt_rejects" btree (h_id, a_id)
which was explicitly reindexed by the above mentioned nightly script and
would have been used normally.
# EXPLAIN SELECT * FROM eintrag WHERE h_id>5227;
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on eintrag (cost=0.00..1579036.38 rows=7135194 width=463)
Filter: (h_id > 5227)
(2 rows)
# EXPLAIN SELECT * FROM eintrag WHERE h_id=5227;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on eintrag (cost=0.00..1579036.38 rows=1642 width=463)
Filter: (h_id = 5227)
(2 rows)
# EXPLAIN SELECT * FROM eintrag WHERE mtime = 'yesterday';
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using e_mtime on eintrag (cost=0.00..511.80 rows=477
width=528)
Index Cond: (mtime = '2009-03-07 00:00:00'::timestamp without time zone)
(2 rows)
(the above were with enable_seqscan=true obviously, with it set to false
the cost was artifically high, 1000.... as usually, but I didn't save
the output). Other queries, e.g. using an index over "mtime" still
worked fine. Autovacuum was enabled on the table.
The odd thing is, the problem completely disappeared after I restarted
Postgres. I'm sorry that I cannot provide more details, but I have
absolutely no idea what could have happened here and which system tables
I could have checked for inconsistencies etc. My guess is that the index
"e_opt_rejects" was somehow corrupted during the REINDEX and the some of
the information kept in memory and used by the planner was wrong (the
row estimates for the SELECTs above were quite accurate though).
Regards,
Marinos
