2009/9/29 Sam Mason <sam@xxxxxxxxxxxxx>: > On Tue, Sep 29, 2009 at 02:25:52PM +0200, Brian Modra wrote: >> 2009/9/29 tomrevam <tomer@xxxxxxxxx>: >> > My DB is auto-vacuuming all the time. The specific table I'm talking about >> > gets vacuumed at least every 2 hours (usually a little more frequently than >> > that). >> > Deletes are happening on the table at about the same rate as inserts (there >> > are also some updates). >> >> The index quite likely is in a poor state. > > Really? Plain vacuum should allow things to reach a steady state after > a while, doing a large delete will put things out of kilter, but that > doesn't sound to be the case here. Vacuum full can also cause things to > go amiss, but if it's just regular vacuums then things should be OK. If there are a lot of deletes, then likely the index parameters are not the best. ANALYSE yourtable; Then, reindex (or create new index followed by drop index and rename - if you want to leave the index online. > What do you get out of vacuum analyse verbose? for this table? > >> You could try this: >> >> analyse .... >> create index ... (same parameters as existing index) >> delete the old index. >> rename the new index to the same name as the old one >> repeat this for all indexes. > > Why not just do: > > REINDEX TABLE yourbigtable; > > No need to worry about rebuilding foreign key constraints or anything > like that then. Thats OK if the table can be taken offline. REINDEX locks the index while in progress. > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
