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 1hkyPl-0003KE-QG for pgsql-performance@arkaria.postgresql.org; Tue, 09 Jul 2019 22:16:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hkyPk-0006OM-6i for pgsql-performance@arkaria.postgresql.org; Tue, 09 Jul 2019 22:16:44 +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 1hkyPj-0006OF-Rr for pgsql-performance@lists.postgresql.org; Tue, 09 Jul 2019 22:16:43 +0000 Received: from mail-wm1-x331.google.com ([2a00:1450:4864:20::331]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hkyPh-0003wi-HR for pgsql-performance@lists.postgresql.org; Tue, 09 Jul 2019 22:16:43 +0000 Received: by mail-wm1-x331.google.com with SMTP id g67so330250wme.1 for ; Tue, 09 Jul 2019 15:16:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=20161025; h=subject:to:cc:references:from:openpgp:autocrypt:message-id:date :user-agent:mime-version:in-reply-to:content-language :content-transfer-encoding; bh=5cvLRxBNfTk9gmNdyv42bxMLJrIHsQysMxLjh8Nls2o=; b=XHkYyIkYtGd1DzUl1Zwwfk1/GL5zoT88XBPjytxmnLZWxnVWqmal39YVUraEZbk/vv 8Ek0AJxGZHif+M/ZWm4/tfds4rdww3e4i4g2x1bU1gov+1rcVvLXi6ibIw80jYkhBroS 1WI1OO7AtrqpOJsjEv/ltpIkPXUriRAd23SV8dCV7OUIIjWpyoRdC1VXYA76SATql/DY VrgVKH/7SvrCYCx7Gp8Hf9GWgfRb1wxb/r4evhB4fSyHFsyRrVUxbQ1Z32R79QcgIoD5 aMVkhGGDKzy9N5jYBMIx9KWsqXZOKH4DpfgE6NPQ3NK1cecWY7MO0WNycEacPJn5Pjfa gmfQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:cc:references:from:openpgp:autocrypt :message-id:date:user-agent:mime-version:in-reply-to :content-language:content-transfer-encoding; bh=5cvLRxBNfTk9gmNdyv42bxMLJrIHsQysMxLjh8Nls2o=; b=aw44RpnOuYVu0sARG5J0WBO3YfPR7rK3AAew4rm2/LSabIfy6714SMPoBC61LYxB32 l+Hucf1Ikhl6r1sAf7Xi1p62Z5VwH/PUrGp5CPBwHqhmRt4PphC0UlcL/xxXTHlybVwn vZlcVfUnfpV0yuIMKrkavTDMote5/0JyQaqcGivHwnT3jYTjO0Yx6DAP8QOI0lMc3DI4 QRjG3ZtQb8TFrJziez6QcKafdAV464REroEooTlSFGlwLndtBFyGYK6G6ogXsnlZJBR+ qRiCPVQ+kW/0i+siuzMytW4a+kPl0u8nRUcX8BN9+nUlwerVJoVIH6uks8NlQw2jDfxD uidQ== X-Gm-Message-State: APjAAAUOYBGvoixjY1yxdNI93XJrPj3VIR6inDZtVzS+cFaqQdlIeVK7 7MQrdY50DOS5PVSLPxW2giV6ftgA X-Google-Smtp-Source: APXvYqwORT7qgQh9XaPmx4RhxUX102cSFLPDz71EMeQOPLlScox+huE090vO/whBRQVH6ltVwRb4bA== X-Received: by 2002:a7b:cc04:: with SMTP id f4mr1611519wmh.125.1562710600123; Tue, 09 Jul 2019 15:16:40 -0700 (PDT) Received: from ?IPv6:2003:e9:ef1e:eb00:5cdc:22e8:e6d0:1efd? (p200300E9EF1EEB005CDC22E8E6D01EFD.dip0.t-ipconnect.de. [2003:e9:ef1e:eb00:5cdc:22e8:e6d0:1efd]) by smtp.googlemail.com with ESMTPSA id u2sm3747309wmc.3.2019.07.09.15.16.39 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 09 Jul 2019 15:16:39 -0700 (PDT) Subject: Re: Custom opclass for column statistics? To: Tom Lane Cc: pgsql-performance@lists.postgresql.org References: <26689.1562421538@sss.pgh.pa.us> 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: Wed, 10 Jul 2019 00:16:38 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.7.1 MIME-Version: 1.0 In-Reply-To: <26689.1562421538@sss.pgh.pa.us> 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 On 06/07/2019 15:58, Tom Lane wrote: > Ancoron Luciferis writes: >> I've been wondering whether it is possible somehow to have the standard >> column statistics to respect a certain operator class? > > In principle, pg_statistic can represent stats for a non-default opclass. > Teaching ANALYZE to collect such stats when appropriate, and then teaching > the planner to use them when appropriate, is left as an exercise for the > reader. Hehe, now that you are saying it, I realize what I was actually asking for with this... ;) > > I think the "when appropriate" bit is actually the hardest part of that. > Possibly, if you were satisfied with a relatively manual approach, > you could proceed by using CREATE STATISTICS to declare interest in > keeping standard stats for a non-default sort order. Not sure what to > do if you want it to be automatic, because I don't think people would > hold still for having ANALYZE collect stats for any random non-default > opclass automatically. Maybe a new opclass property? I totally agree with the complications around all that. Now I think if I want better statistics and better plans for my new time-sorted index, I will need a new data type for which I can set the opclass as default, which also would provide users the guarantee that they'll get what they expect. Thanx and cheers, Ancoron