1

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?

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
5
  • 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
    Commented Feb 25, 2013 at 3:17
  • could you try something like this:SELECT pg_size_pretty(pg_total_relation_size('probem_table'));
    – Chris Travers
    Commented Feb 25, 2013 at 3:19
  • 3,800Mb - I pressed enter by mistake while I was typing earlier in the comment.
    – ETL
    Commented Feb 25, 2013 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
    Commented Feb 25, 2013 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
    Commented Feb 26, 2013 at 16:39
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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