Bruno Wolff III <[EMAIL PROTECTED]> writes: > It probably has one visible row in it. If it can changed a lot, there > may be lots of deleted tuples in a row. That would explain why an > index scan speeds things up.
Right, every UPDATE on unique_ids generates a dead row, and a seqscan has no alternative but to wade through them all. When a unique index is present, the indexscan code knows that after it's fetched one live tuple there can be no more matching the same index key, and so it need not keep examining index entries. Furthermore, due to the way that btree handles equal keys, it is likely (not certain, just likely) that more-recent and hence more-likely-to-be-live tuples will be seen first. However, the above-described optimization for unique keys is new in 7.3.*, and it's buggy. It's disabled as of 7.3.3, so the performance improvement you're seeing will go away as soon as you update (which you should). There's a fresh try at it in 7.4 CVS. More-frequent vacuums would be a much more reliable solution, in any case. If you are updating the single row once a second, then a cron job to vacuum (not full, just plain "vacuum") that particular table every couple of minutes would not be a bad idea. A hundred dead rows will still fit in one disk block (unless there's lots more in the row than you've mentioned), and as long as you can keep the table to one disk block you shouldn't notice any performance degradation. You might care to use contrib/pgstattuple to check out the contents of the table, but I'm pretty sure what you'll find ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly