On 4/2/06, Martin Scholes <[EMAIL PROTECTED]> wrote: > I have long believed that the bottleneck in transaction-oriented systems is > the writing of the indexes, complete with splits and merges. A single update > to one field of a heavily-indexed table could cause dozens of index writes > to cascade.
This is not the case with *most* OLTP systems. It is, however, an issue with systems that use a MVCC system which employ an MVTO-like algorithm where indexes must be updated because row locations are never the same. > Running again pgbench with 75 users and 100 transactions, I received a > consistent rate of 132 tps, a 40% increase in throughput. Less I/O = increased throughput. Generally, when you use less CPU or less I/O, you're going to get better performance. The question is, at what cost? > Upon recovery, the index gets rebuilt. The only downside is potentially long > rebuild times during recovery. Yes, this is unacceptable for large systems. While retaining the main MVCC implementation PostgreSQL currently has, we're working on a prototype to reduce WAL I/O and index updates in a large percentage of OLTP situations by employing an update-in-place under *safe* conditions. However, I don't see how your recommendation would actually be feasable. Similarly, from my point of view, a database should NEVER become corrupt. Likewise, I think your tests may have been a little biased... you may want to look through the xlog and index code to see how temp tables are handled (or not). In my opinion, I don't think we should have an option to allow the indexes to become corrupt. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match