Re: SELECT * FROM <table> LIMIT 1; is really slow

On Wed, 26 May 2004, Tom Lane wrote:

> if you have to do an UPDATE that affects every row of a large table
>
> UPDATE tab SET col = col + 1
>
> which leaves you with N live rows, N dead rows, and lots of pain to get
> back down to a less-than-twice-normal-size table. (Traditional way is
> VACUUM FULL; CLUSTER is better, but still painful.) As of 7.5 you could
> hack this with
>
> ALTER TABLE tab ALTER COLUMN col TYPE same-type USING col + 1
>
> which will have the effect of rewriting a fresh table file containing
> the updated rows, and dropping the old file at commit.
>
> I'm not real sure where to document this
> trick but it seems like we ought to mention it someplace.

Isn't it better to detect a UPDATE without a where and do that update in
the same way as the alter table above? Then we don't need to document and
learn a new non standard way of doing an update.

--
/Dennis Björklund