Joseph S <jks@xxxxxxxxxxxxxxx> writes: > ... and when I notice that the tuplesperpage for the indexes is low (or > that the indexes are bigger then the tables themselves) I know it is > time for a VACUUM FULL and REINDEX on that table. If you are taking the latter as a blind must-be-wrong condition, you are fooling yourself -- it's not true for small tables. For instance, in a freshly initdb'd database: postgres=# vacuum verbose pg_opclass; INFO: vacuuming "pg_catalog.pg_opclass" INFO: index "pg_opclass_am_name_nsp_index" now contains 107 row versions in 4 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.00 sec. INFO: index "pg_opclass_oid_index" now contains 107 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_opclass": found 0 removable, 107 nonremovable row versions in 2 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 1 pages contain useful free space. 0 pages are entirely empty. CPU 0.01s/0.00u sec elapsed 0.00 sec. VACUUM postgres=# Have you checked whether the VACUUM FULL + REINDEX actually makes anything smaller? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
