Content-Type: text/plain; charset="utf-8"
Date: Tue, 22 Jan 2013 16:56:01 -0500
From: "Kevin Grittner" <kgrittn@mail.com>
Message-ID: <20130122215601.120610@gmx.com>
MIME-Version: 1.0
Subject: Re: Schema design question as it pertains to performance
To: "Benjamin Krajmalnik" <kraj@servoyant.com>,pgsql-admin@postgresql.org
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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