Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm running PG 8.4

I had a table with about 20K records. Any query accessing this table with more than 1 record (e.g. joins) would be very very slow. Even a count would take like 20 seconds.

Stats showed 5 million live_tuples.

I tried vacuum, vacuum analyze, and in the end, vacuum full. Nothing changes in terms of speed. And stats still showed the same thing.

I ended up creating a new table, insert all records in the new table and drop/rename the old one.

The new table now runs lightning fast.

Any idea of:

1) What would have been the more correct handling? I'm looking for a better way to do this as one of the side effect is that the views which were pointing to the table got modified by the "alter table rename to" statement and unbeknownst to me, the views were pointing to the old table for 2 days!

2) Why would that happen in the first place?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

1) I would have tried cluster followed by analyze. My only hesitation is I am not 100% sure what happened here. Is it possible there was some index corruption as well? Reindex might have helped? Given that the stats entries were way off, is it possible something was corrupt elsewhere regarding the relation's OID?

2) I have no idea. I have never seen anything like that before. I would need a lot more diagnostic information even to hazard a guess. If it happens again it would be worth knowing the physical size of the table, for example.

Edit: Remembering that 8.4 still had max_fsm_pages, if that was set too low between vacuums that could cause this sort of table bloat.

Edit2: Vacuum full, however should have been able to handle bloat due to max_fsm_pages, at least it does in my experience, suggesting again to me that something deeper was wrong.

share|improve this answer
    
Well I did not drop the old table yet. I just ran select count and it took 2m59s to run to get me 22,484 rows! n_live_tup is 3,591,992. pg_total_relation_size reports 3,800Mb. I realize that answering the "why" will be pretty hard without too much info! –  ETL Feb 25 '13 at 3:17
    
could you try something like this:SELECT pg_size_pretty(pg_total_relation_size('probem_table')); –  Chris Travers Feb 25 '13 at 3:19
    
3,800Mb - I pressed enter by mistake while I was typing earlier in the comment. –  ETL Feb 25 '13 at 3:21
    
edited to incloude a possible answer. max_fsm_pages might be set too low causing a shortage of mapped free space. –  Chris Travers Feb 25 '13 at 3:24
    
Just for the record, as I had kept aside (only renamed) the problematic table, I tried the cluster command on it and after running it, the table behave correctly. The select count on it runs normally, stats make sense, etc. –  ETL Feb 26 '13 at 16:39

I think I finally found an answer for 1) What would have been the more correct handling?

Tip: Neither form of VACUUM is entirely satisfactory when a table contains large numbers of dead row versions as a result of massive update or delete activity. If you have such a table and you need to reclaim the excess disk space it occupies, the best way is to use CLUSTER or one of the table-rewriting variants of ALTER TABLE. These commands rewrite an entire new copy of the table and build new indexes for it. Like VACUUM FULL, they require exclusive lock. Note that they also temporarily use extra disk space, since the old copies of the table and indexes can't be released until the new ones are complete. In the worst case where your disk is nearly full, VACUUM FULL may be the only workable alternative. 1

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.