This script will find spatial tables that have no index a build an index on them.

PostGIS index Builder

#!/bin/env python

import psycopg2

# Connect to db
dbname =
host =
user =
try:
    db = psycopg2.connect(dbname=dbname,host=host,user=user)
    cursor = db.cursor()
except Exception, dbmsg:
    print str(dbmsg)
    print "I cannot work without a database connection"
    sys.exit(1)  

# Get spatial tables that don't have an index

query = """SELECT g.f_table_name, g.f_geometry_column FROM geometry_columns g LEFT JOIN pg_indexes i ON g.f_table_name=i.tablename WHERE i.tablename IS NULL;"""
cursor.execute(query)
tables = cursor.fetchall()

# Loop over the list and build the index for each table
for table, geom_column in tables:
    query = """CREATE INDEX CONCURRENTLY {0}_{1}_index ON {0} USING GIST ({1});""".format(table, geom_column)
    cursor.execute(query)

cursor.close()
db.close()