On Thu, 2004-11-18 at 22:51, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > The main problem on INSERTs is that it is usually the same few pages: > > the lead data block and the lead index block. There are ways of > > spreading the load out across an index, but I'm not sure what happens on > > the leading edge of the data relation, but I think it hits the same > > block each time. > > FSM does what it can to spread the insertion load across multiple pages, > but of course this is not going to help much unless your table has lots > of embedded free space. I think it would work pretty well on a table > with lots of update turnover, but not on an INSERT-only workload.
OK, thats what I thought. So with a table with an INSERT-only workload, the FSM is always empty, so there only ever is one block that gets locked. That means we can't ever go faster than 1 CPU can go - any other CPUs will just wait for the block lock. [In Josh's case, 32 INSERT streams won't go significantly faster than about 4 streams, allowing for some overlap of other operations] Would it be possible to: when a new block is allocated from the relation file (rather than reused), we check the FSM - if it is empty, then we allocate 8 new blocks and add them all to the FSM. The next few INSERTers will then use the FSM blocks normally. Doing that will definitely speed up DBT-2 and many other workloads. Many tables have SERIAL defined, or use a monotonically increasing unique key. -- Best Regards, Simon Riggs ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match