Antonios Christofides <anthony@xxxxxxxxxxxx> writes: > CREATE TABLE test(id integer not null primary key, records text[]); > UPDATE test SET records[2000007] = 'hello, world!'; > [11 seconds] > UPDATE test SET records[1000000] = 'hello, world!'; > [15 seconds (but the difference may be because of system load - I > don't have a completely idle machine available right now)] > I thought the two above UPDATE commands would be instant. Hardly likely seeing that text[] has variable-length array entries; the only way to isolate and replace the 2000007'th entry is to scan through all the ones before it. However the fact that the two cases seem to be about the same speed suggests to me that the bulk of the time is going into loading/decompressing/compressing/storing the array datum. You might try experimenting with the column storage option (see ALTER TABLE) --- using EXTERNAL instead of the default EXTENDED would suppress the compression/decompression step. I suspect that will be a net loss because it will eliminate CPU overhead by trading it off for more I/O ... but it would be worth doing the experiment to find out. On the whole though, I think you're going to have to abandon this approach. The TOAST mechanism really isn't designed to support partial updates of huge fields efficiently. It forces any change in the value to be an update of the whole value. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
