Re: Thoughts on maintaining 7.3 - Mailing list pgsql-hackers

Alvaro Herrera wrote:
> > Yeah, I think that's exactly the important point.  These days there's
> > not a lot of reason to do VACUUM FULL unless you have a major amount of
> > restructuring to do.  I would once have favored maintaining two code
> > paths with two strategies, but now I doubt it's worth the trouble.
> > (Or I should say, we have two code paths, the other being lazy VACUUM
> > --- do we need three?)
> 
> There are two points that could be made here:
> 
> 1. We do not want users having to think too hard about what kind of
> VACUUM they want.  This probably botches Bruce's idea of an additional
> VACUUM DATA command.
> 
> 2. We do not want to expose the VACUUM command family at all.  The
> decisions about what code paths should be taken are best left to the
> backend-integrated vacuum daemon, which has probably much better
> information than users.

Agreed.  We need to head in a direction where vacuum is automatic.  I
guess the question is whether an automatic method would ever user VACUUM
DATA?

I just did a simple test.  I did:test=> CREATE TABLE test (x INT, y TEXT);CREATE TABLEtest=> INSERT INTO test VALUES
(1,'lk;jasdflkjlkjawsiopfjqwerfokjasdflkj');INSERT 17147 1test=> INSERT INTO test SELECT * FROM test;{ repeat until 65k
rowsare inserted, so there are 131k rows}test=> INSERT INTO test SELECT 2, y FROM test;INSERT 0 131072test=> DELETE
FROMtest WHERE x=1;DELETE 131072test=> \timingTiming is on.test=> VACUUM FULL;VACUUMTime: 4661.82 mstest=> INSERT INTO
testSELECT 3, y FROM test;INSERT 0 131072Time: 7925.57 mstest=> CREATE INDEX i ON test(x);CREATE INDEXTime: 3337.96
mstest=>DELETE FROM test WHERE x=2;DELETE 131072Time: 3204.18 mstest=> VACUUM FULL;VACUUMTime: 10523.69 mstest=>
REINDEXTABLE test;REINDEXTime: 2193.14 ms
 


Now, as I understand it, this is the worst-case for VACUUM FULL.  What
we have here is 4661.82 for VACUUM FULL without an index, and 10523.69
for VACUUM FULL with an index, and REINDEX takes 2193.14.  If we assume
VACUUM FULL with REINDEX will equal the time of VACUUM without the index
plus the REINDEX time, we have 4661.82 + 2193.14, or 6854.96 vs.
10523.69, so clearly VACUUM REINDEX is a win for this case.  What I
don't know is what percentage of a table has to be expired for REINDEX
to be a win.  I assume if only one row is expired, you get 4661.82 +
2193.14 vs. just 4661.82, roughly.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073