On 8 Jul 2005, Adam Pritchard wrote: > so I have a singleton table that keeps track of where we are in the > pseudo-sequence. The table is very simple: > > CREATE TABLE t ( next BIGINT ); > INSERT INTO t ( next ) VALUES ( 0 ); > > UPDATE t SET next = $1; -- $1 = next+1 > > The problem is that the performance of those operations is pretty slow > > 1000: 891ms > 2000: 1296ms > 3000: 1735ms > 4000: 2312ms > 5000: 2844ms > 6000: 3328ms > 7000: 3875ms > 8000: 4531ms > 9000: 4875ms Looks like you need to vacuum that table very, very often. Each time you update the counter you get a dead row that needs to be vacuumed. You can't vacuum inside a transaction, but maybe that is not a problem in your case. For bulk loading in a transaction you might also be able to lock the table to prevent others from using it and then insert 1000 rows and do: UPDATE t SET next = next + 1000; -- /Dennis Björklund ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
