Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tUki0-002Nlr-87 for pgsql-hackers@arkaria.postgresql.org; Mon, 06 Jan 2025 10:51:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tUkh0-007YSl-2m for pgsql-hackers@arkaria.postgresql.org; Mon, 06 Jan 2025 10:50:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tUkgz-007YSa-MV for pgsql-hackers@lists.postgresql.org; Mon, 06 Jan 2025 10:50:41 +0000 Received: from forward103a.mail.yandex.net ([2a02:6b8:c0e:500:1:45:d181:d103]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tUkgu-0009Jo-2w for pgsql-hackers@postgresql.org; Mon, 06 Jan 2025 10:50:39 +0000 Received: from mail-nwsmtp-smtp-production-main-77.iva.yp-c.yandex.net (mail-nwsmtp-smtp-production-main-77.iva.yp-c.yandex.net [IPv6:2a02:6b8:c0c:108e:0:640:8d65:0]) by forward103a.mail.yandex.net (Yandex) with ESMTPS id AAAAA60D14; Mon, 6 Jan 2025 13:50:34 +0300 (MSK) Received: by mail-nwsmtp-smtp-production-main-77.iva.yp-c.yandex.net (smtp/Yandex) with ESMTPSA id Vohng6JMhOs0-GjdFFINP; Mon, 06 Jan 2025 13:50:33 +0300 X-Yandex-Fwd: 1 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tantorlabs.com; s=mail; t=1736160634; bh=SwBqnik+8ItZcGin4wDkkRvZYBcteRV1rdygpYxAEuQ=; h=In-Reply-To:Cc:Date:References:To:Subject:Message-ID:From; b=NP7s85qYffCpvNN988lzpsm56JtshlRrEaBBb41lpMpSGFJi84ll8J0yRVrc/dG3/ 7N9iJBhI4/BYm1ugdqldkcp6H6KYnbSjL5LAhMeeZsL9v3uG317FcipQF0lW7mNqUQ RFppDXrYL+Ju1HnSYgdJpMRFeAnRG2awp7WwFg9A= Authentication-Results: mail-nwsmtp-smtp-production-main-77.iva.yp-c.yandex.net; dkim=pass header.i=@tantorlabs.com Content-Type: multipart/mixed; boundary="------------V1jPju41ETIr5CY201cbGsvS" Message-ID: <52e5e3aa-066a-4daf-8d65-77c34a2f0d98@tantorlabs.com> Date: Mon, 6 Jan 2025 13:50:31 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Sample rate added to pg_stat_statements From: Ilia Evdokimov To: Alexander Korotkov , Michael Paquier , "Andrey M. Borodin" , Greg Sabino Mullane Cc: pgsql-hackers References: <6707FCA9-FD1A-4609-A1A6-142456C14E0C@yandex-team.ru> <320a31e6-4c91-438b-ab17-8a1d72384727@tantorlabs.com> Content-Language: en-US In-Reply-To: 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. --------------V1jPju41ETIr5CY201cbGsvS Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10.12.2024 17:35, Ilia Evdokimov wrote: > Hi everyone, > > I attached previous sampling patch for pg_stat_statements (v4). > Suggestions like sampling based on execution time remain unfeasible, > as pg_stat_statements can track query during query planning, not > execution. > > To evaluate the implementation, I ran a benchmark creating 1,000 > random tables and executing randomized JOIN queries  on a small > machine. When pg_stat_statements enabled, performance decreases, but > reducing the sampling rate helps mitigate the impact and improves > performance. > > I’d be interested in hearing your new thoughts. Are there areas where > this patch could be improved, or other ideas worth exploring? > > -- > Best regards. > Ilia Evdokimov, > Tantor Labs LLC. I've fixed some small typos in the documentation and in the GUC description in the attached patch. Any suggestions for improvements? -- Best regards, Ilia Evdokimov, Tantor Labs LLC. --------------V1jPju41ETIr5CY201cbGsvS Content-Type: text/x-patch; charset=UTF-8; name="v6-0001-Allow-setting-sample-ratio-for-pg_stat_statements.patch" Content-Disposition: attachment; filename*0="v6-0001-Allow-setting-sample-ratio-for-pg_stat_statements.pa"; filename*1="tch" Content-Transfer-Encoding: base64 RnJvbSBlMDk1NWIxMmU2YTcxZWZkNmZjNWQ0MDZlM2Q5M2JkYTRlYzU3YmJmIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBJbGlhIEV2ZG9raW1vdiA8aWx5YS5ldmRva2ltb3ZA dGFudG9ybGFicy5jb20+CkRhdGU6IE1vbiwgNiBKYW4gMjAyNSAxMzozODoyMiArMDMwMApT dWJqZWN0OiBbUEFUQ0hdIEFsbG93IHNldHRpbmcgc2FtcGxlIHJhdGlvIGZvciBwZ19zdGF0 X3N0YXRlbWVudHMKCk5ldyBjb25maWd1cmF0aW9uIHBhcmFtZXRlciBwZ19zdGF0X3N0YXRl bWVudHMuc2FtcGxlX3JhdGlvIG1ha2VzIGl0CnBvc3NpYmxlIHRvIHRyYWNrIGp1c3QgYSBm cmFjdGlvbiBvZiB0aGUgcXVlcmllcyBtZWV0aW5nIHRoZSBjb25maWd1cmVkCnRocmVzaG9s ZCwgdG8gcmVkdWNlIHRoZSBhbW91bnQgb2YgdHJhY2tpbmcuCi0tLQogLi4uL3BnX3N0YXRf c3RhdGVtZW50cy9wZ19zdGF0X3N0YXRlbWVudHMuYyAgIHwgMjMgKysrKysrKysrKysrKysr KysrLQogZG9jL3NyYy9zZ21sL3Bnc3RhdHN0YXRlbWVudHMuc2dtbCAgICAgICAgICAgIHwg MTggKysrKysrKysrKysrKysrCiAyIGZpbGVzIGNoYW5nZWQsIDQwIGluc2VydGlvbnMoKyks IDEgZGVsZXRpb24oLSkKCmRpZmYgLS1naXQgYS9jb250cmliL3BnX3N0YXRfc3RhdGVtZW50 cy9wZ19zdGF0X3N0YXRlbWVudHMuYyBiL2NvbnRyaWIvcGdfc3RhdF9zdGF0ZW1lbnRzL3Bn X3N0YXRfc3RhdGVtZW50cy5jCmluZGV4IGJlYmY4MTM0ZWIuLmE4ZTkxZjk0NjkgMTAwNjQ0 Ci0tLSBhL2NvbnRyaWIvcGdfc3RhdF9zdGF0ZW1lbnRzL3BnX3N0YXRfc3RhdGVtZW50cy5j CisrKyBiL2NvbnRyaWIvcGdfc3RhdF9zdGF0ZW1lbnRzL3BnX3N0YXRfc3RhdGVtZW50cy5j CkBAIC01MCw2ICs1MCw3IEBACiAjaW5jbHVkZSAiYWNjZXNzL3BhcmFsbGVsLmgiCiAjaW5j bHVkZSAiY2F0YWxvZy9wZ19hdXRoaWQuaCIKICNpbmNsdWRlICJjb21tb24vaW50LmgiCisj aW5jbHVkZSAiY29tbW9uL3BnX3BybmcuaCIKICNpbmNsdWRlICJleGVjdXRvci9pbnN0cnVt ZW50LmgiCiAjaW5jbHVkZSAiZnVuY2FwaS5oIgogI2luY2x1ZGUgImppdC9qaXQuaCIKQEAg LTI5NCwxMiArMjk1LDE2IEBAIHN0YXRpYyBib29sIHBnc3NfdHJhY2tfdXRpbGl0eSA9IHRy dWU7CS8qIHdoZXRoZXIgdG8gdHJhY2sgdXRpbGl0eSBjb21tYW5kcyAqLwogc3RhdGljIGJv b2wgcGdzc190cmFja19wbGFubmluZyA9IGZhbHNlOwkvKiB3aGV0aGVyIHRvIHRyYWNrIHBs YW5uaW5nCiAJCQkJCQkJCQkJCSAqIGR1cmF0aW9uICovCiBzdGF0aWMgYm9vbCBwZ3NzX3Nh dmUgPSB0cnVlOwkvKiB3aGV0aGVyIHRvIHNhdmUgc3RhdHMgYWNyb3NzIHNodXRkb3duICov CitzdGF0aWMgZG91YmxlIHBnc3Nfc2FtcGxlX3JhdGUgPSAxOwogCisvKiBJcyB0aGUgY3Vy cmVudCB0b3AtbGV2ZWwgcXVlcnkgdG8gYmUgc2FtcGxlZD8gKi8KK3N0YXRpYyBib29sIGN1 cnJlbnRfcXVlcnlfc2FtcGxlZCA9IGZhbHNlOwogCiAjZGVmaW5lIHBnc3NfZW5hYmxlZChs ZXZlbCkgXAogCSghSXNQYXJhbGxlbFdvcmtlcigpICYmIFwKIAkocGdzc190cmFjayA9PSBQ R1NTX1RSQUNLX0FMTCB8fCBcCi0JKHBnc3NfdHJhY2sgPT0gUEdTU19UUkFDS19UT1AgJiYg KGxldmVsKSA9PSAwKSkpCisJKHBnc3NfdHJhY2sgPT0gUEdTU19UUkFDS19UT1AgJiYgKGxl dmVsKSA9PSAwKSkgJiYgXAorCWN1cnJlbnRfcXVlcnlfc2FtcGxlZCkKIAogI2RlZmluZSBy ZWNvcmRfZ2NfcXRleHRzKCkgXAogCWRvIHsgXApAQCAtNDE0LDYgKzQxOSwxOSBAQCBfUEdf aW5pdCh2b2lkKQogCQkJCQkJCU5VTEwsCiAJCQkJCQkJTlVMTCk7CiAKKwlEZWZpbmVDdXN0 b21SZWFsVmFyaWFibGUoInBnX3N0YXRfc3RhdGVtZW50cy5zYW1wbGVfcmF0ZSIsCisJCQkJ CQkJICJGcmFjdGlvbiBvZiBxdWVyaWVzIHRvIHRyYWNrLiIsCisJCQkJCQkJIE5VTEwsCisJ CQkJCQkJICZwZ3NzX3NhbXBsZV9yYXRlLAorCQkJCQkJCSAxLjAsCisJCQkJCQkJIDAuMCwK KwkJCQkJCQkgMS4wLAorCQkJCQkJCSBQR0NfU1VTRVQsCisJCQkJCQkJIDAsCisJCQkJCQkJ IE5VTEwsCisJCQkJCQkJIE5VTEwsCisJCQkJCQkJIE5VTEwpOworCiAJRGVmaW5lQ3VzdG9t RW51bVZhcmlhYmxlKCJwZ19zdGF0X3N0YXRlbWVudHMudHJhY2siLAogCQkJCQkJCSAiU2Vs ZWN0cyB3aGljaCBzdGF0ZW1lbnRzIGFyZSB0cmFja2VkIGJ5IHBnX3N0YXRfc3RhdGVtZW50 cy4iLAogCQkJCQkJCSBOVUxMLApAQCAtODM1LDYgKzg1Myw5IEBAIHBnc3NfcG9zdF9wYXJz ZV9hbmFseXplKFBhcnNlU3RhdGUgKnBzdGF0ZSwgUXVlcnkgKnF1ZXJ5LCBKdW1ibGVTdGF0 ZSAqanN0YXRlKQogCWlmIChwcmV2X3Bvc3RfcGFyc2VfYW5hbHl6ZV9ob29rKQogCQlwcmV2 X3Bvc3RfcGFyc2VfYW5hbHl6ZV9ob29rKHBzdGF0ZSwgcXVlcnksIGpzdGF0ZSk7CiAKKwlp ZiAobmVzdGluZ19sZXZlbCA9PSAwKQorCQljdXJyZW50X3F1ZXJ5X3NhbXBsZWQgPSAocGdf cHJuZ19kb3VibGUoJnBnX2dsb2JhbF9wcm5nX3N0YXRlKSA8IHBnc3Nfc2FtcGxlX3JhdGUp OworCiAJLyogU2FmZXR5IGNoZWNrLi4uICovCiAJaWYgKCFwZ3NzIHx8ICFwZ3NzX2hhc2gg fHwgIXBnc3NfZW5hYmxlZChuZXN0aW5nX2xldmVsKSkKIAkJcmV0dXJuOwpkaWZmIC0tZ2l0 IGEvZG9jL3NyYy9zZ21sL3Bnc3RhdHN0YXRlbWVudHMuc2dtbCBiL2RvYy9zcmMvc2dtbC9w Z3N0YXRzdGF0ZW1lbnRzLnNnbWwKaW5kZXggNTAxYjQ2OGU5YS4uMzdmNjc4ODc5OCAxMDA2 NDQKLS0tIGEvZG9jL3NyYy9zZ21sL3Bnc3RhdHN0YXRlbWVudHMuc2dtbAorKysgYi9kb2Mv c3JjL3NnbWwvcGdzdGF0c3RhdGVtZW50cy5zZ21sCkBAIC05MzYsNiArOTM2LDI0IEBACiAg ICAgPC9saXN0aXRlbT4KICAgIDwvdmFybGlzdGVudHJ5PgogCisgICA8dmFybGlzdGVudHJ5 PgorICAgIDx0ZXJtPgorICAgICA8dmFybmFtZT5wZ19zdGF0X3N0YXRlbWVudHMuc2FtcGxl X3JhdGU8L3Zhcm5hbWU+ICg8dHlwZT5yZWFsPC90eXBlPikKKyAgICAgPGluZGV4dGVybT4K KyAgICAgIDxwcmltYXJ5Pjx2YXJuYW1lPnBnX3N0YXRfc3RhdGVtZW50cy5zYW1wbGVfcmF0 ZTwvdmFybmFtZT4gY29uZmlndXJhdGlvbiBwYXJhbWV0ZXI8L3ByaW1hcnk+CisgICAgIDwv aW5kZXh0ZXJtPgorICAgIDwvdGVybT4KKworICAgIDxsaXN0aXRlbT4KKyAgICAgPHBhcmE+ CisgICAgICA8dmFybmFtZT5wZ19zdGF0X3N0YXRlbWVudHMuc2FtcGxlX3JhdGU8L3Zhcm5h bWU+IGNhdXNlcyBwZ19zdGF0X3N0YXRlbWVudHMgdG8gb25seQorICAgICAgdHJhY2sgYSBm cmFjdGlvbiBvZiB0aGUgc3RhdGVtZW50cyBpbiBlYWNoIHNlc3Npb24uIFRoZSBkZWZhdWx0 IGlzIDEsCisgICAgICBtZWFuaW5nIHRyYWNrIGFsbCB0aGUgcXVlcmllcy4gSW4gY2FzZSBv ZiBuZXN0ZWQgc3RhdGVtZW50cywKKyAgICAgIGVpdGhlciBhbGwgd2lsbCBiZSB0cmFja2Vk IG9yIG5vbmUuIE9ubHkgc3VwZXJ1c2VycyBjYW4gY2hhbmdlIHRoaXMgc2V0dGluZy4KKyAg ICAgPC9wYXJhPgorICAgIDwvbGlzdGl0ZW0+CisgICA8L3Zhcmxpc3RlbnRyeT4KKwogICAg PHZhcmxpc3RlbnRyeT4KICAgICA8dGVybT4KICAgICAgPHZhcm5hbWU+cGdfc3RhdF9zdGF0 ZW1lbnRzLnNhdmU8L3Zhcm5hbWU+ICg8dHlwZT5ib29sZWFuPC90eXBlPikKLS0gCjIuMzQu MQoK --------------V1jPju41ETIr5CY201cbGsvS--