Analyze Table made performance worse! -- help!
Posted on 2008-06-16
I ave a relatively large database that had some minor performance issues. I had been going through and Analyzing tables and computing statistics. This improved overall efficiency and the number of buffer gets went down dramatically.
That is, until I analyzed a table that was really out of wack. The table stats claimed to have 400,000 rows, but a select count(*) showed about a million. So I analyzed the table and computed statistics.
When I did that, a query that was only using about 30 buffer gets per transaction now uses over 1,000 buffer gets for the same transaction.
The explain plan shows that the index is no longer being used. Instead it is doing a full table scan.
What's up with that? How do I fix it?