Re: Why Does UPDATE Take So Long?

On Tue, Sep 30, 2008 at 4:37 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Tue, 2008-09-30 at 16:34 -0600, Bill Thoen wrote:
>> > Also, how many indexes does this table have?
>> >
>> >
>> Two, but the column I'm updating isn't included in either one of them.
>>
>
> Even if the column is not indexed, when a new row is created (which is
> the case with UPDATE) a new index entry must be made in each index to
> point to the new row.

Unless you're:

running 8.3 or later AND
have enough free space for the new tuple to go in the same page.

for instance here's a sample from my db at work:

select n_tup_upd, n_tup_hot_upd from pg_stat_user_tables where
schemaname='public' order by n_tup_hot_upd desc limit 20; n_tup_upd |
n_tup_hot_upd
-----------+---------------
52872193 | 5665884
4635216 | 3876594
264194 | 261693
159171 | 153360
242383 | 75591
97962 | 72665
86800 | 66914
57300 | 56013
284929 | 50079
43411 | 37527
43283 | 33285
30657 | 28132
31705 | 22572
26358 | 18495
19296 | 18411
22299 | 17065
16343 | 15981
23311 | 15748
13575 | 13330
12808 | 12536

If you notice some of those tables have well over 75% of the updates
are HOT. Our load dropped from 15 or 20 to 1 or 2 going to 8.3.