pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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


view thread (5+ messages)

Message-ID: <F4E6A2751A2823418A21D4A160B6898892B89D@fletch.stackdump.local>
Permalink:  ../F4E6A2751A2823418A21D4A160B6898892B89D@fletch.stackdump.local/
Also on:    postgresql.org/message-id/F4E6A2751A2823418A21D4A160B6898892B89D@fletch.stackdump.local

 · 

reply

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