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))With that stored procedure in place preparing a table to a new set of indexes is as simple as:
RETURNS void AS
objname character varying;
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
EXECUTE 'DROP INDEX ' || objname ;
LANGUAGE 'plpgsql' VOLATILE
SELECT deindex_table('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.
VACUUM ANALYZE VERBOSE job;
Note: Due to PostgreSQL restrictions it is not possible to execute a VACUUM or and ANALYZE statement from within a stored procedure.