On Thu, May 27, 2021 at 04:17:58PM -0400, Tom Lane wrote:
> BTW, perhaps I should clarify my goal here: it's to cut off expending
> further effort on this feature during v14.
No disagreement here.
> If we can decide that the
> existing performance situation is acceptable, I'm content with that
> decision. But if we're to start designing new user-visible behavior to
> satisfy performance objections, then I'd prefer to remove this VACUUM
> behavior altogether for now.
After putting a PGDATA on a tmpfs, I have looked at the run time of
VACUUM FULL with tables full of text columns, with that:
CREATE OR REPLACE FUNCTION create_cols(tabname text, num_cols int)
RETURNS VOID AS
$func$
DECLARE
query text;
BEGIN
query := 'CREATE TABLE ' || tabname || ' (';
FOR i IN 1..num_cols LOOP
query := query || 'a_' || i::text || ' text NOT NULL DEFAULT ' || i::text;
IF i != num_cols THEN
query := query || ', ';
END IF;
END LOOP;
query := query || ')';
EXECUTE format(query);
query := 'INSERT INTO ' || tabname || ' SELECT FROM generate_series(1,1000000)';
EXECUTE format(query);
END
$func$ LANGUAGE plpgsql;
After 12 runs of VACUUM FULL on my laptop, I have removed the two
highest and the two lowest to remove some noise, and did an average of
the rest:
- HEAD, 100 text columns: 5720ms
- REL_13_STABLE, 100 text columns: 4308ms
- HEAD, 200 text columns: 10020ms
- REL_13_STABLE, 200 text columns: 8319ms
So yes, that looks much visible to me, and an argument in favor of the
removal of the forced recompression on HEAD when rewriting tuples.
--
Michael