On Monday, January 14, 2013, T. E. Lawrence wrote:
RESOLVED
--
Dear all,
Thank you for your great help and multiple advices.
I discovered the problem and I have to say that it is very stupid and strange.
Here is what happened.
...
So I decided to try the whole thing properly from the beginning. Dropped the index, did again VACUUM ANALYZE and tried the queries, in order to measure them without and with index. Surprise! - the slow query was blazing fast. The previous indexes (not the dropped partial index) were properly used. All was fine.
Which makes me think that, as we grew the database more than 250 times in size over a 2-3 months period, relying on autovacuum (some tables grew from 200k to 50m records, other from 1m to 500m records), the autovacuum has either let us down or something has happen to the ANALYZE.
What do pg_stat_user_tables tell you about last_vacuum, last_autovacuum, last_analyze, last_autoanalyze ?
Is the autovacuum 100% reliable in relation to VACUUM ANALYZE?
No. For example, if you constantly do things that need an access exclusive lock, then autovac will keep getting interrupted and never finish.
Cheers,
Jeff