On Thu, May 24, 2012 at 3:35 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, May 24, 2012 at 3:46 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> hm, looking at the code some more, it looks like the whole point of
>> the strategy system is to do that. ISTM bulk insert type queries
>> would be good candidates for a buffer strategy somehow?
>
> Yep.
>
> commit 85e2cedf985bfecaf43a18ca17433070f439fb0e
> Author: Tom Lane <tgl@sss.pgh.pa.us>
> Date: Thu Nov 6 20:51:15 2008 +0000
>
> Improve bulk-insert performance by keeping the current target buffer pinned
> (but not locked, as that would risk deadlocks). Also, make it work in a sma
> ring of buffers to avoid having bulk inserts trash the whole buffer arena.
>
> Robert Haas, after an idea of Simon Riggs'.
>
> But that doesn't help here, since pgbench is only looking up one row per query.
Wait -- OP's gripe this isn't regarding standard pgbench, but multiple
large concurrent 'insert into foo select...'. I looked in the code
and it appears that the only bulk insert strategy using operations are
copy, create table as select, and table rewrite operations. Concurrent
INSERT SELECT apparently doesn't get the benefit of a strategy and
should be fighting over the freelist once the pool exhausts.
We don't get to skip wal of course, but we should be able to use a
bulk insert strategy, especially if there was some way of predicting
that a large number of tuples were going to be inserted. I'm
wondering though of contention on the free list is in fact the OP's
problem.
merlin