Re: Vacuum non-clustered tables only

On Wed, 2007-05-16 at 15:40 -0700, Glen Parker wrote:
> Is there a semi-easy way vacuum all tables in a database *except*
> those that are clustered?

Yes, it can be done "semi-easily". Here's an example:

select N.nspname,relname
from pg_class C
join pg_namespace N on C.relnamespace=N.oid
where relkind='r' and not exists
(select * from pg_index I
where C.oid=I.indrelid and I.indisclustered);

Many of us build statements in SQL itself. For instance, replace the
first line above with

select 'vacuum '||N.nspname||'.'||C.relname||';' as vacuum_cmd

and now the query returns executable statements. You probably want to
restrict by namespace/schema name as well. If you put all of that into
a view, it's then easy to do something like

$ psql -c 'select vacuum_cmd from view' | psql -aX

which uses one connection to select the script, and another to execute
it.

Good luck,
Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0