Automate Foreign Key Index Checks in PostgreSQL: A Practical Bash Script

./check_fk_without_index_on_src_columns.sh -h localhost -p 5432 -x '%mydb%' -a 0 -b 10

#!/bin/bash

##################################################################################################################################
#
# Name: check_fk_without_index_on_src_columns.sh
#
# Description: Check Postgres FKs with missing indexes on source
#
# Author: Dmitry
#
# Date: 01-Jan-2020
#
# Usage Example:
#
#     ./check_fk_without_index_on_src_columns.sh -h localhost -p port -x db_pattern -a start_position -b end_position
#
####################################################################################################################################

helpFunction()
{
   echo ""
   echo "Usage: $0 -h hostname -p port -x db_pattern -a offset -b limit"
   echo -e "\t-h Postgres hostname"
   echo -e "\t-p Postgers port"
   echo -e "\t-x Postgres db pattern"
   echo -e "\t-a Report offset starting from 0"
   echo -e "\t-b Report limit"
   echo -e " "
   echo -e "Example how to run: $0 -h localhost -p 15459 -x % -a 0 -b 10 "
   echo -e " ** during run the output report is generated automatically"
   echo -e " ** the output report is generated in the directory output_dir"
   echo -e " "
   exit 1 # Exit script after printing help
}

while getopts "h:p:x:a:b:" opt
do
   case "$opt" in
      h ) inpHost="$OPTARG" ;;
      p ) inpPort="$OPTARG" ;;
      x ) inpDBPattern="$OPTARG" ;;
      a ) inpOffset="$OPTARG" ;;
      b ) inpLimit="$OPTARG" ;;
      ? ) helpFunction ;; # Print helpFunction in case parameter is non-existent
   esac
done

# Print helpFunction in case parameters are empty
if [ -z "$inpHost" ] || [ -z "$inpPort" ] || [ -z "$inpDBPattern" ] || [ -z "$inpOffset" ] || [ -z "$inpLimit" ] 
then
   echo "Some or all of the parameters are empty";
   helpFunction
fi

# Begin script in case all parameters are correct

db_sdm_name=`sdm status | grep $inpPort | awk ' { print $1 }'`
current_date_time="$(date +'%Y%m%d_%H%M%S')" 

report_name="Check postgres FKs with missing index on source column(s)"

output_dir="./output_dir"

output_trace_name="report_check_pg_fk_without_indexes_${current_date_time}_port_${inpPort}_dbinstance_${db_sdm_name}.trc"

output_full_name="${output_dir}/${output_trace_name}"

touch ${output_full_name}

echo "------------------------------------------------------------------" 
echo "Report: ${report_name} " 
echo "------------------------------------------------------------------" 
echo "Postgres DB Instance: [${db_sdm_name}] port [${inpPort}]" 
echo "$report_name" 
echo "Timestamp: [${current_date_time}]" 
echo "------------------------------------------------------------------" 

echo " " 
echo "Input parameters:" 
echo "---------------- " 
echo "inpHost=$inpHost" 
echo "inpPort=$inpPort" 
echo "inpDBPattern=$inpDBPattern"
echo "inpOffset=$inpOffset"
echo "inpLimit=$inpLimit"
echo "---------------- "

echo "------------------------------------------------------------------" >> $output_full_name
echo "Report: ${report_name} " >> $output_full_name
echo "------------------------------------------------------------------" >> $output_full_name
echo "Postgres DB Instance: [${db_sdm_name}] port [${inpPort}]" >> $output_full_name
echo "$report_name"  >> $output_full_name
echo "Timestamp: [${current_date_time}]" >> $output_full_name
echo "------------------------------------------------------------------" >> $output_full_name

echo " " >> $output_full_name
echo "Input parameters:" >> $output_full_name
echo "---------------- " >> $output_full_name
echo "inpHost=$inpHost" >> $output_full_name
echo "inpPort=$inpPort" >> $output_full_name
echo "inpDBPattern=$inpDBPattern" >> $output_full_name
echo "inpOffset=$inpOffset" >> $output_full_name
echo "inpLimit=$inpLimit" >> $output_full_name
echo "---------------- " >> $output_full_name

num_of_dbs_to_process=$(psql -h $inpHost -p $inpPort -t -c "select count(M.*) from (select datname from pg_database where datname like '${inpDBPattern}' order by 1 offset ${inpOffset} limit ${inpLimit}) M " postgres)

echo " "
echo "------------------------------------------------------------------"
echo " Number of databases: ${num_of_dbs_to_process}"
echo "------------------------------------------------------------------"

echo " " >> $output_full_name
echo "------------------------------------------------------------------" >> $output_full_name
echo " Number of databases: ${num_of_dbs_to_process}" >> $output_full_name
echo "------------------------------------------------------------------" >> $output_full_name

idy=0

idx=${inpOffset}

idx=$(($idx + 1))

for DBs_to_process in $(psql -h $inpHost -p $inpPort -t -c "select M.* from (select datname from pg_database where datname like '${inpDBPattern}' order by 1 offset ${inpOffset} limit ${inpLimit} ) M " postgres); do
echo "#${idx}:"
echo "DB:${DBs_to_process}" 

echo "#${idx}:" >> $output_full_name
echo "DB:${DBs_to_process}" >> $output_full_name

n_of_fks_without_indexes_on_src_columns="1"

n_of_fks_without_indexes_on_src_columns=$(psql -h localhost -p $inpPort -U postgres -d $DBs_to_process -t << EOF
select count(1)
from
(
SELECT c.conrelid::regclass AS "table",
       string_agg(a.attname, ',' ORDER BY x.n) AS columns,
       pg_catalog.pg_size_pretty(
          pg_catalog.pg_relation_size(c.conrelid)
       ) AS size,
       c.conname AS constraint, 
       c.confrelid::regclass AS referenced_table
FROM pg_catalog.pg_constraint c
   /* enumerated key column numbers per foreign key */
   CROSS JOIN LATERAL
      unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
   /* name for each key column */
   JOIN pg_catalog.pg_attribute a
      ON a.attnum = x.attnum
         AND a.attrelid = c.conrelid
WHERE NOT EXISTS
        (SELECT 1 FROM pg_catalog.pg_index i
         WHERE i.indrelid = c.conrelid 
           AND i.indpred IS NULL
           AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1]
               OPERATOR(pg_catalog.@>) c.conkey)
  AND c.contype = 'f'
GROUP BY c.conrelid, c.conname, c.confrelid
ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC
) M;
EOF)

if [ "$n_of_fks_without_indexes_on_src_columns" -ne "0" ]; then

psql -h localhost -p $inpPort -U postgres -d $DBs_to_process << EOF
SELECT c.conrelid::regclass AS "table",
       /* list of key column names in order */
       string_agg(a.attname, ',' ORDER BY x.n) AS columns,
       pg_catalog.pg_size_pretty(
          pg_catalog.pg_relation_size(c.conrelid)
       ) AS size,
       c.conname AS constraint,
       c.confrelid::regclass AS referenced_table
FROM pg_catalog.pg_constraint c
   /* enumerated key column numbers per foreign key */
   CROSS JOIN LATERAL
      unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
   /* name for each key column */
   JOIN pg_catalog.pg_attribute a
      ON a.attnum = x.attnum
         AND a.attrelid = c.conrelid
WHERE NOT EXISTS
        /* is there a matching index for the constraint? */
        (SELECT 1 FROM pg_catalog.pg_index i
         WHERE i.indrelid = c.conrelid
           /* it must not be a partial index */
           AND i.indpred IS NULL
           /* the first index columns must be the same as the
              key columns, but order doesn't matter */
           AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1]
               OPERATOR(pg_catalog.@>) c.conkey)
  AND c.contype = 'f'
GROUP BY c.conrelid, c.conname, c.confrelid
ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC;
EOF

psql -h localhost -p $inpPort -U postgres -d $DBs_to_process << EOF >> $output_full_name
SELECT c.conrelid::regclass AS "table",
       /* list of key column names in order */
       string_agg(a.attname, ',' ORDER BY x.n) AS columns,
       pg_catalog.pg_size_pretty(
          pg_catalog.pg_relation_size(c.conrelid)
       ) AS size,
       c.conname AS constraint,
       c.confrelid::regclass AS referenced_table
FROM pg_catalog.pg_constraint c
   /* enumerated key column numbers per foreign key */
   CROSS JOIN LATERAL
      unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
   /* name for each key column */
   JOIN pg_catalog.pg_attribute a
      ON a.attnum = x.attnum
         AND a.attrelid = c.conrelid
WHERE NOT EXISTS
        /* is there a matching index for the constraint? */
        (SELECT 1 FROM pg_catalog.pg_index i
         WHERE i.indrelid = c.conrelid
           /* it must not be a partial index */
           AND i.indpred IS NULL
           /* the first index columns must be the same as the
              key columns, but order doesn't matter */
           AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1]
               OPERATOR(pg_catalog.@>) c.conkey)
  AND c.contype = 'f'
GROUP BY c.conrelid, c.conname, c.confrelid
ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC;
EOF

idy=$(($idy + 1))

fi

echo "................................................................."

idx=$(($idx + 1))

done

echo "Total Fks with Missing Indexes On Source Columns: $idy"
echo "Total Fks with Missing Indexes On Source Columns: $idy" >> $output_full_name

echo " "
echo "                      --- The End ---                            " 
echo " " 

echo " " >> $output_full_name
echo "                      --- The End ---                            " >> $output_full_name
echo " " >> $output_full_name

echo " "