From: | "Benjamin Krajmalnik" <kraj(at)servoyant(dot)com> |
---|---|
To: | "Kevin Grittner" <kgrittn(at)mail(dot)com>, <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Schema design question as it pertains to performance |
Date: | 2013-01-23 00:48:42 |
Message-ID: | F4E6A2751A2823418A21D4A160B6898892B89D@fletch.stackdump.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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(at)mail(dot)com]
Sent: Tuesday, January 22, 2013 5:23 PM
To: Benjamin Krajmalnik; pgsql-admin(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Bert | 2013-01-23 11:43:01 | vacuum / analyze parent tables on partitioned tables. |
Previous Message | Kevin Grittner | 2013-01-23 00:23:01 | Re: Schema design question as it pertains to performance |