Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hqa4h-0006Fh-5E for pgsql-performance@arkaria.postgresql.org; Thu, 25 Jul 2019 09:30:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hqa4e-0008PE-7w for pgsql-performance@arkaria.postgresql.org; Thu, 25 Jul 2019 09:30:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hqa18-0001AC-8n for pgsql-performance@lists.postgresql.org; Thu, 25 Jul 2019 09:26:30 +0000 Received: from mail-wr1-x42e.google.com ([2a00:1450:4864:20::42e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hqa16-0007NA-J4 for pgsql-performance@lists.postgresql.org; Thu, 25 Jul 2019 09:26:29 +0000 Received: by mail-wr1-x42e.google.com with SMTP id c2so46745411wrm.8 for ; Thu, 25 Jul 2019 02:26:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=20161025; h=to:from:subject:openpgp:autocrypt:message-id:date:user-agent :mime-version:content-language:content-transfer-encoding; bh=Blrw8QK23XczRb7q+Od7krXrGDq/a0pCaLkHgG43GTc=; b=faz0lspgkTDUpF77szn3ggIl7WnWDdJ7I1nYNIeDM6B4VU5ap0pmYuQjFZKWtmKmhP qAe8dbbOIf8CzLeTAIgJyk3hUtnurLSdi8M/MeZDZ1rndAAJ3calQrXrb1/3JggT6ZY7 +bTpOTPYThQgDl/uWByM9pB4Z41WoB62R94wkUbd9IQMj5/6683299J0gkJHWDs11OVL Xd8T27Aw80jxfMbUhI0qGeEtcgFlPbglna6NRnN/jeJGOzdf4RUHnI6/FDvTfUJET0DD XTBv1qmwrANutF/oUSX1/Jh7EeZl9qafDF0Skd7UB9K5DlIQF/Oa0xghboFiwwloQVFH +8ww== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:to:from:subject:openpgp:autocrypt:message-id :date:user-agent:mime-version:content-language :content-transfer-encoding; bh=Blrw8QK23XczRb7q+Od7krXrGDq/a0pCaLkHgG43GTc=; b=HiFsTaajjRb99IEFlbci2L23z35sC8jwyu9Vd6At6ku/OXbsQtMBPbD6mZYVmQeoJC AcJA93A00EwJ4gRPuHtHoWEGnjJqe1KeTKrSdvhqHTEqkRtwl7tREmXw5hA5OCou0FKA dcbHs2ciB0FeUgNovnXXRCQuhyI5hr1i6MtVTv+K2uzzx3hEtPq9LuMAoUhpaEH4wvA/ lupLjB9m85rVgZQw6/AEGK/sVYKWqdubfq+Pm/P2NBKsKKWTN27SyUKVZjja3YLpLUt+ F2vtAxCv7vmZSFaxemNEn+fR+ei27PsZW9mA3wv7Fc04xFKRhGfBV0a4Q+eM427alKTS SWMA== X-Gm-Message-State: APjAAAUR5QCvBj8Iyg8cAQxo+09P5jSQVjJW1XQAVH2S5Tjuil91nB/p aW8bV+nWimoFa3gGYKA7mW1MC3MG X-Google-Smtp-Source: APXvYqy6EvbVy1V3smU7YFGrTLI6OzttsgGTCcecBgkCSdCGe0AJ3z2hjVZuSW5Dsusek5ClD4rALw== X-Received: by 2002:a05:6000:1203:: with SMTP id e3mr17104003wrx.300.1564046785083; Thu, 25 Jul 2019 02:26:25 -0700 (PDT) Received: from ?IPv6:2003:cd:ef25:1700:5d1d:4747:a18f:326a? (p200300CDEF2517005D1D4747A18F326A.dip0.t-ipconnect.de. [2003:cd:ef25:1700:5d1d:4747:a18f:326a]) by smtp.googlemail.com with ESMTPSA id x20sm108571160wrg.10.2019.07.25.02.26.24 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 25 Jul 2019 02:26:24 -0700 (PDT) To: pgsql-performance@lists.postgresql.org From: Ancoron Luciferis Subject: Standard uuid vs. custom data type uuid_v1 Openpgp: preference=signencrypt Autocrypt: addr=ancoron.luciferis@googlemail.com; prefer-encrypt=mutual; keydata= mQINBE3Z7bUBEAC3ZYDTnxOiu0JWJR+6XzFQmJEVncGMehyuE9GBfyaNYnsNKmFITkubNVB9 tN/xgn5FJ9eg0WediTaWXigHNDDOPSNHZGaf1nHu0AQNOgpEyJETtX/A56PXQ82+VwoKCNUN v8+m5CJ8njWZlZMCVA3AcQ1vR2VRR6O0pzEVPDCDMutPytkRl+XrPoPhxMTVEmwM4jfWwmfI NgiOZnsueU78VW8Go1qTN20YdVOdVpbPWfyp8VIGYBNjEEefv5f/l+zt4Z+CQMmvunDUdaKY 85opNIMLqC3dDY0AQG+FpcDLhHWLcuGLmE+Ghiix0G3kX4QX/fH0/2KtVNdfnZtcpq+5rim0 E97PVQe4EixVD/DivpwvNMWqQ2GJlOCx52+J13ZSfe/63XpjL7TNqhR3wMnNuMidLMgLoH5z RtQ2FH9zeYlKgqMUkmwvEHVI+M4oQRE0rzgqx6xHbkPIFEov9ZziQVwCtct+Ja9ho/BXoe/T VIFVOGEp7DG12QSaiLILhzTRUkDXqa+BH9H4V2Hsv77nT2JQ7KO9pM5gb4wq4gtLFo7MMTUq jlqjD2vOm3NY3tg1s7HTggVNgGsWhh9JlNR3v9SRGckKVIIvwDxvzo+Z4iji2SLElWb9BWiH Xku5IEuWGTmC+nOzSvbSxTaC53JcppC5kKXj11N73uw5eR4s3wARAQABtDRBbmNvcm9uIEx1 Y2lmZXJpcyA8YW5jb3Jvbi5sdWNpZmVyaXNAZ29vZ2xlbWFpbC5jb20+iQI4BBMBAgAiBQJN 2e21AhsDBgsJCAcDAgYVCAIJCgsEFgIDAQIeAQIXgAAKCRBzrR1GWXG7SN0DD/9qJR1Bsm52 W/T+XKQ0DApt3rCVgt9e5gvk4mUgtUIufIVOx3t/s2qw84VcSyNVclfoecLF0JdBWavobyCn JAIVkgGt0wGF6Kzsbiq7vlqDHDYiB7prvY4vj6bpmEQGYBBmWPG6/AVvBRJsvvaSGTqqdVth 4MYl43VxDZD+aVq52o0ROQAWyZC28kevTvRxusLuwZc4Q7HvOuAGNeD4aGLIgbOpyiS4mkC3 i2B++/cQ+zurzCWqCi1U/0sFXtwV2Qh6gjZK/TvZogTMUxjftZdvP49R5NrEB6AWx5ztp/RO 7Jy2j+Xm4OAteb5uGegzyoPKzr7B9O76GtfXLgLyZjpBIpuudosx+vJ9g7wuDHWXj7f5QKZQ PLqDO5SxI/Yi5tM4byag9/KZ7aHmeCXuAOnRTvKh6xMyB0fq6aPtpRlek4NcHWdT2SaR1y5K XxqWM/x7wLUyQo1lvjPTWX2MS70AjPJ5BdZzEjoA3zucT/ibu+CRjbyAOx5XwIBTnutj/+oI osslCFbMMj6n9NRoJidmy8q2nMYH+lMP1nTbdLCLesURVlTVLcMxIQhsjRZC/E6tu86C6rHB VrNjlUHkXHwJ9qQ3FbFOudqPUatYcfJSI7PmPbxWqNhcnVXlA1yndT7kAfWsmIxaJvDw09p5 dxkrNZi0LYoyLmMLImCb0yCHibkCDQRN2e21ARAAuQP7cntv+HFvl+3aUMZ68vTcV+0AfFfN DqZy6UYNbggIkuTtE5c5O88oBaJ/4idyAwzleb0Ess51xgYExv1zdxj61i+FfTOXEHhXp+uG Zk3mYVlp5juZwdEClrNkMTZehP6zvEvem5MkGGe3RBg7v4svg+xiaqaPMeN2Kj7KbObrlKcA F9A/4lRr5F0lH/RJaIaIUAgccVbRQdN1Z0JD6CU54sNlOCvkRqq1zNgmDSq/6WPs+WMkd0FT yMeyus9sPOmBrld4Wp6jHJSt7cYzq45F87b6oUPPVs4uSa1CN6IZZKk1rmQzuuFclTdw3Ari kjBKhFXe0gwsahPUYMOaICc62ivGKhHW/6HXh0JrPP9zqc/hMSY/fXffBXKAab4xnCA7/oUU 4W8/wD4qvq0w77i5bUJrHg+AhFEGDXWPfMCYjIE0oLTCYK696yhhVpVSFD8c3Uj1tSQERbBN 7qpDKGSOjRZyPLAH0yOAVgqteLkDhzwhqgwBhCXEOQAWqgvP2JI8cvbQYprG5uQnsS6TkYlq 1/nmmV+6HfMmitEp7siGaq0UmVZHBrGD+BQjuLkKPZ95YCSwc8xM/Y6viOIxvgisVRNXqo0r QW/FYXKnz0i7ZQTYE4Rad487mtd+eAcW1LhwBlM5dJRxx6Ee8dKkhjvQjap4MmghpNltULUF yGkAEQEAAYkCHwQYAQIACQUCTdnttQIbDAAKCRBzrR1GWXG7SOmfEACisSp8ZEfX6zVvzLNu YVXlvnQcd+w7VtN9+MMB3P9xZUku82/flaZTVvCbQ/kDRT9BrLdNcxM9dgt6Fgih49qN6+TI 2Atl+nBO9KtKaBc8wFs06VEEYkaIvtiS99KJO2wjQXKE1/Fj/HLKET62BctFFk5fgTdzHpxZ 2yovkQ+cPhkkyKpW7/UjFYn2Phpvg3KU1kcH/5oyGjRzCFTT0ERlyKWA/X/XxzX7+k2LVEh0 QzpuZbn/S0ldHDtP+fMoZZAHjVpWUu6A0zLYIHQkX/X4gkBvlWwvVuh27lDULJ8elrECyGOp 46G14r1t6bSnESm9W9AcvtG1pQQMv7zoHXJNgYew2cbkSxgBSjNNxnkUPIJdvX8Bel/iQzLr EXCaZ/myDJ4RNrzkvRBWdOWMvBkIV3yXogp6CyYzq5IaQgSIiv5Hr17ssuNR+FS5aZBocJru pCqLD/AdS3MZLBeRGmpDWkuj4uc4/4tLDIHcmCYia47FD2OuGSbLDz4BBeEmR37CkFsxIq6f eszCf1awGfZv7i0ddVFT3gtJTBoRE6VPkpcMrYaEjhtbrGI9PwB+7rNd4V9O6qPwV450KldB FwlHzOjWNRkfDUc3sgRB3QXlH3bVsHqUVBBTXf5YwqxeQtIRe/A7ICU3kLx8BKiUZXhOoTur jG7LftY+nte9Tt8CTQ== Message-ID: <2d21f58d-230b-27b9-e48c-dda23a258066@googlemail.com> Date: Thu, 25 Jul 2019 11:26:23 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.8.0 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi, I have finally found some time to implement a custom data type optimized for version 1 UUID's (timestamp, clock sequence, node): https://github.com/ancoron/pg-uuid-v1 Some tests (using a few millions of rows) have shown the following results (when used as a primary key): COPY ... FROM: ~7.8x faster (from file - SSD) COPY ... TO : ~1.5x faster (no where clause, sequential output) The best thing is that for INSERT's there is a very high chance of hitting the B-Tree "fastpath" because of the timestamp being the most significant part of the data type, which tends to be increasing. This also results in much lower "bloat", where the standard "uuid" type easily goes beyond 30%, the "uuid_v1" should be between 10 and 20%. Additionally, it also reveals the massive performance degrade I saw in my tests for standard UUID's: Initial 200 million rows: ~ 80k rows / second Additional 17 million rows: ~26k rows / second ...and the new data type: Initial 200 million rows: ~ 623k rows / second Additional 17 million rows: ~618k rows / second The data type also has functions to extract the three parts and has an additional set of operators to compare it to timestamps for time-series queries. Other test results which are of interest: ANALYZE: essentially equal with uuid_v1 to be just slightly faster VACUUM: ~4-5x faster REINDEX: only slightly faster (~10-20%) I think there's also something from it for a faster standard UUID implementation as a char array is not very compute friendly and conversion from or to strings (in/out) can be optimized. Cheers, Ancoron Ref: Latest test results: https://gist.github.com/ancoron/d5114b0907e8974b6808077e02f8d109