Content-Type: multipart/mixed; boundary="------------vrX5039H7WQXZPq90ejTHa40"
Message-ID: <aaf18433-82d3-417b-a1f5-12e62fedffb8@tantorlabs.com>
Date: Thu, 5 Sep 2024 19:05:06 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
To: pgsql-hackers <pgsql-hackers@postgresql.org>
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
Subject: Adding NOTICE for differences between estimated and actual rows
Archived-At: <https://www.postgresql.org/message-id/aaf18433-82d3-417b-a1f5-12e62fedffb8%40tantorlabs.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------vrX5039H7WQXZPq90ejTHa40
Content-Type: multipart/alternative;
 boundary="------------rCVLS5z60Rj4jM53QkzU0hxM"

--------------rCVLS5z60Rj4jM53QkzU0hxM
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hi hackers,

It's quite common that poor query performance can be attributed to 
inaccurate row estimations by the planner. To make it easier to detect 
these discrepancies, rather than scrutinizing the estimates manually, it 
would be helpful to output a dedicated |NOTICE| message.

In the current patch, I've introduced a new GUC parameter called 
'estimated_rows_scale_factor'. If the ratio of the estimated rows to the 
actual rows is less than this factor, or if the estimated rows 
significantly exceed the actual rows (when the ratio is greater than 
this factor), a NOTICE message will be printed. The message reads: 
"Estimated rows (%.0f) less(greater) than actual rows (%.0f)."


Here is an example:

CREATE TABLE t(a int, b int);
INSERT INTO t SELECT x/10, x FROM generate_series(1,10000000) g(x);
ANALYZE;

SET estimated_rows_scale_factor = 0.9;

EXPLAIN ANALYZE SELECT * FROM t WHERE a > 10 AND b <= 200;
NOTICE:  Estimated rows (1000) greater than actual rows (91).
                                                      QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
  Gather  (cost=1000.00..107848.00 rows=1000 width=8) (actual 
time=0.446..122.476 rows=91 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    ->  Parallel Seq Scan on t  (cost=0.00..106748.00 rows=417 width=8) 
(actual time=77.657..118.000 rows=30 loops=3)
          Filter: ((a > 10) AND (b <= 200))
          Rows Removed by Filter: 3333303
  Planning Time: 0.097 ms
  Execution Time: 122.502 ms
(8 rows)

EXPLAIN ANALYZE SELECT * FROM t WHERE a = 10 AND b <= 200;
NOTICE:  Estimated rows (1) less than actual rows (10).
                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------
  Gather  (cost=1000.00..107748.10 rows=1 width=8) (actual 
time=0.280..104.752 rows=10 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    ->  Parallel Seq Scan on t  (cost=0.00..106748.00 rows=1 width=8) 
(actual time=66.493..101.102 rows=3 loops=3)
          Filter: ((b <= 200) AND (a = 10))
          Rows Removed by Filter: 3333330
  Planning Time: 0.129 ms
  Execution Time: 104.768 ms
(8 rows)


If you have any suggestions regarding the wording of the message, its 
placement, or if you'd like to see a different criterion used, I would 
greatly appreciate your feedback.

Looking forward to your thoughts and suggestions.

-- 
Regards,
Ilia Evdokimov,
Tantor Labs LCC.

--------------rCVLS5z60Rj4jM53QkzU0hxM
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi hackers,</p>
    <p>It's quite common that poor query performance can be attributed
      to inaccurate row estimations by the planner. To make it easier to
      detect these discrepancies, rather than scrutinizing the estimates
      manually, it would be helpful to output a dedicated <code>NOTICE</code>
      message.</p>
    <p>In the current patch, I've introduced a new GUC parameter called
      'estimated_rows_scale_factor'. If the ratio of the estimated rows
      to the actual rows is less than this factor, or if the estimated
      rows significantly exceed the actual rows (when the ratio is
      greater than this factor), a NOTICE message will be printed. The
      message reads: "Estimated rows (%.0f) less(greater) than actual
      rows (%.0f)."</p>
    <p><br>
    </p>
    <p>Here is an example:</p>
    <p>CREATE TABLE t(a int, b int);<br>
      INSERT INTO t SELECT x/10, x FROM generate_series(1,10000000)
      g(x);<br>
      ANALYZE;<br>
      <br>
      SET estimated_rows_scale_factor = 0.9;<br>
      <br>
      EXPLAIN ANALYZE SELECT * FROM t WHERE a &gt; 10 AND b &lt;= 200;<br>
      NOTICE:  Estimated rows (1000) greater than actual rows (91).<br>
                                                           QUERY
      PLAN                                                      <br>
---------------------------------------------------------------------------------------------------------------------<br>
       Gather  (cost=1000.00..107848.00 rows=1000 width=8) (actual
      time=0.446..122.476 rows=91 loops=1)<br>
         Workers Planned: 2<br>
         Workers Launched: 2<br>
         -&gt;  Parallel Seq Scan on t  (cost=0.00..106748.00 rows=417
      width=8) (actual time=77.657..118.000 rows=30 loops=3)<br>
               Filter: ((a &gt; 10) AND (b &lt;= 200))<br>
               Rows Removed by Filter: 3333303<br>
       Planning Time: 0.097 ms<br>
       Execution Time: 122.502 ms<br>
      (8 rows)<br>
      <br>
      EXPLAIN ANALYZE SELECT * FROM t WHERE a = 10 AND b &lt;= 200;<br>
      NOTICE:  Estimated rows (1) less than actual rows (10).<br>
                                                          QUERY
      PLAN                                                    <br>
------------------------------------------------------------------------------------------------------------------<br>
       Gather  (cost=1000.00..107748.10 rows=1 width=8) (actual
      time=0.280..104.752 rows=10 loops=1)<br>
         Workers Planned: 2<br>
         Workers Launched: 2<br>
         -&gt;  Parallel Seq Scan on t  (cost=0.00..106748.00 rows=1
      width=8) (actual time=66.493..101.102 rows=3 loops=3)<br>
               Filter: ((b &lt;= 200) AND (a = 10))<br>
               Rows Removed by Filter: 3333330<br>
       Planning Time: 0.129 ms<br>
       Execution Time: 104.768 ms<br>
      (8 rows)</p>
    <p><br>
    </p>
    <p>If you have any suggestions regarding the wording of the message,
      its placement, or if you'd like to see a different criterion used,
      I would greatly appreciate your feedback.</p>
    <p>Looking forward to your thoughts and suggestions.</p>
    <pre class="moz-signature" cols="72">-- 
Regards,
Ilia Evdokimov,
Tantor Labs LCC.</pre>
  </body>
</html>

--------------rCVLS5z60Rj4jM53QkzU0hxM--
--------------vrX5039H7WQXZPq90ejTHa40
Content-Type: text/x-patch; charset=UTF-8;
 name="v1-0001-Add-message-when-estimated-rows-differ-from-actual.patch"
Content-Disposition: attachment;
 filename*0="v1-0001-Add-message-when-estimated-rows-differ-from-actual.p";
 filename*1="atch"
Content-Transfer-Encoding: base64

RnJvbSA4ZTAzOGYxNmQ0NWQ1ZjhjMzljNzIwNGZjNjYxZWQ0YjdmNTA0YTYzIE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBJbGlhIEV2ZG9raW1vdiA8aWx5YS5ldmRva2ltb3ZA
dGFudG9ybGFicy5jb20+CkRhdGU6IFRodSwgNSBTZXAgMjAyNCAxODoxNTowMSArMDMwMApT
dWJqZWN0OiBbUEFUQ0ggdjFdIEFkZCBtZXNzYWdlIHdoZW4gZXN0aW1hdGVkIHJvd3MgZGlm
ZmVyIGZyb20gYWN0dWFsIHJvd3MKCklmIHRoZSByYXRpbyBvZiBlc3RpbWF0ZWQgcm93cyB0
byBhY3R1YWwgcm93cyBpcyBsZXNzCnRoYW4gdGhlICdlc3RpbWF0ZWRfcm93c19zY2FsZV9m
YWN0b3InLCBvciBpZiB0aGUgZXN0aW1hdGVkIHJvd3MKZXhjZWVkIHRoZSBhY3R1YWwgcm93
cywgYSBOT1RJQ0UgbWVzc2FnZSB3aWxsIGJlIHByaW50ZWQuClRoaXMgaGVscHMgdG8gaWRl
bnRpZnkgZGlzY3JlcGFuY2llcyBiZXR3ZWVuIHRoZSBwbGFubmVyJ3MgZXN0aW1hdGlvbnMK
YW5kIHRoZSBhY3R1YWwgcXVlcnkgZXhlY3V0aW9uLCBlbmFibGluZyBiZXR0ZXIgYW5hbHlz
aXMgYW5kIG9wdGltaXphdGlvbi4KLS0tCiBzcmMvYmFja2VuZC9jb21tYW5kcy9leHBsYWlu
LmMgICAgICB8IDMxICsrKysrKysrKysrKysrKysrKysrKysrKysrKysrCiBzcmMvYmFja2Vu
ZC91dGlscy9taXNjL2d1Y190YWJsZXMuYyB8IDExICsrKysrKysrKysKIHNyYy9pbmNsdWRl
L3V0aWxzL2d1Yy5oICAgICAgICAgICAgIHwgIDEgKwogMyBmaWxlcyBjaGFuZ2VkLCA0MyBp
bnNlcnRpb25zKCspCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvY29tbWFuZHMvZXhwbGFp
bi5jIGIvc3JjL2JhY2tlbmQvY29tbWFuZHMvZXhwbGFpbi5jCmluZGV4IDExZGY0YTA0ZDQu
LmRhNDM1Y2ZlZTEgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL2V4cGxhaW4u
YworKysgYi9zcmMvYmFja2VuZC9jb21tYW5kcy9leHBsYWluLmMKQEAgLTQxOSw2ICs0MTks
MzUgQEAgRXhwbGFpblJlc3VsdERlc2MoRXhwbGFpblN0bXQgKnN0bXQpCiAJcmV0dXJuIHR1
cGRlc2M7CiB9CiAKKy8qCisgKiBOb3RpY2Ugd2hldGhlciBlc3RpbWF0ZWQgcm93cyBpcyBi
YWQuCisgKi8KK3N0YXRpYyB2b2lkCitFeHBsYWluUHJpbnRFc3RpbWF0ZWRSb3dzQXR0ZW50
aW9uKEV4cGxhaW5TdGF0ZSAqZXMsIFF1ZXJ5RGVzYyAqcXVlcnlEZXNjKQoreworCWRvdWJs
ZSAgICAgcm93czsKKwlkb3VibGUgICAgIG5sb29wczsKKwlQbGFuU3RhdGUgKnBsYW5zdGF0
ZSA9IHF1ZXJ5RGVzYy0+cGxhbnN0YXRlOworCisJaWYgKGVzLT5hbmFseXplICYmCisJCXBs
YW5zdGF0ZS0+aW5zdHJ1bWVudCAmJiBwbGFuc3RhdGUtPmluc3RydW1lbnQtPm5sb29wcyA+
IDApCisJeworCQlubG9vcHMgPSBwbGFuc3RhdGUtPmluc3RydW1lbnQtPm5sb29wczsKKwkJ
cm93cyAgID0gcGxhbnN0YXRlLT5pbnN0cnVtZW50LT5udHVwbGVzIC8gbmxvb3BzOworCisJ
CWlmKHF1ZXJ5RGVzYy0+cGxhbnN0YXRlLT5wbGFuLT5wbGFuX3Jvd3MgLyByb3dzIDwgZXN0
aW1hdGVkX3Jvd3Nfc2NhbGVfZmFjdG9yKQorCQkJZXJlcG9ydChOT1RJQ0UsCisJCQkJCShl
cnJtc2coIkVzdGltYXRlZCByb3dzICglLjBmKSBsZXNzIHRoYW4gYWN0dWFsIHJvd3MgKCUu
MGYpLiIsCisJCQkJCQkJCXF1ZXJ5RGVzYy0+cGxhbnN0YXRlLT5wbGFuLT5wbGFuX3Jvd3Ms
CisJCQkJCQkJCXJvd3MpLCBlcnJoaWRlc3RtdCh0cnVlKSkpOworCQllbHNlIGlmIChyb3dz
IC8gcXVlcnlEZXNjLT5wbGFuc3RhdGUtPnBsYW4tPnBsYW5fcm93cyA8IGVzdGltYXRlZF9y
b3dzX3NjYWxlX2ZhY3RvcikKKwkJCWVyZXBvcnQoTk9USUNFLAorCQkJCQkoZXJybXNnKCJF
c3RpbWF0ZWQgcm93cyAoJS4wZikgZ3JlYXRlciB0aGFuIGFjdHVhbCByb3dzICglLjBmKS4i
LAorCQkJCQkJCQlxdWVyeURlc2MtPnBsYW5zdGF0ZS0+cGxhbi0+cGxhbl9yb3dzLAorCQkJ
CQkJCQlyb3dzKSwgZXJyaGlkZXN0bXQodHJ1ZSkpKTsKKwl9Cit9CisKIC8qCiAgKiBFeHBs
YWluT25lUXVlcnkgLQogICoJICBwcmludCBvdXQgdGhlIGV4ZWN1dGlvbiBwbGFuIGZvciBv
bmUgUXVlcnkKQEAgLTcyMSw2ICs3NTAsOCBAQCBFeHBsYWluT25lUGxhbihQbGFubmVkU3Rt
dCAqcGxhbm5lZHN0bXQsIEludG9DbGF1c2UgKmludG8sIEV4cGxhaW5TdGF0ZSAqZXMsCiAJ
LyogQ3JlYXRlIHRleHR1YWwgZHVtcCBvZiBwbGFuIHRyZWUgKi8KIAlFeHBsYWluUHJpbnRQ
bGFuKGVzLCBxdWVyeURlc2MpOwogCisJRXhwbGFpblByaW50RXN0aW1hdGVkUm93c0F0dGVu
dGlvbihlcywgcXVlcnlEZXNjKTsKKwogCS8qIFNob3cgYnVmZmVyIGFuZC9vciBtZW1vcnkg
dXNhZ2UgaW4gcGxhbm5pbmcgKi8KIAlpZiAocGVla19idWZmZXJfdXNhZ2UoZXMsIGJ1ZnVz
YWdlKSB8fCBtZW1fY291bnRlcnMpCiAJewpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvdXRp
bHMvbWlzYy9ndWNfdGFibGVzLmMgYi9zcmMvYmFja2VuZC91dGlscy9taXNjL2d1Y190YWJs
ZXMuYwppbmRleCA2ODYzMDlkYjU4Li5mZDA1ZmNlZTA5IDEwMDY0NAotLS0gYS9zcmMvYmFj
a2VuZC91dGlscy9taXNjL2d1Y190YWJsZXMuYworKysgYi9zcmMvYmFja2VuZC91dGlscy9t
aXNjL2d1Y190YWJsZXMuYwpAQCAtNTI4LDYgKzUyOCw3IEBAIGludAkJCWxvZ19wYXJhbWV0
ZXJfbWF4X2xlbmd0aF9vbl9lcnJvciA9IDA7CiBpbnQJCQlsb2dfdGVtcF9maWxlcyA9IC0x
OwogZG91YmxlCQlsb2dfc3RhdGVtZW50X3NhbXBsZV9yYXRlID0gMS4wOwogZG91YmxlCQls
b2dfeGFjdF9zYW1wbGVfcmF0ZSA9IDA7Citkb3VibGUgICAgICBlc3RpbWF0ZWRfcm93c19z
Y2FsZV9mYWN0b3IgPSAxLjA7CiBjaGFyCSAgICpiYWNrdHJhY2VfZnVuY3Rpb25zOwogCiBp
bnQJCQl0ZW1wX2ZpbGVfbGltaXQgPSAtMTsKQEAgLTM5OTUsNiArMzk5NiwxNiBAQCBzdHJ1
Y3QgY29uZmlnX3JlYWwgQ29uZmlndXJlTmFtZXNSZWFsW10gPQogCQlOVUxMLCBOVUxMLCBO
VUxMCiAJfSwKIAorCXsKKwkJeyJlc3RpbWF0ZWRfcm93c19zY2FsZV9mYWN0b3IiLCBQR0Nf
U1VTRVQsIExPR0dJTkdfV0hFTiwKKwkJCWdldHRleHRfbm9vcCgiRXN0aW1hdGVkIHJvd3Ms
IGFzIGEgZnJhY3Rpb24gb2YgYWN0dWFsIHJvd3MiKSwKKwkJCU5VTEwKKwkJfSwKKwkJJmVz
dGltYXRlZF9yb3dzX3NjYWxlX2ZhY3RvciwKKwkJMC4wLCAwLjAsIDEuMCwKKwkJTlVMTCwg
TlVMTCwgTlVMTAorCX0sCisKIAkvKiBFbmQtb2YtbGlzdCBtYXJrZXIgKi8KIAl7CiAJCXtO
VUxMLCAwLCAwLCBOVUxMLCBOVUxMfSwgTlVMTCwgMC4wLCAwLjAsIDAuMCwgTlVMTCwgTlVM
TCwgTlVMTApkaWZmIC0tZ2l0IGEvc3JjL2luY2x1ZGUvdXRpbHMvZ3VjLmggYi9zcmMvaW5j
bHVkZS91dGlscy9ndWMuaAppbmRleCA4NDBiMGZlNTdmLi45ZTY3NmI2OTRmIDEwMDY0NAot
LS0gYS9zcmMvaW5jbHVkZS91dGlscy9ndWMuaAorKysgYi9zcmMvaW5jbHVkZS91dGlscy9n
dWMuaApAQCAtMjczLDYgKzI3Myw3IEBAIGV4dGVybiBQR0RMTElNUE9SVCBpbnQgbG9nX21p
bl9kdXJhdGlvbl9zYW1wbGU7CiBleHRlcm4gUEdETExJTVBPUlQgaW50IGxvZ19taW5fZHVy
YXRpb25fc3RhdGVtZW50OwogZXh0ZXJuIFBHRExMSU1QT1JUIGludCBsb2dfdGVtcF9maWxl
czsKIGV4dGVybiBQR0RMTElNUE9SVCBkb3VibGUgbG9nX3N0YXRlbWVudF9zYW1wbGVfcmF0
ZTsKK2V4dGVybiBQR0RMTElNUE9SVCBkb3VibGUgZXN0aW1hdGVkX3Jvd3Nfc2NhbGVfZmFj
dG9yOwogZXh0ZXJuIFBHRExMSU1QT1JUIGRvdWJsZSBsb2dfeGFjdF9zYW1wbGVfcmF0ZTsK
IGV4dGVybiBQR0RMTElNUE9SVCBjaGFyICpiYWNrdHJhY2VfZnVuY3Rpb25zOwogCi0tIAoy
LjM0LjEKCg==

--------------vrX5039H7WQXZPq90ejTHa40--