Re: Performance die when COPYing to table with bigint PK

Robert Ayrapetyan <robert.ayrapetyan@comodo.com> wrote:
> Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:

>> What timings do you get for the insert statements if you run the
>> following in your environment?

>> Here's what I get:
>>
>> Time: 1629.141 ms
>> Time: 1638.060 ms
>> Time: 1711.833 ms
>>
>> Time: 4151.953 ms
>> Time: 4602.679 ms
>> Time: 5107.259 ms
>>
>> Time: 4654.060 ms
>> Time: 5158.157 ms
>> Time: 5101.110 ms

> Timings for your test:

> [no index]
> Time: 2789.607 ms
> Time: 2959.679 ms
> Time: 3651.206 ms

> [int index]
> Time: 5671.883 ms
> Time: 5668.894 ms
> Time: 6530.010 ms

> [bigint index]
> Time: 8093.276 ms
> Time: 5988.694 ms
> Time: 5702.236 ms

> [regarding tests which do show the problem]
> tried same with 2 columns (bigint and int) - it didn't produced
> such effect probably because data volume has critical effect.

Based on what you're showing, this is almost certainly just a matter
of pushing your volume of active data above the threshold of what
your cache holds, forcing it to do disk access rather than RAM
access for a significant portion of the reads.

-Kevin