Re: Why Does UPDATE Take So Long?

On Tue, Sep 30, 2008 at 4:37 PM, Jeff Davis <pgsql@j-davis.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.