>On the other hand, if you have very many rows where enabled is not 'T', and you are generally searching for where enabled = 'T', you might want a partial index (an index with a WHERE clause in its definition). If >enabled only has two states, you will probably get better performance using a boolean column. Maybe 1% or 2% are enabled='F' all others are 'T' Another question Kevin (thanks for your time) When an insert/update occur, the index is "reindexed" how index deals with new or deleted rows. Whay happened with select, it wait that index "reindex" or rebuild or something? Or just select view another "version" of the table? Thanks -----Mensaje original----- De: Kevin Grittner [mailto:Kevin.Grittner@xxxxxxxxxxxx] Enviado el: jueves, 08 de septiembre de 2011 01:01 p.m. Para: Anibal David Acosta; pgsql-performance@xxxxxxxxxxxxxx CC: 'Tomas Vondra' Asunto: RE: how delete/insert/update affects select performace? "Anibal David Acosta" <aa@xxxxxxxxxxxx> wrote: > Tthe reason I add the enabled column to index is because a select > won't need to read the table to get this value That's not true in PostgreSQL, although there is an effort to support that optimization, at least to some degree. In all current versions of PostgreSQL, it will always need to read the heap to determine whether the index entry is pointing at a version of the row which is visible to your transaction. Adding the enabled column to an index will prevent faster HOT updates to that column. > My select is : exists(select * from table where account_id=X and > service_id=Y and enabled='T') On the other hand, if you have very many rows where enabled is not 'T', and you are generally searching for where enabled = 'T', you might want a partial index (an index with a WHERE clause in its definition). If enabled only has two states, you will probably get better performance using a boolean column. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
