Re: Index speeds up one row table (why)?

Bruno Wolff III <bruno(at)wolff(dot)to> 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