Re: Index speeds up one row table (why)?

> vacuum verbose bigint_unique_ids;
> INFO: --Relation public.bigint_unique_ids--
> INFO: Index bigint_unique_ids__table_name: Pages 29; Tuples 1: Deleted
> 5354.
> CPU 0.01s/0.04u sec elapsed 0.05 sec.
> INFO: Removed 11348 tuples in 79 pages.
> CPU 0.00s/0.02u sec elapsed 0.02 sec.
> INFO: Pages 79: Changed 1, Empty 0; Tup 1: Vac 11348, Keep 0, UnUsed 0.
> Total CPU 0.03s/0.06u sec elapsed 0.14 sec.

Vacuum (regular, not full) frequently enough that the 'Pages' value
doesn't increase past 1 and you'll be fine. A sequential scan on a very
small table is what you want to have.

In this particular case, vacuum removed over 11000 dead versions of the
tuple.

An 8 k page will hold approx 140 tuples based on your structure. So,
for every ~100 updates you'll want to run vacuum (regular, not full) on
the table.

--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc