Re: SELECT * FROM <table> LIMIT 1; is really slow

On Wed, 26 May 2004 18:17:55 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>(Some days I think we should dump
>VACUUM FULL, because it's optimized for a case that's no longer very
>interesting...)

So we still have to stick with VACUUM FULL for some time, right?

The next set of compatibility breakers I'm currently working on requires
a change in VACUUM FULL behaviour. I would only move tuples that are
visible to all running transactions. OTOH I wouldn't stop at the first
unmovable tuple.

With
X active tuple
. free space or dead tuple
y new tuple, not yet visible to a running transaction
z deleted tuple, still visible to a running transaction

the current implementation transforms this relation

XXXX.... XXXX.... XXXX.... XXXX.... XXyX.... XXXX.... XzXX.... XXXX....

into

XXXXXXXX XXXXXzXX XXXXXXXX XXXXXXyX

The new implementation would leave it as

XXXXXXXX XXXXXXXX XXXXXXXX XXXXXX.. ..y..... ........ .z......

If there are concurrent long-running transactions, the new VACUUM FULL
wouldn't truncate the relation as aggressively as it does now. It could
leave the relation with lots of free space near the end. This was
absolutely unacceptable at the time when VACUUM FULL was designed. But
now we could use lazy VACUUM as an excuse for VACUUM FULL not working so
hard. After the transaction still seeing z terminates, VACUUM (without
FULL) can truncate the relation to

XXXXXXXX XXXXXXXX XXXXXXXX XXXXXX.. ..y.....

and when y is updated the new version will be stored in a lower block
and plain VACUUM can truncate the relation again:

XXXXXXXX XXXXXXXX XXXXXXXX XXXXXXY.

AFAICS this would make vacuum.c much simpler (no more chain moves).
Clearly this change alone doesn't have any merit. But would such a
patch have any chance of being accepted, if it facilitates improvements
in other areas?

Servus
Manfred