"Anibal David Acosta" <aa(at)devshock(dot)com> 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