From: Benjamin Krajmalnik <kraj@servoyant.com>
To: Kevin Grittner <kgrittn@mail.com>
To: pgsql-admin@postgresql.org
Subject: Re: Schema design question as it pertains to performance
Date: Tue, 22 Jan 2013 17:48:42 -0700
Message-ID: <F4E6A2751A2823418A21D4A160B6898892B89D@fletch.stackdump.local> (raw)
In-Reply-To: <20130123002301.120590@gmx.com>
References: <20130123002301.120590@gmx.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Thanks for the clarification. Now I understand - it is an all or nothing due to required entries for each row version on the main table.
I will have to see if removing the constantly changing value from the indices will affect the performance of our selects.
-----Original Message-----
From: Kevin Grittner [mailto:kgrittn@mail.com]
Sent: Tuesday, January 22, 2013 5:23 PM
To: Benjamin Krajmalnik; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Schema design question as it pertains to performance
Benjamin Krajmalnik wrote:
> Kevin Grittner wrote:
>> Benjamin Krajmalnik wrote:
>>> 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 any indexed column is updated, [...] it needs new
>> entries in all the indexes.
> That was my intent - if no column of an index changes in an update
> then no changes are done on the index.
I don't think you understood -- if any column referenced by any index is updated, then all indexes must get a new entry for that row whether or not they include that column. The new version of the row will be at a new location, so new entries are needed in all indexes to point to the new location.
I did *not* say that only indexes referencing the updated column need a new entry.
-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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: kraj@servoyant.com, kgrittn@mail.com
Subject: Re: Schema design question as it pertains to performance
In-Reply-To: <F4E6A2751A2823418A21D4A160B6898892B89D@fletch.stackdump.local>
References: <20130123002301.120590@gmx.com> <F4E6A2751A2823418A21D4A160B6898892B89D@fletch.stackdump.local>
* 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