On Tue, Aug 23, 2016 at 4:28 PM, Rob Sargent <robjsargent@xxxxxxxxx> wrote: > On 08/23/2016 07:44 AM, Francisco Olarte wrote: >> On Tue, Aug 23, 2016 at 2:26 PM, pinker <pinker@xxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
