Re: Slowdown problem when writing 1.7million records

"Stephen Livesey" <ste(at)exact3ex(dot)co(dot)uk> writes:
>> Further question --- is there any particular pattern to the order in
>> which you are inserting the records? For example, are they in order
>> by postcode, or approximately so?
>
> Yes they would be inserted in postcode order.

Ah. What must be happening is that the index on postcode is getting
badly misbalanced --- instead of a roughly symmetrical b-tree, all the
branches link to the right, causing index insertions and lookups to scan
the whole index instead of only an O(log N) portion of it. You'd get
better results if you inserted the data in random order, or dropped the
index while inserting the data and then recreated it after the bulk
loading is done.

I am not sure how difficult this behavior might be to change, but in any
case I'm not going to risk twiddling the btree code at this late stage
of the 7.1 release cycle. Bruce, would you add a TODO item?

* Be smarter about insertion of already-ordered data into btree index

regards, tom lane