Re: Insert performance and multi-column index order - Mailing list pgsql-performance

From Greg Smith
Subject Re: Insert performance and multi-column index order
Date
Msg-id alpine.GSO.2.01.0906270100010.29185@westnet.com
Whole thread Raw
In response to Insert performance and multi-column index order  (bob_lunney@yahoo.com)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Greg Smith
Date:
Subject: Re: what server stats to track / monitor ?
Next
From: Scott Carey
Date:
Subject: Re: slow DELETE on 12 M row table