Dario Fumagalli <dfumagalli@tin.it> writes:
> - Given that single user REINDEX [database name] FORCE in single user
> modedid rebuild the indexes but did not optimize them;
> - Given that a REINDEX INDEX [index name] did optimize and shrink a
> single index but increased the overall data size;
> - Given that a VACUUM VERBOSE ANALYZE somehow alters the overall
> database files size (seems to re-distribute the file space evenly across
> indexes);
There is no such thing as a "reindex that optimizes" versus "reindex
that doesn't optimize". A rebuild is a rebuild. I suspect that you
were fooled by out-of-date relpages statistics in pg_class, and/or
expecting REINDEX to rebuild indexes it won't.
VACUUM updates relpages to reflect current reality, but I'm not sure
whether REINDEX does.
Also, REINDEX at the database level only reindexes system-table indexes.
regards, tom lane