Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1luHGl-0003Xt-VO for pgsql-hackers@arkaria.postgresql.org; Fri, 18 Jun 2021 16:23:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1luHGj-0000x2-61 for pgsql-hackers@arkaria.postgresql.org; Fri, 18 Jun 2021 16:22:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1luHGi-0000wu-Po for pgsql-hackers@lists.postgresql.org; Fri, 18 Jun 2021 16:22:56 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1luHGf-0008O2-F8 for pgsql-hackers@lists.postgresql.org; Fri, 18 Jun 2021 16:22:56 +0000 Received: from Egors-MacBook-Pro.local (unknown [46.188.123.252]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 1205F21C82A9 for ; Fri, 18 Jun 2021 19:22:52 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1624033372; bh=k78aQi4wcNEJi4wcX/Fiyp33Lzb7CWAe1CxPI9pUfds=; h=From:Subject:To:Date; b=BHHq54gdRnU8qlxvdezHtbBb/q+HBCC2cKx1S9Divl+a03DDfPr8svRR8DVfGMz3B anACGBVVQmT1VRFqisipAAMFCqwvIo+/LMC4bWlJoPYdi7zLP7BjLqykmxn25D7XEf 1EJ0gULjZ4np5S2FCtjgAqGIi1KuBKMlJK6xtAqc= From: Egor Rogov Subject: pg_stats and range statistics To: pgsql-hackers@lists.postgresql.org Message-ID: Date: Fri, 18 Jun 2021 19:22:51 +0300 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:78.0) Gecko/20100101 Thunderbird/78.6.1 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="------------626DE80AF6986DCEA52A6A07" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------626DE80AF6986DCEA52A6A07 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Hi, Statistics for range types are not currently exposed in pg_stats view (i.e. STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM and STATISTIC_KIND_BOUNDS_HISTOGRAM). Shouldn't they? If so, here is a patch for adding them. The following is a simple example of what it looks like: CREATE TABLE test(r int4range); INSERT INTO test     SELECT int4range((random()*10)::integer,(10+random()*10)::integer)     FROM generate_series(1,10000); SET default_statistics_target = 10; ANALYZE test; SELECT range_length_histogram, range_length_empty_frac, range_bounds_histogram FROM pg_stats WHERE tablename = 'test' \gx -[ RECORD 1 ]-----------+------------------------------------------------------------------------------------------------------ range_length_histogram  | {1,4,6,8,9,10,11,12,14,16,20} range_length_empty_frac | {0.0036666666} range_bounds_histogram  | {"[0,10)","[1,11)","[2,12)","[3,13)","[4,14)","[5,15)","[6,16)","[7,17)","[8,18)","[9,19)","[10,20)"} Regards, Egor Rogov. --------------626DE80AF6986DCEA52A6A07 Content-Type: text/plain; charset=UTF-8; x-mac-type="0"; x-mac-creator="0"; name="pgstats.patch" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="pgstats.patch" ZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9jYXRhbG9ncy5zZ21sIGIvZG9jL3NyYy9zZ21s L2NhdGFsb2dzLnNnbWwKaW5kZXggZjUxN2E3ZDRhZi4uNGQwMzdiNTkwZSAxMDA2NDQKLS0t IGEvZG9jL3NyYy9zZ21sL2NhdGFsb2dzLnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL2NhdGFs b2dzLnNnbWwKQEAgLTEyODc3LDYgKzEyODc3LDM2IEBAIFNFTEVDVCAqIEZST00gcGdfbG9j a3MgcGwgTEVGVCBKT0lOIHBnX3ByZXBhcmVkX3hhY3RzIHBweAogICAgICAgIG5vbi1udWxs IGVsZW1lbnRzLiAgKE51bGwgZm9yIHNjYWxhciB0eXBlcy4pCiAgICAgICA8L3BhcmE+PC9l bnRyeT4KICAgICAgPC9yb3c+CisKKyAgICAgPHJvdz4KKyAgICAgIDxlbnRyeSByb2xlPSJj YXRhbG9nX3RhYmxlX2VudHJ5Ij48cGFyYSByb2xlPSJjb2x1bW5fZGVmaW5pdGlvbiI+Cisg ICAgICAgPHN0cnVjdGZpZWxkPnJhbmdlX2xlbmd0aF9oaXN0b2dyYW08L3N0cnVjdGZpZWxk PiA8dHlwZT5hbnlhcnJheTwvdHlwZT4KKyAgICAgIDwvcGFyYT4KKyAgICAgIDxwYXJhPgor ICAgICAgIEEgaGlzdG9ncmFtIG9mIGxlbmd0aHMgb2Ygbm9uLWVtcHR5LCBub24tbnVsbCBy YW5nZXMuCisgICAgICAgKE51bGwgZm9yIG5vbi1yYW5nZSB0eXBlcy4pCisgICAgICA8L3Bh cmE+PC9lbnRyeT4KKyAgICAgPC9yb3c+CisKKyAgICAgPHJvdz4KKyAgICAgIDxlbnRyeSBy b2xlPSJjYXRhbG9nX3RhYmxlX2VudHJ5Ij48cGFyYSByb2xlPSJjb2x1bW5fZGVmaW5pdGlv biI+CisgICAgICAgPHN0cnVjdGZpZWxkPnJhbmdlX2xlbmd0aF9lbXB0eV9mcmFjPC9zdHJ1 Y3RmaWVsZD4gPHR5cGU+ZmxvYXQ0PC90eXBlPgorICAgICAgPC9wYXJhPgorICAgICAgPHBh cmE+CisgICAgICAgQSBmcmFjdGlvbiBvZiBlbXB0eSByYW5nZXMuICAoTnVsbCBmb3Igbm9u LXJhbmdlIHR5cGVzLikKKyAgICAgIDwvcGFyYT48L2VudHJ5PgorICAgICA8L3Jvdz4KKwor ICAgICA8cm93PgorICAgICAgPGVudHJ5IHJvbGU9ImNhdGFsb2dfdGFibGVfZW50cnkiPjxw YXJhIHJvbGU9ImNvbHVtbl9kZWZpbml0aW9uIj4KKyAgICAgICA8c3RydWN0ZmllbGQ+cmFu Z2VfYm91bmRzX2hpc3RvZ3JhbTwvc3RydWN0ZmllbGQ+IDx0eXBlPmFueWFycmF5PC90eXBl PgorICAgICAgPC9wYXJhPgorICAgICAgPHBhcmE+CisgICAgICAgSGlzdG9ncmFtcyBvZiBs b3dlciBhbmQgdXBwZXIgYm91bmRzIG9mIG5vbi1lbXB0eSwgbm9uLW51bGwgcmFuZ2VzLAor ICAgICAgIGNvbWJpbmVkIGludG8gYSBzaW5nbGUgYXJyYXkgb2YgcmFuZ2UgdmFsdWVzLgor ICAgICAgIChOdWxsIGZvciBub24tcmFuZ2UgdHlwZXMuKQorICAgICAgPC9wYXJhPjwvZW50 cnk+CisgICAgIDwvcm93PgogICAgIDwvdGJvZHk+CiAgICA8L3Rncm91cD4KICAgPC90YWJs ZT4KZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NhdGFsb2cvc3lzdGVtX3ZpZXdzLnNxbCBi L3NyYy9iYWNrZW5kL2NhdGFsb2cvc3lzdGVtX3ZpZXdzLnNxbAppbmRleCA5OTlkOTg0MDY4 Li45M2ZhZGZmZjE1IDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9jYXRhbG9nL3N5c3RlbV92 aWV3cy5zcWwKKysrIGIvc3JjL2JhY2tlbmQvY2F0YWxvZy9zeXN0ZW1fdmlld3Muc3FsCkBA IC0yNDMsNyArMjQzLDI4IEBAIENSRUFURSBWSUVXIHBnX3N0YXRzIFdJVEggKHNlY3VyaXR5 X2JhcnJpZXIpIEFTCiAgICAgICAgICAgICBXSEVOIHN0YWtpbmQzID0gNSBUSEVOIHN0YW51 bWJlcnMzCiAgICAgICAgICAgICBXSEVOIHN0YWtpbmQ0ID0gNSBUSEVOIHN0YW51bWJlcnM0 CiAgICAgICAgICAgICBXSEVOIHN0YWtpbmQ1ID0gNSBUSEVOIHN0YW51bWJlcnM1Ci0gICAg ICAgIEVORCBBUyBlbGVtX2NvdW50X2hpc3RvZ3JhbQorICAgICAgICBFTkQgQVMgZWxlbV9j b3VudF9oaXN0b2dyYW0sCisgICAgICAgIENBU0UKKyAgICAgICAgICAgIFdIRU4gc3Rha2lu ZDEgPSA2IFRIRU4gc3RhdmFsdWVzMQorICAgICAgICAgICAgV0hFTiBzdGFraW5kMiA9IDYg VEhFTiBzdGF2YWx1ZXMyCisgICAgICAgICAgICBXSEVOIHN0YWtpbmQzID0gNiBUSEVOIHN0 YXZhbHVlczMKKyAgICAgICAgICAgIFdIRU4gc3Rha2luZDQgPSA2IFRIRU4gc3RhdmFsdWVz NAorICAgICAgICAgICAgV0hFTiBzdGFraW5kNSA9IDYgVEhFTiBzdGF2YWx1ZXM1CisgICAg ICAgIEVORCBBUyByYW5nZV9sZW5ndGhfaGlzdG9ncmFtLAorICAgICAgICBDQVNFCisgICAg ICAgICAgICBXSEVOIHN0YWtpbmQxID0gNiBUSEVOIHN0YW51bWJlcnMxWzFdCisgICAgICAg ICAgICBXSEVOIHN0YWtpbmQyID0gNiBUSEVOIHN0YW51bWJlcnMyWzFdCisgICAgICAgICAg ICBXSEVOIHN0YWtpbmQzID0gNiBUSEVOIHN0YW51bWJlcnMzWzFdCisgICAgICAgICAgICBX SEVOIHN0YWtpbmQ0ID0gNiBUSEVOIHN0YW51bWJlcnM0WzFdCisgICAgICAgICAgICBXSEVO IHN0YWtpbmQ1ID0gNiBUSEVOIHN0YW51bWJlcnM1WzFdCisgICAgICAgIEVORCBBUyByYW5n ZV9sZW5ndGhfZW1wdHlfZnJhYywKKyAgICAgICAgQ0FTRQorICAgICAgICAgICAgV0hFTiBz dGFraW5kMSA9IDcgVEhFTiBzdGF2YWx1ZXMxCisgICAgICAgICAgICBXSEVOIHN0YWtpbmQy ID0gNyBUSEVOIHN0YXZhbHVlczIKKyAgICAgICAgICAgIFdIRU4gc3Rha2luZDMgPSA3IFRI RU4gc3RhdmFsdWVzMworICAgICAgICAgICAgV0hFTiBzdGFraW5kNCA9IDcgVEhFTiBzdGF2 YWx1ZXM0CisgICAgICAgICAgICBXSEVOIHN0YWtpbmQ1ID0gNyBUSEVOIHN0YXZhbHVlczUK KyAgICAgICAgRU5EIEFTIHJhbmdlX2JvdW5kc19oaXN0b2dyYW0KICAgICBGUk9NIHBnX3N0 YXRpc3RpYyBzIEpPSU4gcGdfY2xhc3MgYyBPTiAoYy5vaWQgPSBzLnN0YXJlbGlkKQogICAg ICAgICAgSk9JTiBwZ19hdHRyaWJ1dGUgYSBPTiAoYy5vaWQgPSBhdHRyZWxpZCBBTkQgYXR0 bnVtID0gcy5zdGFhdHRudW0pCiAgICAgICAgICBMRUZUIEpPSU4gcGdfbmFtZXNwYWNlIG4g T04gKG4ub2lkID0gYy5yZWxuYW1lc3BhY2UpCmRpZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdy ZXNzL2V4cGVjdGVkL3J1bGVzLm91dCBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvcnVs ZXMub3V0CmluZGV4IGU1YWIxMTI3NWQuLjc4MGFlZmRjMjYgMTAwNjQ0Ci0tLSBhL3NyYy90 ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvcnVsZXMub3V0CisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3Mv ZXhwZWN0ZWQvcnVsZXMub3V0CkBAIC0yNDA5LDcgKzI0MDksMzEgQEAgcGdfc3RhdHN8IFNF TEVDVCBuLm5zcG5hbWUgQVMgc2NoZW1hbmFtZSwKICAgICAgICAgICAgIFdIRU4gKHMuc3Rh a2luZDQgPSA1KSBUSEVOIHMuc3RhbnVtYmVyczQKICAgICAgICAgICAgIFdIRU4gKHMuc3Rh a2luZDUgPSA1KSBUSEVOIHMuc3RhbnVtYmVyczUKICAgICAgICAgICAgIEVMU0UgTlVMTDo6 cmVhbFtdCi0gICAgICAgIEVORCBBUyBlbGVtX2NvdW50X2hpc3RvZ3JhbQorICAgICAgICBF TkQgQVMgZWxlbV9jb3VudF9oaXN0b2dyYW0sCisgICAgICAgIENBU0UKKyAgICAgICAgICAg IFdIRU4gKHMuc3Rha2luZDEgPSA2KSBUSEVOIHMuc3RhdmFsdWVzMQorICAgICAgICAgICAg V0hFTiAocy5zdGFraW5kMiA9IDYpIFRIRU4gcy5zdGF2YWx1ZXMyCisgICAgICAgICAgICBX SEVOIChzLnN0YWtpbmQzID0gNikgVEhFTiBzLnN0YXZhbHVlczMKKyAgICAgICAgICAgIFdI RU4gKHMuc3Rha2luZDQgPSA2KSBUSEVOIHMuc3RhdmFsdWVzNAorICAgICAgICAgICAgV0hF TiAocy5zdGFraW5kNSA9IDYpIFRIRU4gcy5zdGF2YWx1ZXM1CisgICAgICAgICAgICBFTFNF IE5VTEw6OmFueWFycmF5CisgICAgICAgIEVORCBBUyByYW5nZV9sZW5ndGhfaGlzdG9ncmFt LAorICAgICAgICBDQVNFCisgICAgICAgICAgICBXSEVOIChzLnN0YWtpbmQxID0gNikgVEhF TiBzLnN0YW51bWJlcnMxWzFdCisgICAgICAgICAgICBXSEVOIChzLnN0YWtpbmQyID0gNikg VEhFTiBzLnN0YW51bWJlcnMyWzFdCisgICAgICAgICAgICBXSEVOIChzLnN0YWtpbmQzID0g NikgVEhFTiBzLnN0YW51bWJlcnMzWzFdCisgICAgICAgICAgICBXSEVOIChzLnN0YWtpbmQ0 ID0gNikgVEhFTiBzLnN0YW51bWJlcnM0WzFdCisgICAgICAgICAgICBXSEVOIChzLnN0YWtp bmQ1ID0gNikgVEhFTiBzLnN0YW51bWJlcnM1WzFdCisgICAgICAgICAgICBFTFNFIE5VTEw6 OnJlYWwKKyAgICAgICAgRU5EIEFTIHJhbmdlX2xlbmd0aF9lbXB0eV9mcmFjLAorICAgICAg ICBDQVNFCisgICAgICAgICAgICBXSEVOIChzLnN0YWtpbmQxID0gNykgVEhFTiBzLnN0YXZh bHVlczEKKyAgICAgICAgICAgIFdIRU4gKHMuc3Rha2luZDIgPSA3KSBUSEVOIHMuc3RhdmFs dWVzMgorICAgICAgICAgICAgV0hFTiAocy5zdGFraW5kMyA9IDcpIFRIRU4gcy5zdGF2YWx1 ZXMzCisgICAgICAgICAgICBXSEVOIChzLnN0YWtpbmQ0ID0gNykgVEhFTiBzLnN0YXZhbHVl czQKKyAgICAgICAgICAgIFdIRU4gKHMuc3Rha2luZDUgPSA3KSBUSEVOIHMuc3RhdmFsdWVz NQorICAgICAgICAgICAgRUxTRSBOVUxMOjphbnlhcnJheQorICAgICAgICBFTkQgQVMgcmFu Z2VfYm91bmRzX2hpc3RvZ3JhbQogICAgRlJPTSAoKChwZ19zdGF0aXN0aWMgcwogICAgICBK T0lOIHBnX2NsYXNzIGMgT04gKChjLm9pZCA9IHMuc3RhcmVsaWQpKSkKICAgICAgSk9JTiBw Z19hdHRyaWJ1dGUgYSBPTiAoKChjLm9pZCA9IGEuYXR0cmVsaWQpIEFORCAoYS5hdHRudW0g PSBzLnN0YWF0dG51bSkpKSkK --------------626DE80AF6986DCEA52A6A07--