Re: Reduce WAL logging of INSERT SELECT

On Sat, Aug 6, 2011 at 4:16 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> Well, if the table is created in the same transaction (which is the only
> case under consideration), no other sessions can write to the table so
> you are just writing the entire table on commit, rather than to the WAL.

Below a certain point, skipping WAL is slower and over an intermediate
range there is no benefit. So small amounts of data on large servers
goes slower.

heap_fsync() requires a scan of shared buffers, which may not be cheap.

There is a difficulty because you would need to calculate the cut-off
is for a particular database, and then predict ahead of time whether
the number of rows that will be handled by the statement is low enough
to warrant using the optimisation. Both of which I call a hard
problem.

I think we should remove the COPY optimisation because of this and
definitely not extend INSERT SELECT to perform it automatically.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services