Re: [HACKERS] Why frequently updated tables are an issue

[Shridhar Daithankar]

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster