Re: Reduce WAL logging of INSERT SELECT

On 05.08.2011 04:23, Jeff Davis wrote:
> On Thu, 2011-08-04 at 20:55 -0400, Bruce Momjian wrote:
>> It would act like COPY, meaning the table would have to be truncated or
>> created in the same transaction.
>
> Well, in that case it could work for any INSERT. No need for a SELECT to
> be involved. For that matter, why not make it work for DELETE and
> UPDATE, too?

Yep. If we are to expand it, we should make it work for any operation.

However, for small operations it's a net loss - you avoid writing a WAL
record, but you have to fsync() the heap instead. If you only modify a
few rows, the extra fsync (or fsyncs if there are indexes too) is more
expensive than writing the WAL.

We'd need a heuristic to decide whether to write WAL or fsync at the
end. For regular INSERTs, UPDATEs and DELETEs, you have the planner's
estimate of number of rows affected. Another thing we should do is move
the fsync call from the end of COPY (and other such operations) to the
end of transaction. That way if you do e.g one COPY followed by a bunch
of smaller INSERTs or UPDATEs, you only need to fsync the files once.

> However, I think this is all just a workaround for not having a faster
> loading path. I don't object to applying this optimization to inserts,
> but I think it might be more productive to figure out if we can support
> loading data efficiently -- i.e. also set hint bits and frozenxid during
> the load.

Yeah, that would make a much bigger impact in practice.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com