#!/bin/bash

FILE=$1

if [ ! $FILE ] ; then
	echo usage: $0 analyze_file
	echo
	exit;
fi
#D='\d '
D='\d+ '

# Get indexes
INDICES=$( egrep -o 'Index Scan using \b[a-zA-Z0-9_-]*' $FILE  | cut -f 4 -d ' ' | sort | uniq ) 


# Get tables
TABLES=$( \
cat <( \
# Indexed tables \
egrep -o 'Index Scan using \b[a-zA-Z0-9_-]* on [a-zA-Z0-9_-]*' $FILE  | cut -f 6 -d ' ' \
) <( \
# Scanned Tables \
egrep -o 'Seq Scan on \b[a-zA-Z0-9_-]* ' $FILE | cut -f 4 -d ' ' \
) | sort | uniq \

)


# Report version
echo "SELECT 'Postgres Version';"
echo "SELECT version();"
#echo "SELECT '';"

# Server Tuning

echo "SELECT 'Server Tuning';"
echo "SELECT name, current_setting(name), source FROM pg_settings WHERE source NOT IN ('default', 'override');"

# Issue \d table command
echo "SELECT 'Table Descriptions';"
for x in $TABLES; do echo  "$D $x" ; done ; echo

# Issue \d index command
echo "SELECT 'Index Descriptions';"
for x in $INDICES; do echo "$D $x" ; done ; echo

# Get actual & estimated table counts
echo "SELECT 'Table Counts, (actual and estimated)';"
for x in $TABLES ; do
echo "SELECT '$x' AS table,count(*) AS actual, (SELECT reltuples FROM pg_class WHERE relname = '$x') AS estimated FROM $x UNION"
done
echo "VALUES (NULL::text,NULL::int,NULL::int);"



