"Anibal David Acosta" <aa@xxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
