On 08/23/2016 08:34 AM, Francisco Olarte wrote:
> On Tue, Aug 23, 2016 at 4:28 PM, Rob Sargent <robjsargent@gmail.com> wrote:
>> On 08/23/2016 07:44 AM, Francisco Olarte wrote:
>>> On Tue, Aug 23, 2016 at 2:26 PM, pinker <pinker@onet.eu> wrote:
>>>> I am just surprised by the order of magnitude in the difference though. 2
>>>> and 27 minutes that's the huge difference... I did another, simplified
>>>> test,
>>>> to make sure there is no duplicates and the only difference between both
>>>> sets is the order:
>>> ...
>>>> INSERT INTO t_sequential SELECT * FROM source_sequential;
>>>> 102258,949 ms
>>>> INSERT INTO t_random SELECT * FROM source_random;
>>>> 1657575,699 ms
>>> If I read correctly, you are getting 100s/10Mkeys=10us/key in
>>> sequential, and 165 in random.
>>>
>>> I'm not surprissed at all. I've got greater differences on a memory
>>> tree, sorted insertion can be easily optimized to be very fast. AS an
>>> example, sequential insertion can easily avoid moving data while
>>> filling the pages and, with a little care, it can also avoid some of
>>> them when splitting. I'm not current with the current postgres
>>> details, but it does not surprise me they have big optimizations for
>>> this, especially when index ordered insertion is quite common in
>>> things like bulk loads or timestamped log lines.
>> And if each insert is in a separate transaction, does this still hold true?
> What are you referring to by 'this'? ( BTW, bear in mind one
> transaction needs at least a disk flush, and, if done via network, at
> least one RTT, so I doubt you can achieve 10us/transaction unless you
> have very special conditions ).
>
> Francisco Olarte.
By 'this' I was referring to the optimizations mentioned, and am
wondering if this holds true under user load. Much magic can happen in
a custom data load, but do these optimization apply to an application
loading single (or perhaps several) records per transaction. Does one,
in that scenario, not suffer any consequence for continuously loading
one side of the tree (the rightmost node?).