From: "Anibal David Acosta" <aa@devshock.com>
To: "'Kevin Grittner'" <Kevin.Grittner@wicourts.gov>,
	<pgsql-performance@postgresql.org>
Cc: "'Tomas Vondra'" <tv@fuzzy.cz>
References: <004301cc6e26$015a75e0$040f61a0$@devshock.com>
	<4E688FFC0200002500040F10@gw.wicourts.gov>
	<012001cc6e45$fa57f950$ef07ebf0$@devshock.com>
	<4E68AE600200002500040F47@gw.wicourts.gov>
In-Reply-To: <4E68AE600200002500040F47@gw.wicourts.gov>
Subject: Re: how delete/insert/update affects select	 performace?
Date: Thu, 8 Sep 2011 15:34:00 -0400
Message-ID: <008001cc6e5e$44737420$cd5a5c60$@devshock.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AQHYrwFqiugYygNYi1ErgOQqHWWRlwF7ZGtRALkg6ZgB5VikY5UKjsfQ
Content-Language: es-py

>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@wicourts.gov] 
Enviado el: jueves, 08 de septiembre de 2011 01:01 p.m.
Para: Anibal David Acosta; pgsql-performance@postgresql.org
CC: 'Tomas Vondra'
Asunto: RE: [PERFORM] how delete/insert/update affects select performace?

"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