Skip site navigation (1) Skip section navigation (2)

Re: Exact index overhead

From: Richard Huxton <dev(at)archonet(dot)com>
To: Gunther Mayer <gunther(dot)mayer(at)googlemail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Exact index overhead
Date: 2008-04-17 10:00:14
Message-ID: 48071FAE.1070408@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Heikki LinnakangasDate: 2008-04-17 10:02:47
Subject: Re: Exact index overhead
Previous:From: Gunther MayerDate: 2008-04-17 09:27:35
Subject: Exact index overhead

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group