Re: Insert performance and multi-column index order

On Fri, 26 Jun 2009, bob_lunney@yahoo.com wrote:

> The original unique index was in the order (timestamptz, varchar, text,
> text) and most queries against it were slow.  I changed the index order
> to (varchar, text, timestamptz, text) and queries now fly, but loading
> data (via copy from stdin) in the table is 2-4 times slower.

Is the input data closer to being sorted by the timestamptz field than the
varchar field?  What you might be seeing is that the working set of index
pages needed to keep building the varchar index are bigger or have more of
a random access component to them as they spill in and out of the buffer
cache.  Usually you can get a better idea what the difference is by
comparing the output from vmstat while the two are loading.  More random
read/write requests in the mix will increase the waiting for I/O
percentage while not increasing the total amount read/written per second.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD