Search Postgresql Archives

Re: index bloat WAS: reindexing pg_shdepend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux