On Fri, 26 Jun 2009, bob_lunney@xxxxxxxxx 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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
