From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Gunther Mayer <gunther.mayer@googlemail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Exact index overhead
Date: Thu, 17 Apr 2008 13:02:47 +0300
Message-ID: <48072047.7030407@enterprisedb.com> (raw)
In-Reply-To: <48071807.8000409@gmail.com>
References: <48071807.8000409@gmail.com>
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.
Updates do generally modify the indexes as well. The way MVCC has been
implemented in PostgreSQL, UPDATE is internally very much like
DELETE+INSERT. A new row version is inserted, new index pointers are
added for the new row version, and the old row version is marked as deleted.
In version 8.3, however, the new HOT feature reduces the need for that.
In a nutshell, if the new row version fits on the same page as the old
one, no new index pointers need to be created.
I would suggest just testing how much additional overhead the new index
incurs. It might be less expensive than you think.
You didn't mention how often the inserts happen, in other words, how
fast you expect the table to grow. If the table is expected to grow
orders of magnitude larger, you might want to partition the table by date.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, gunther.mayer@googlemail.com
Subject: Re: Exact index overhead
In-Reply-To: <48072047.7030407@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox