Re: Schema design question as it pertains to performance - Mailing list pgsql-admin

From Benjamin Krajmalnik
Subject Re: Schema design question as it pertains to performance
Date
Msg-id F4E6A2751A2823418A21D4A160B6898892B89D@fletch.stackdump.local
Whole thread Raw
In response to Re: Schema design question as it pertains to performance  ("Kevin Grittner" <kgrittn@mail.com>)
List pgsql-admin
Thanks for the clarification.  Now I understand - it is an all or nothing due to required entries for each row version
onthe 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
forthat row whether or not they include that column. The new version of the row will be at a new location, so new
entriesare 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

pgsql-admin by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: Schema design question as it pertains to performance
Next
From: Bert
Date:
Subject: vacuum / analyze parent tables on partitioned tables.