Message-ID: <48071FAE.1070408@archonet.com>
Date: Thu, 17 Apr 2008 11:00:14 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080213)
MIME-Version: 1.0
To: Gunther Mayer <gunther.mayer@googlemail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Exact index overhead
References: <48071807.8000409@gmail.com>
In-Reply-To: <48071807.8000409@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Gunther Mayer wrote:
> You see, all updates change most of the data fields but never ever touch 
> the time field. Assuming correct and efficient behaviour of postgresql 
> it should then also never touch the time index and incur zero overhead 
> in its presence, but is this really the case? If it somehow does update 
> the index too even though the value hasn't changed by some weird 
> implementation detail I'd rather not have that index and live with slow 
> queries for the few times a day that reporting is run.

Well, until 8.3 PG does indeed update the index. That's because with 
MVCC an update is basically a delete+insert, so you'll end up with two 
versions (the V in MVCC) of the row.

With 8.3 there's a new feature called HOT which means updates that don't 
change an index can be more efficient.

So - if you are running 8.3, I'd say try the index and see what 
difference it makes.

-- 
   Richard Huxton
   Archonet Ltd