On Thursday 13. of May 2010 17:24:47 Tom Lane wrote: > Rumko <rum...@gmail.com> writes: > > Tom Lane wrote: > >> There's something extremely wacko about that vacuum output. > > > > Regarding storage paramaters, you mean ALTER TABLE x SET STORAGE...? Then > > no. > > No, I was wondering about ALTER TABLE ... SET (fillfactor = n). > It would be worth checking to see if you get a nonnull result from > select reloptions from pg_class where relname = 'pg_toast_1066371';
Returns NULL. > The funny behavior would be partially explained if the toast table has a > ridiculously small fillfactor --- in particular that would explain > VACUUM claiming there's no free space, as well as the bloat caused by > having only one useful toast row per page. > > There's still the question of why it's toasting such short values at > all, but I think I see that: your table rows contain 500 non-toastable > columns, either bigints or timestamps, each of which requires 8 bytes. > So assuming those are all non-null, that's 4000 unremovable bytes right > there. The toast code then goes nuts trying to push out all the > toastable columns to bring the tuple down to target size; it's going to > push columns to toast that ordinarily wouldn't get pushed. This does not bother me, the amount in the toast tables is miniscule and comes up to ~275MB at the end and as far as performance goes, there were no noticable problems (it's quite fast). > > You might want to think about collapsing all those standalone bigint > columns into an array. The current design is not final yet, but for now it has proven (with the exception of the 2 tables that have giant toast tables) to be the most useful (administration vs. speed vs. ease of use). There will be more experimentation. > > Maybe the toast heuristics should be modified to cope a bit more > gracefully with a case like this. Pushing out a relatively small column > in order to get down from 4200 to 4100 bytes doesn't seem like a win. > OTOH, this is by no stretch of the imagination a good schema design, so > I'm not sure how excited people will be about making it perform better. > > regards, tom lane As far as I'm concerned, the TOAST table itself does not bother me even if I have a few bytes per row there, only the part where VACUUM claims no free space even though pages are more empty than not. From what I can tell, the problem seems to be in the fsm? Used pg_freespace from the pg_freespacemap module and it claims that there are no pages in the toast table that have any free space left (on the other hand vacuum shows that each page has a max of 122 bytes of data ... so there should still be ~8000 bytes of free space left, right?). I tested this on a table that I already ran VACUUM FULL and CLUSTER on it and on a table that I didn't, but for both, pg_freespace claimed that all pages were full for the toast table. -- Regards, Rumko
signature.asc
Description: This is a digitally signed message part.
