Running ANALYZE (or other table-specific commands) across distributed tables in a Citus cluster

analyze_all.sh

# Analyzes all distributed tables

export PGHOST=...
export PGUSER=citus
export PGPASSWORD=...
#export PGSSLMODE=require
export PGDATABASE=citus
export PARALLEL_FACTOR=4

psql -tA -F" " -c "SELECT logicalrelid::regclass||'_'||s.shardid, \
nodename, nodeport FROM pg_dist_shard s JOIN pg_dist_shard_placement p ON (s.shardid = p.shardid)" |
xargs -n 3 -P $PARALLEL_FACTOR sh -c "psql -h \$1 -p \$2 -c \"ANALYZE \$0\""

analyze_one.sh

# Analyzes a specific distributed table

export PGHOST=...
export PGUSER=citus
export PGPASSWORD=...
#export PGSSLMODE=require
export PGDATABASE=citus
export TABLE_NAME=impressions
export PARALLEL_FACTOR=4

psql -tA -F" " -c "SELECT logicalrelid::regclass||'_'||s.shardid, \
nodename, nodeport FROM pg_dist_shard s JOIN pg_dist_shard_placement p ON (s.shardid = p.shardid) \
WHERE logicalrelid::regclass = '$TABLE_NAME'::regclass;" |
xargs -n 3 -P $PARALLEL_FACTOR sh -c "psql -h \$1 -p \$2 -c \"ANALYZE \$0\""