Deindexing A Table (PostgreSQL)

One of the key steps for performing tuning a database is to test indexing strategies and their effect on performance. It is a series of:

1. Drop the indexes
2. Create new indexes
3. Update statistics
4. EXPLAIN the important queries.

Rinse and repeat.

The most annoying step in testing different sets of index is 1. Drop the indexes - as you need to know the name of every index to drop. This generally means there is a large amount of tedious and RSI inducing cut-n-paste involved in the process. That amount of cut-n-paste also tends to rapidly become error prone.

There has got to be some way to expedite that process, right? After all, the database engine knows the indexes that exist on a table.

Yep! At least for PostgreSQL a stored procedure can be constructed that drops the indexes defined on a named table.

CREATE OR REPLACE FUNCTION deindex_table(table_name VARCHAR(255))
  RETURNS void AS
    $BODY$
     DECLARE
      objname character varying;

     BEGIN

      FOR objname IN SELECT ci.relname 
                       FROM pg_index i,pg_class ci,pg_class ct 
                      WHERE i.indexrelid=ci.oid AND i.indrelid=ct.oid 
                        AND i.indisprimary = false 
                        AND ct.relname=table_name
      LOOP
       EXECUTE 'DROP INDEX ' || objname ;
      END LOOP;

     END;
    $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

With that stored procedure in place preparing a table to a new set of indexes is as simple as:

SELECT deindex_table('job');
VACUUM ANALYZE VERBOSE job;

One caveat is that indexes automatically created by the engine to enforce constraints cannot be dropped; in particular this procedure skips any index involved with enforcing the tables primary key - but you almost certainly don't intend to change the primary key, so that may be a feature rather than a bug.

Note: Due to PostgreSQL restrictions it is not possible to execute a VACUUM or and ANALYZE statement from within a stored procedure.