Re: GLOBAL vs LOCAL temp tables

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I am asking more from a theoretical perspective --- can we say VACUUM
> > regularly or VACUUM FULL are the same in terms of index recovery, or at
> > least as similar as FULL/non-FULL are?
>
> See the comments in nbtree.c's btvacuumcleanup(). FULL is able to
> recycle empty pages faster than non-FULL, since it knows there can be no
> other transactions with open indexscans. So a freshly emptied index
> page can be added to the FSM freelist immediately, whereas in the
> non-FULL case it will need to wait till the next VACUUM (possibly even
> longer if you have long-running transactions). Also, VACUUM FULL will
> truncate off any free pages at the end of the index, though I doubt this
> is very effective since it won't move data across pages.
>
> As I commented to Alvaro, I don't really see a need for an intermediate
> level of cleanup between what VACUUM FULL does now and REINDEX. Moving
> data in an index is slow, would certainly require exclusive lock, and
> helps to degrade the physical ordering of the index. REINDEX gives you
> a nice new freshly-sorted index and would probably be what you'd want
> if you were going to lock down the index for a long period anyway.

Hmm...could VACUUM FULL then take an additional option, REINDEX (not
sure if calling it that will be possible if it'll cause ambiguities in
the parser), so that you don't have to do REINDEX separately? The
only reason I mention this is that VACUUM FULL doesn't require an
argument and will operate on an entire database in that situation, and
doesn't require that you be in standalone mode, whereas REINDEX
DATABASE must operate in standalone mode and other REINDEX modes
require a specific table or index as arguments. At least, that is the
case under 7.3.x. Ignore this if REINDEX can reindex a database
without being in standalone mode in 7.4...

The situations where you'd do a VACUUM FULL are the same situations in
which you're likely to want to REINDEX as well, so it seems reasonable
to make a REINDEX operation an option to VACUUM FULL, especially since
VACUUM FULL acquires an exclusive lock on the table (so the indexes
associated with that table won't be in use during the VACUUM -- a
perfect time to recreate them). In fact, since VACUUM FULL is
scanning the entire heap, rebuilding the indexes while the VACUUM FULL
scan is going seems like it would be a significant performance win
over doing separate VACUUM FULLs and REINDEXes.

On a slightly different note, from what I've seen of the source,
REINDEX TABLE acquires an access exclusive lock on the table, thus
preventing concurrent SELECTs against the table while REINDEX TABLE is
running. But shouldn't those SELECTs be allowed until the newly-built
index is ready for operation (at which point the regular exclusive
lock on the table could be upgraded to a shared exclusive lock)? If
the DBA doesn't want anyone to be doing SELECTs against the table he
can lock the table in access exclusive mode himself and then issue the
REINDEX TABLE from within that same transaction, right? I mean, if
REINDEX TABLE is supposed to be a regularly scheduled maintenance
command, then shouldn't its impact on normal operations be as minimal
as possible?

Or am I missing something fundamentally important? Searching the
archives didn't prove very enlightening...

--
Kevin Brown kevin(at)sysexperts(dot)com