Re: Why frequently updated tables are an issue

Glen Parker wrote:

>>It has been suggested in past to add such a visibility to index
>>tuple header so
>>that index and heaps can be cleaned out of order. In such a case
>>other backround
> It seems to me that the benefit of this wouldn't be all that impressive
> *when accessing the cache*, which is the problem this discussion is about.

I doubt. I have seen examnples on general list where people have thousands of
dead *pages* for few hundred live tuples. If it is a problem with cache access,
it will spill to disk as the problem grows.

I don't think postgresql cache is that bad. No matter how you do it RAM is RAM.
Problem is with disk bandwidth. See past discussions about vacuum delay patch
and improvement it brought around.

Vacuum costs disk bandwidth and that affects performance. That remains a fact.

> Disk access would occur more commonly with large tables, which I'll ignore.
> Let's say total scan time for a query on a very dirty table is 100ms. It
> seems safe to assume that the scan time for the index would be *roughly*
> half that of the heap. If visibilty could be determined by looking at just
> the index tuple, you'd cut you query scan time down to 50ms. When the clean
> table case is 7ms total scan time, the difference between 50 and 100 ms is
> not much of an issue; either way, it's still way to high!
>>However increasing index footprint seems to be a tough sell.
> And rightly so, IMO.

Mee too. Unless somebody comes up with patch that demonstrates the improvement.
Obviously people can live with cost of mandatory vacuum so this is not high
priority. But one day it will be.

OTOH if the perceived benefit is not there, at least it is proven that it is not
there. I plan to do it when I find time. But again, I don't face the problem
myself(I don't even use postgresql for anything important for that matter) so
haven't bothered spending any time on it.

As long as it is not high priority, it is going to be a tough sell. Thats not
unusual.

Shridhar