ALTER TABLE uses a bistate but not for toast tables
Justin Pryzby <pryzby@telsasoft.com>
From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@postgresql.org
Date: 2022-06-22T14:38:41Z
Lists: pgsql-hackers
Attachments
- 0001-WIP-use-BulkInsertState-for-toast-tuples-too.patch (text/x-diff)
ATRewriteTable() calls table_tuple_insert() with a bistate, to avoid clobbering and polluting the buffers. But heap_insert() then calls heap_prepare_insert() > heap_toast_insert_or_update > toast_tuple_externalize > toast_save_datum > heap_insert(toastrel, toasttup, mycid, options, NULL /* without bistate:( */); I came up with this patch. I'm not sure but maybe it should be implemented at the tableam layer and not inside heap. Maybe the BulkInsertState should have a 2nd strategy buffer for toast tables. CREATE TABLE t(i int, a text, b text, c text,d text,e text,f text,g text); INSERT INTO t SELECT 0, array_agg(a),array_agg(a),array_agg(a),array_agg(a),array_agg(a),array_agg(a) FROM generate_series(1,999)n,repeat(n::text,99)a,generate_series(1,99)b GROUP BY b; INSERT INTO t SELECT * FROM t; INSERT INTO t SELECT * FROM t; INSERT INTO t SELECT * FROM t; INSERT INTO t SELECT * FROM t; ALTER TABLE t ALTER i TYPE smallint; SELECT COUNT(1), relname, COUNT(1) FILTER(WHERE isdirty) FROM pg_buffercache b JOIN pg_class c ON c.oid=b.relfilenode GROUP BY 2 ORDER BY 1 DESC LIMIT 9; Without this patch: postgres=# SELECT COUNT(1), relname, COUNT(1) FILTER(WHERE isdirty) FROM pg_buffercache b JOIN pg_class c ON c.oid=b.relfilenode GROUP BY 2 ORDER BY 1 DESC LIMIT 9; 10283 | pg_toast_55759 | 8967 With this patch: 1418 | pg_toast_16597 | 1418 -- Justin