From: Michael Paquier <michael@paquier.xyz>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Move pg_attribute.attcompression to earlier in struct for reduced size?
Date: Wed, 2 Jun 2021 11:32:51 +0900
Message-ID: <YLbt02A+IDnFhwIp@paquier.xyz> (raw)
In-Reply-To: <1885750.1622146678@sss.pgh.pa.us>
References: <202105271934.mneyybkyeo66@alvherre.pgsql>
<1885750.1622146678@sss.pgh.pa.us>
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: michael@paquier.xyz, tgl@sss.pgh.pa.us, alvherre@alvh.no-ip.org, robertmhaas@gmail.com, dilipbalaut@gmail.com, andres@anarazel.de
Subject: Re: Move pg_attribute.attcompression to earlier in struct for reduced size?
In-Reply-To: <YLbt02A+IDnFhwIp@paquier.xyz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox