Finding the postgresql tables that have been clustered

thinc on 2010-04-30T20:30:51

At $work we have a job that runs every night that calls clusterdb [1] on our database. clusterdb reruns cluster [2] against any tables that have previously been clustered.

If you want to know which tables have previously had cluster run on them, the following query does the trick:

SELECT pg_class.relname, pg_index.indisclustered FROM pg_class JOIN pg_index ON pg_class.oid = pg_index.indrelid AND pg_index.indisclustered = 't';

This will list all the tables that cluster will reorder.

[1] http://www.postgresql.org/docs/8.3/static/app-clusterdb.html

[2] http://www.postgresql.org/docs/8.3/static/sql-cluster.html