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
