Re: when to reindex?

On Sat, Jun 7, 2008 at 8:07 PM, Jeremy Harris <jgh(at)wizmail(dot)org> wrote:

> Gregory Stark wrote:
>
>> REINDEX scans the table
>> precisely once and sorts it.
>>
>
> For the bloat, as opposed to corruption, case -
> what information is needed from the table that
> is not in the old index? Why would a sequential
> read of the old index alone (then some processing)
> not suffice?

In Postgres, an index does not hold the livliness information of the rows it
is pointing it; that is, there may be a thousand row-pointers in the index,
but not all the rows pointed to by those pointers are known to be live. This
is an implication of MVCC in Postgres.

So every index lookup has to look at the corresponding heap (aka table) row
and decide if that row should be visible to the querying session.

Best regards,

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device