Auto-generate SQL to create indexes

 Need to add a bunch of indexes at once? This script will spit out CREATE INDEX statements for all columns in your DB, one statement per line. Then you can delete the statements you don't want and run the SQL file using psql -f create_indexes.sql

Caveat hacker: do not indiscriminately create indexes on all your columns! Instead, use create_indexes.sql as a way to speed up creation of the indexes you know you need.

Why? Indexes use scarce disk cache, and they slow down writes (because each time a row is written to a table, all indexes on that table must be updated). Create an index only when you have evidence — in the form of before-and-after EXPLAIN ANALYZE plans — that the index will speed up a slow or frequently-used query.

#!/bin/bash

psql -o create_indexes.sql -c "
copy (

    select

        'CREATE INDEX CONCURRENTLY ' || relname::text || '__' || attname::text || ' ON ' || relname::text || '(' || attname::text || ');'

    from

        pg_stat_user_tables

            join pg_attribute on pg_stat_user_tables.relid = pg_attribute.attrelid order by 1

) to stdout csv;

"