From: Kevin Grittner <kgrittn@mail.com>
To: Benjamin Krajmalnik <kraj@servoyant.com>
To: pgsql-admin@postgresql.org
Subject: Re: Schema design question as it pertains to performance
Date: Tue, 22 Jan 2013 16:56:01 -0500
Message-ID: <20130122215601.120610@gmx.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Benjamin Krajmalnik wrote:
> From a performance standpoint, is there a big hit on select performance
> if a query ends up utilizing more than one index, taking into account
> that an index has been used already to reduce the data set of potential
> records, and the secondary index would mostly be used in the ordering of
> the result set (such as a last updated time)?
That depends on so many variables it is hard to give a simple answer.
> I also assume that if no data has changed in an index, nothing is done
> when the record is updated as pertains to the particular index - am I
> correct in this assumption?
No. If the update doesn't affect *any* indexed column, and there is
room in the page, it will do a HOT update and can skip all index
updates. If any indexed column is updated, it must expire the old
tuple and create a new tuple to represent the updated version of
the row, and this new tuple is not likely to land in the same page
as the old tuple; so it needs new entries in all the indexes. The
old index entries must remain until they can no longer be the
visible version of the row for any database transaction, so both
versions of the row will be on the index for a while.
-Kevin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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-admin@postgresql.org
Cc: kgrittn@mail.com, kraj@servoyant.com
Subject: Re: Schema design question as it pertains to performance
In-Reply-To: <20130122215601.120610@gmx.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