Re: performance tuning on inserts

"Peter T. Brown" <peter(at)memeticsystems(dot)com> writes:
> Regarding the SQL: The query I showed there is built dynamically from a
> library of queries chosen by the application user (using a web gui). For
> now, I don't have any way to intelligently condense the often complex series
> of operations into a single 'smart' query.

Nonetheless, I wonder whether you couldn't reconsider the structure.
The fragment you showed seemed to be of two minds about whether
VisitorPointer is a permanent data structure or a suitable place for
temporary row insertions...

> That being said, I still don't understand why doing all those inserts should
> take so long since the entire table should be in memory... I am pretty sure
> I've allowed enough shared_buffers.

You still need WAL traffic. Or don't you care whether those inserts
will survive a system crash? In-RAM buffers surely don't count as
stable storage.

> Regarding timestamps in pg_xlog: as I understand things, if wal_buffers and
> checkpoint_segments are high enough the files in pg_xlog should never be
> used, right?

No, you haven't understood things at all. WAL *will* be written
whenever you make changes, at the latest when the transaction is
committed.

regards, tom lane