Re: Insert performance and multi-column index order

On Fri, 26 Jun 2009, bob_lunney(at)yahoo(dot)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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>From pgsql-performance-owner(at)postgresql(dot)org Sat Jun 27 22:25:32 2009
Received: from localhost (unknown [200.46.208.211])
by mail.postgresql.org (Postfix) with ESMTP id C571F6344C8
for <pgsql-performance-postgresql(dot)org(at)mail(dot)postgresql(dot)org>; Sat, 27 Jun 2009 22:25:31 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
with ESMTP id 63209-06
for <pgsql-performance-postgresql(dot)org(at)mail(dot)postgresql(dot)org>;
Sat, 27 Jun 2009 22:25:22 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from EXHUB018-2.exch018.msoutlookonline.net (exhub018-2.exch018.msoutlookonline.net [64.78.17.17])
by mail.postgresql.org (Postfix) with ESMTP id 62F426344A5
for <pgsql-performance(at)postgresql(dot)org>; Sat, 27 Jun 2009 22:25:30 -0300 (ADT)
Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by
EXHUB018-2.exch018.msoutlookonline.net ([64.78.17.17]) with mapi; Sat, 27 Jun
2009 18:25:27 -0700
From: Scott Carey <scott(at)richrelevance(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>, Janet Jacobsen <jsjacobsen(at)lbl(dot)gov>
CC: =?utf-8?B?TWFyY2luIFN0xJlwbmlja2k=?= <mstepnicki(at)gmail(dot)com>,
"pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Date: Sat, 27 Jun 2009 18:25:24 -0700
Subject: Re: slow DELETE on 12 M row table
Thread-Topic: [PERFORM] slow DELETE on 12 M row table
Thread-Index: Acn2x8xtTl5UcYF4RD2ysShqVy3ScgAx4LkJ
Message-ID: <C66C1494(dot)8CEB%scott(at)richrelevance(dot)com>
In-Reply-To: <603c8f070906261836o6224b8a7uf304273988e978c3(at)mail(dot)gmail(dot)com>
Accept-Language: en-US
Content-Language: en
X-MS-Has-Attach:
X-MS-TNEF-Correlator:
acceptlanguage: en-US
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: base64
MIME-Version: 1.0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.461 tagged_above=0 required=5 tests=AWL=-0.440,
URIBL_RHS_DOB=0.901
X-Spam-Level:
X-Archive-Number: 200906/376
X-Sequence-Number: 34620

DQoNCg0KT24gNi8yNi8wOSA2OjM2IFBNLCAiUm9iZXJ0IEhhYXMiIDxyb2JlcnRtaGFhc0BnbWFp
bC5jb20+IHdyb3RlOg0KDQo+IDIwMDkvNi8yNiBKYW5ldCBKYWNvYnNlbiA8anNqYWNvYnNlbkBs
YmwuZ292PjoNCj4+IEhpLiDCoFRoZSB1c2VyIGluIHF1ZXN0aW9uIGlzIHVzaW5nIHBzeWNvcGcy
LCB3aGljaCBoZSB1c2VzDQo+PiBwc3ljb3BnMjoNCj4+PiBpbXBvcnQgcHN5Y29wZzINCj4+PiBj
b25uID0gcHN5Y29wZzIuY29ubmVjdCgiZGJuYW1lPSVzIMKgdXNlcj0lcyBob3N0PSVzIHBhc3N3
b3JkPSVzIHBvcnQ9JXMiDQo+Pj4gLi4uKQ0KPj4+IHBnX2N1cnNvciA9IGNvbm4uY3Vyc29yKCkN
Cj4+PiBwZ19jdXJzb3IuZXhlY3V0ZSg8c2VsZWN0IHN0cmluZz4pDQo+Pj4gcm93cyA9IHBnX2N1
cnNvci5mZXRjaGFsbCgpDQo+PiBOb3RlIHRoYXQNCj4+ICgxKSBoZSBzYWlkIHRoYXQgaGUgZG9l
cyBub3Qgc2V0IGFuIGlzb2xhdGlvbiBsZXZlbCwgYW5kDQo+PiAoMikgaGUgZG9lcyBub3QgY2xv
c2UgdGhlIGRhdGFiYXNlIGNvbm5lY3Rpb24gYWZ0ZXIgdGhlDQo+PiBmZXRjaGFsbCAtIGluc3Rl
YWQgaGUgaGFzIGEgUHl0aG9uIHNsZWVwIGNvbW1hbmQsIHNvDQo+PiBoZSBpcyBjaGVja2luZyB0
aGUgZGF0YWJhc2UgZXZlcnkgNjAgcyB0byBzZWUgd2hldGhlcg0KPj4gbmV3IGVudHJpZXMgaGF2
ZSBiZWVuIGFkZGVkIHRvIGEgZ2l2ZW4gdGFibGUuIMKgKEhpcw0KPj4gY29kZSBpcyBwYXJ0IG9m
IHRoZSBhbmFseXNpcyBwaXBlbGluZSAtIHdlIHByb2Nlc3MgdGhlDQo+PiBpbWFnZSBkYXRhIGFu
ZCBsb2FkIGl0IGludG8gdGhlIGRhdGFiYXNlLCBhbmQgb3RoZXINCj4+IGdyb3VwcyBmZXRjaCB0
aGUgZGF0YSBmcm9tIHRoZSBkYXRhYmFzZSBhbmQgZG8gc29tZQ0KPj4gYW5hbHlzZXMuKQ0KPj4g
DQo+PiBZZXMsIGl0IGlzIHRoZSBjYXNlIHRoYXQgdGhlIHVzZXIncyBwcm9jZXNzIHNob3dzIHVw
IGluDQo+PiBwcyBhdXggYXMgImlkbGUgaW4gdHJhbnNhY3Rpb24iLg0KPj4gDQo+PiBXaGF0IHdv
dWxkIHlvdSByZWNvbW1lbmQgaW4gdGhpcyBjYXNlPyDCoFNob3VsZCB0aGUNCj4+IHVzZXIgc2V0
IHRoZSBpc29sYXRpb25fbGV2ZWwgZm9yIHBzeWNvcGcsIGFuZCBpZiBzbyB0byB3aGF0Pw0KPj4g
DQo+PiBJcyB0aGVyZSBhbnkgUG9zdGdyZXMgY29uZmlndXJhdGlvbiBwYXJhbWV0ZXIgdGhhdCBJ
DQo+PiBzaG91bGQgc2V0Pw0KPj4gDQo+PiBTaG91bGQgdGhlIHVzZXIgY2xvc2UgdGhlIGRhdGFi
YXNlIGNvbm5lY3Rpb24gYWZ0ZXINCj4+IGV2ZXJ5IGZldGNoYWxsPw0KPiANCj4gWW91IG5lZWQg
dG8gQ09NTUlUIG9yIFJPTExCQUNLIHRoZSBpbi1wcm9jZXNzIHRyYW5zYWN0aW9uIGFuZCB0aGVu
IG5vdA0KPiBzdGFydCBhIG5ldyB0cmFuc2FjdGlvbiB1bnRpbCB5b3UncmUgcmVhZHkgdG8gZXhl
Y3V0ZSB0aGUgbmV4dCBxdWVyeS4NCj4gUG9zc2libHkgY2FsbGluZyAuY29tbWl0KCkgYWZ0ZXIg
ZXhlY3V0aW5nIHlvdXIgcXVlcnkgbWlnaHQgYmUgYWxsIHlvdQ0KPiBuZWVkIHRvIGRvLCBidXQg
bmV2ZXIgaGF2aW5nIHVzZWQgcHN5Y29wZzIgSSBjb3VsZG4ndCBzYXkuICBZb3UgbWlnaHQNCj4g
dHJ5IGFza2luZyBvbiB0aGUgcHN5Y29wZyBtYWlsaW5nIGxpc3QuDQo+IA0KPiAuLi5Sb2JlcnQN
Cj4gDQoNCkluIGFkZGl0aW9uIHRvIHRoZSBhYm92ZSwgbm90ZSB0aGF0IGxvbmcgbGl2ZWQgdHJh
bnNhY3Rpb25zIGNhdXNlIGFsbCBzb3J0cw0Kb2Ygb3RoZXIgcHJvYmxlbXMgaW4gdGhlIGRhdGFi
YXNlLiAgSW4gcGFydGljdWxhciwgdGFibGUgYW5kIGluZGV4IGJsb2F0IGNhbg0KYmVjb21lIHNl
dmVyZSBkdWUgdG8gdGhpcyBzb3J0IG9mIHBvb3IgY2xpZW50IGJlaGF2aW9yIGlmIHRoZXJlIGlz
IGEgbG90IG9mDQp1cGRhdGUgb3IgZGVsZXRlIGFjdGl2aXR5LiAgWW91IGNhbiBmaW5kIG91dCB3
aXRoICJ2YWN1dW0gYW5hbHl6ZSB2ZXJib3NlIg0Kb24gdGFibGVzIG9mIGludGVyZXN0IHdoZXRo
ZXIgdGhlcmUgYXJlIGEgaGlnaCByYXRpbyBvZiBkZWFkIHR1cGxlcyBpbiB0aGUNCnRhYmxlcyBh
bmQgaW5kZXhlcy4NCg0KPiAtLQ0KPiBTZW50IHZpYSBwZ3NxbC1wZXJmb3JtYW5jZSBtYWlsaW5n
IGxpc3QgKHBnc3FsLXBlcmZvcm1hbmNlQHBvc3RncmVzcWwub3JnKQ0KPiBUbyBtYWtlIGNoYW5n
ZXMgdG8geW91ciBzdWJzY3JpcHRpb246DQo+IGh0dHA6Ly93d3cucG9zdGdyZXNxbC5vcmcvbWFp
bHByZWYvcGdzcWwtcGVyZm9ybWFuY2UNCj4gDQoNCg==