I've seen this before while doing SET STATISTICS on a larger number of columns
using xargs, but just came up while doing ADD of a large number of columns.
Seems to be roughly linear in number of children but superlinear WRT columns.
I think having to do with catalog update / cache invalidation with many
ALTERs*children*columns?
32 cols and 2 children => 12MB
256 cols and 11 children => 74MB
256 cols and 111 children => 582MB
512 cols and 11 children => 229MB
(in our "huge" case, there were ~1600 columns and maybe even more children)
I was testing with this command
PGHOST=/tmp PGPORT=9999 sh -ec 'for maxcols in 512 ; do
~/src/postgresql.install/bin/postgres -D ~/src/postgres.dat -c port=9999 &
sleep 4; cols=$(for d in `seq 1 $maxcols`; do echo "ADD c$d int,"; done
|xargs); PGOPTIONS="-c client_min_messages=warning" psql postgres -qc "DROP
TABLE t CASCADE" || [ $? -eq 1 ]; psql postgres -qc "CREATE TABLE t()"; for c
in `seq 1 11`; do psql postgres -qc "CREATE TABLE c$c() INHERITS(t)"; done; for
d in `seq 1 $maxcols`; do echo "ALTER TABLE t ADD c$d int;"; done
|PGOPTIONS="-c client_min_messages=DEBUG3 -c log_statement_stats=on" psql
postgres -c "ALTER TABLE t ${cols%,}" 2>/tmp/pg.err2;
~/src/postgresql.install/bin/pg_ctl -swD ~/src/postgres.dat stop; done'
..and log_statment_stats with a variation on the getrusage patch here
https://www.postgresql.org/message-id/20170615145824.GC15684%40telsasoft.com
Justin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
