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=
=20
> 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.

--=20
Rod Taylor <rbt@rbt.ca>

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