On Tue, Apr 04, 2006 at 04:16:12PM -0400, Jan Wieck wrote:
> int4/int8 column one. Sure, that scan will be more expensive. But I 
> think Andrew forgot that using an artificial serial column means that 
> your origin and subscribers must maintain an additional index on all 
> insert/update operations (and vacuum must clean it up after deletes).
> 
> Andrew, do you have actual experience that it will perform better, or 
> was that an assumption?

No, I didn't forget; but I probably should have made this clearer. 
The real question is what the use patterns are.

For cases where you're likely to change _all_ the columns at once
(think INSERT only, for instance), the overhead for the additional
index is likely not worth it.

For cases where you insert once, but then some (but not all) of the
values change over time, then depending on your hardware, use
patterns, and network speeds, the single column might be worth it.  A
six-column key where 5 the items are longish pieces of text and one
is a status value, for instance, would be not only expensive to look
up: it will also be expensive to ship on the network.  So if the only
thing that normally changes on the table is the status value, the
single-column serial number (== artificial key) might make sense,
provided that you have the processor and disk bandwidth to keep up
with the additional index maintenance and vacuuming.

You have to evaluate these cases one at a time.

A

-- 
Andrew Sullivan  | ajs at crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
		--Philip Greenspun