"Anibal David Acosta" <aa(at)devshock(dot)com> wrote:
> Maybe 1% or 2% are enabled='F' all others are 'T'
Then an index on this column is almost certainly going to be
counter-productive. The only index on this column which *might*
make sense is WHERE enabled = 'F', and only if you run queries for
that often enough to outweigh the added maintenance cost. If it's
always one of those two values, I would use boolean (with NOT NULL
if appropriate).
> When an insert/update occur, the index is "reindexed" how index
> deals with new or deleted rows.
Ignoring details of HOT updates, where less work is done if no
indexed column is updated and there is room for the new version of
the row (tuple) on the same page, an UPDATE is almost exactly like a
DELETE and an INSERT in the same transaction. A new tuple (from an
INSERT or UPDATE) is added to the index(es), and if you query
through the index, it will see entries for both the old and new
versions of the row; this is why it must visit both versions -- to
check tuple visibility. Eventually the old tuples and their index
entries are cleaned up through a "vacuum" process (autovacuum or an
explicit VACUUM command). Until then queries do extra work visiting
and ignoring the old tuples. (That is why people who turn off
autovacuum almost always regret it later.)
> Whay happened with select, it wait that index "reindex" or rebuild
> or something? Or just select view another "version" of the table?
The new information is immediately *added*, but there may be other
transactions which should still see the old state of the table, so
cleanup of old tuples and their index entries must wait for those
transactions to complete.
See this for more information:
http://www.postgresql.org/docs/9.0/interactive/mvcc.html
-Kevin