Re: how delete/insert/update affects select performace?

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема Re: how delete/insert/update affects select performace?
Дата
Msg-id 4E68AE600200002500040F47@gw.wicourts.gov
обсуждение исходный текст
Ответ на Re: how delete/insert/update affects select performace?  ("Anibal David Acosta" <aa@devshock.com>)
Ответы Re: how delete/insert/update affects select performace?  ("Anibal David Acosta" <aa@devshock.com>)
Список pgsql-performance
"Anibal David Acosta" <aa@devshock.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

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Anibal David Acosta"
Дата:
Сообщение: Re: how delete/insert/update affects select performace?
Следующее
От: Merlin Moncure
Дата:
Сообщение: Re: how fast index works?