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 1hUaS9-0001Px-Oh for pgsql-performance@arkaria.postgresql.org; Sat, 25 May 2019 17:27:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hUaS8-0002uR-4q for pgsql-performance@arkaria.postgresql.org; Sat, 25 May 2019 17:27:28 +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 1hUaS7-0002uK-PP for pgsql-performance@lists.postgresql.org; Sat, 25 May 2019 17:27:27 +0000 Received: from mail-wm1-x329.google.com ([2a00:1450:4864:20::329]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hUaS4-0007ct-Qq for pgsql-performance@lists.postgresql.org; Sat, 25 May 2019 17:27:27 +0000 Received: by mail-wm1-x329.google.com with SMTP id t5so12009750wmh.3 for ; Sat, 25 May 2019 10:27:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=20161025; h=subject:to:references:from:openpgp:autocrypt:message-id:date :user-agent:mime-version:in-reply-to:content-language :content-transfer-encoding; bh=IeM0pO5LelYt3WFxLu6+cf2zpP/lNokRPCJ/NN6L7zg=; b=qsP9em33qbJM0zBxu01iDvGZUKsq6pIDvRstl6T7OlNfmDwMUZLcMcpdt+ZuiWsg4W QUIuTH3CyLeFswXMVKbSenLDfX9AXLBf5Byx5cyyFzhypcrHah23HReiSxG0dL/fMC6T LyraFhB8GZLCLeQ96ln30HATseqkK2m2+O2VbsfRuNUyNhKu/QeLE17Y5yVz0U0ePhGK JmUvGV9rTd5KwZkCm7NPYtfld+uZayKNi13+ETcmAujprRmbr8RhU33IzX2Ssv1bStS7 uTnRiPjCXQJIWmWA1WmiGpj81X8kHldJT59dzU4fQIYojqJ1JWif7PdyfU+QEkTsRAzu M4hg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:openpgp:autocrypt :message-id:date:user-agent:mime-version:in-reply-to :content-language:content-transfer-encoding; bh=IeM0pO5LelYt3WFxLu6+cf2zpP/lNokRPCJ/NN6L7zg=; b=nbM/sVpnFI2HYq27SlHHnHu1tkhptZBk1+SEeH27EshNIBFFBO1XdqIZ5gSfCO7kXd qI92VravxAV1uo901N89nB+SM4o/msvbXTUrHQVWOsSGxl2yvMpon9dkUHEsCGyUe5eN Va27vEu8ebi3tK3m9ToOKf1sX73nZVt1Zpm/OlpmF80/OgrcbfKh0oFx9y6HvuCEiG/i RjvCfG6vD8bHXqgR+VrStkLl6gqO9wd0lgc9K9RZKjmpNzFhJ1n9atgRl76q4F86N+Da 16sespfzhDUmxuiEKVFXx1TbwGb8MF8qjl9YvcHiKVGTKG/NQPgQib4pdMQESC77YoR3 CG4w== X-Gm-Message-State: APjAAAU9OV52g8R8swoGjGDEgseiJo6uHJYfj9y1ZCN3793VGlB0GBHM saQOSpjvMK13szmP/5/Gb3EczOlr X-Google-Smtp-Source: APXvYqyuaAZDNLwmg11xauJMA8/vtdeICeQOBGzcTzAYT8JBNTc2tc9yhFhB2X9E9Oaq7gv5EhaRww== X-Received: by 2002:a1c:9dc7:: with SMTP id g190mr19788062wme.121.1558805242978; Sat, 25 May 2019 10:27:22 -0700 (PDT) Received: from [192.168.66.78] (pD9582420.dip0.t-ipconnect.de. [217.88.36.32]) by smtp.googlemail.com with ESMTPSA id o6sm14938230wrh.55.2019.05.25.10.27.22 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Sat, 25 May 2019 10:27:22 -0700 (PDT) Subject: Re: UUID v1 optimizations... To: Peter Eisentraut , pgsql-performance@lists.postgresql.org References: <8d5f4b67-d540-8d3d-9979-808b9c1e1ca7@googlemail.com> <6099cb7d-3507-9b01-433d-dc709ecec8ed@2ndquadrant.com> From: Ancoron Luciferis 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: Date: Sat, 25 May 2019 19:27:21 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.6.1 MIME-Version: 1.0 In-Reply-To: <6099cb7d-3507-9b01-433d-dc709ecec8ed@2ndquadrant.com> Content-Type: text/plain; charset=utf-8 Content-Language: de-DE Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 25/05/2019 16:19, Peter Eisentraut wrote: > On 2019-05-25 15:45, Ancoron Luciferis wrote: >> So, my question now is: Would it make sense for you to handle these >> time-based UUID's differently internally? Specifically un-shuffling the >> timestamp before they are going to storage? > > It seems unlikely that we would do that, because that would break > existing stored UUIDs, and we'd also need to figure out a way to store > non-v1 UUIDs under this different scheme. The use case is pretty narrow > compared to the enormous effort. I agree, the backwards compatibility really would be a show stopper here. About the "enormous effort" I was thinking the simplest "solution" would be to have the version being detected at he time when the internal byte array is created from the provided representation which then could directly provide the unshuffled byte order. > > It is well understood that using UUIDs or other somewhat-random keys > perform worse than serial-like keys. Yes I know. We're using these UUID's for more than just some primary key, because they also tell use the creation time of the entry and which "node" of the highly distributed application generated the entry. It is like an audit-log for us without the need for extra columns and of fixed size, which helps performance also at the application level. > > Btw., it might be nice to rerun your tests with PostgreSQL 12beta1. The > btree storage has gotten some improvements. I don't think it's going to > fundamentally solve your problem, but it would be useful feedback. > Thank you for the pointer to 12beta1. I've just read about it and it might help a bit. I'll give it a try, for sure and report back. I also have to rerun those tests against PG 11 anyway. Cheers, Ancoron