#!/bin/bash

DB="analyze_error"
SCHEMA="vac_test"
NUM_TABLES=500000
OUTFILE="memory_contexts.log"

(
echo "\\pset pager off"

for ((i=1; i<=NUM_TABLES; i++))
do
    echo "ANALYZE $SCHEMA.tbl_$i;"

    if (( i % 500 == 0 )); then
        echo "\\o |cat >> $OUTFILE"
        echo "\\echo ===== After $i tables ====="
        echo "SELECT name,
                     pg_size_pretty(sum(total_bytes)) AS total,
                     pg_size_pretty(sum(used_bytes)) AS used,
                     count(*) AS contexts
              FROM pg_backend_memory_contexts
              GROUP BY name
              ORDER BY sum(total_bytes) DESC
              LIMIT 15;"
        echo "\\o"
    fi
done

) | psql -d "$DB"