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 1o45le-0006R8-Hq for pgsql-hackers@arkaria.postgresql.org; Wed, 22 Jun 2022 19:11:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1o45lc-0006ee-Lw for pgsql-hackers@arkaria.postgresql.org; Wed, 22 Jun 2022 19:11:56 +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 1o45lc-0006eV-9f for pgsql-hackers@lists.postgresql.org; Wed, 22 Jun 2022 19:11:56 +0000 Received: from mail-ed1-x534.google.com ([2a00:1450:4864:20::534]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1o45lX-0000VV-LP for pgsql-hackers@postgresql.org; Wed, 22 Jun 2022 19:11:55 +0000 Received: by mail-ed1-x534.google.com with SMTP id c13so20789916eds.10 for ; Wed, 22 Jun 2022 12:11:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=x54KCLnrSkNwnQhQBoYoP5s4qrfICtzYFffFdZAJUpo=; b=bUDtrv9DSZXAcZlarPlzOJyJ1bHUg4YLuTPOlREPFHqO1l1uVgE8V2ZZJwIxhjvM4g mKcyTf0NU/cHfBXebIWsODGZCDA3esgQhVpYBZpqBtcWyqJVSLDz5C0/52drNBb+1v5o q8VUPvyTOUxvVV5d23ETkXZ/88OW3wzFavsIgANeEaonxIxvMJ1Zy02CwPGnLbD5XA+d 8X0iCEdcGv7pG7wN/p2HpPNnpTS4XD41TvBviVxSNRGgtcN+BanXmWh8Wggu+84CIvpZ Zu5DCTJ9rCoius0I96QmC/utmqwnBwqvp4CnBhlMpx4ekyau0nFIJGWWNXoFUmw/ZT24 L0wA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=x54KCLnrSkNwnQhQBoYoP5s4qrfICtzYFffFdZAJUpo=; b=6zx8oYIvXJiaFulUOHHXmLpTM49Aozg4yiBYu6/xQ9R/AU4KcBMHjuQYAHbhhQePrP s7kI/VxrcqdJjcMtiq29Wb3TxOQeyrYUCA+LVCY37SNLxjboIQ2aS9lQeln09lc5UZg9 NhGsgNvU+XCQu/RujvFgT0QryskOUiYVuwirRQydj5sq8t1IdP4Ja47nwXkq+8qDexVk NXz/cJQ5dBvoMuDentaRRnHdjJvH4t4/IWKtsR1PogVCrq6ntRzCsp6OzhsMD112Pos+ 0sGC38RUr49T3roegKGYx1BKojyV8q9HwghvoDsKS9jp9MqN60yRhWxU8DFQq/htgokn OUXQ== X-Gm-Message-State: AJIora81UPehehcG8bwpyKQ28a8qR8OtXfcytYTlPz6DKrw18BaZNf+B SNBhYqPazsXg2EUBgNxRaW9N1x1Zx/puEcXcs1TLD2G9hPOWJw== X-Google-Smtp-Source: AGRyM1vdsbTx4B/ssHV+6y6MVBIKrTpDFLT/Gf+oXPAwo3yoI0HpNDGKR7wLeYpsyb/lhm0M5+/myfRC458eqXdD6UI= X-Received: by 2002:a05:6402:3891:b0:435:5262:3521 with SMTP id fd17-20020a056402389100b0043552623521mr5752556edb.320.1655925109392; Wed, 22 Jun 2022 12:11:49 -0700 (PDT) MIME-Version: 1.0 References: <603c8f070905281830g2e5419c4xad2946d149e21f9d@mail.gmail.com> <4A1F4FD5.7060204@timbira.com> <4A249CE9.6050708@cheapcomplexdevices.com> <1243950060.23910.58.camel@ebony.2ndQuadrant> <11926.1243953507@sss.pgh.pa.us> In-Reply-To: <11926.1243953507@sss.pgh.pa.us> From: Ibrar Ahmed Date: Thu, 23 Jun 2022 00:11:12 +0500 Message-ID: Subject: Re: explain analyze rows=%.0f To: "pgsql-hackers@postgresql.org" Cc: Robert Haas , Simon Riggs , Ron Mayer , Euler Taveira de Oliveira , Tom Lane Content-Type: multipart/mixed; boundary="000000000000ebddf805e20e1b7d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ebddf805e20e1b7d Content-Type: multipart/alternative; boundary="000000000000ebddf605e20e1b7b" --000000000000ebddf605e20e1b7b Content-Type: text/plain; charset="UTF-8" On Thu, Jun 23, 2022 at 12:01 AM Tom Lane wrote: > Robert Haas writes: > > On Jun 2, 2009, at 9:41 AM, Simon Riggs wrote: > >> You're right that the number of significant digits already exceeds the > >> true accuracy of the computation. I think what Robert wants to see is > >> the exact value used in the calc, so the estimates can be checked more > >> thoroughly than is currently possible. > > > Bingo. > > Uh, the planner's estimate *is* an integer. What was under discussion > (I thought) was showing some fractional digits in the case where EXPLAIN > ANALYZE is outputting a measured row count that is an average over > multiple loops, and therefore isn't necessarily an integer. In that > case the measured value can be considered arbitrarily precise --- though > I think in practice one or two fractional digits would be plenty. > > regards, tom lane > > > Hi, I was looking at the TODO list and found that the issue requires a quick fix. Attached is a patch which shows output like this. It shows the fraction digits in case of loops > 1 postgres=# explain analyze select * from foo; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Seq Scan on foo (cost=0.00..64414.79 rows=2326379 width=8) (actual time=0.025..277.096 rows=2344671 loops=1 Planning Time: 0.516 ms Execution Time: 356.993 ms (3 rows) postgres=# explain analyze select * from foo where b = (select c from bar where c = 1); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Seq Scan on foo (cost=8094.37..78325.11 rows=2326379 width=8) (actual time=72.352..519.159 rows=2344671 loops=1 Filter: (b = $1) InitPlan 1 (returns $1) -> Gather (cost=1000.00..8094.37 rows=1 width=4) (actual time=0.872..72.434 rows=1 loops=1 Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on bar (cost=0.00..7094.27 rows=1 width=4) (actual time=41.931..65.382 rows=0.33 loops=3) Filter: (c = 1) Rows Removed by Filter: 245457 Planning Time: 0.277 ms Execution Time: 597.795 ms (11 rows) -- Ibrar Ahmed --000000000000ebddf605e20e1b7b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Jun 23, 2022 at 12:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
R= obert Haas <r= obertmhaas@gmail.com> writes:
> On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon@2ndQuadrant.com> = wrote:
>> You're right that the number of significant digits already exc= eeds the
>> true accuracy of the computation. I think what Robert wants to see= is
>> the exact value used in the calc, so the estimates can be checked = more
>> thoroughly than is currently possible.

> Bingo.

Uh, the planner's estimate *is* an integer.=C2=A0 What was under discus= sion
(I thought) was showing some fractional digits in the case where EXPLAIN ANALYZE is outputting a measured row count that is an average over
multiple loops, and therefore isn't necessarily an integer.=C2=A0 In th= at
case the measured value can be considered arbitrarily precise --- though I think in practice one or two fractional digits would be plenty.

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 regards, tom lane


Hi,
I was looking at the TODO list and foun= d that the issue requires
a quick fix. Attached is a patch which shows outp= ut like this. It shows the
fraction digits in case of loops > 1

postgres=3D# explain analyze select * from foo= ; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ---------------------------------------------------------------------------= ----------------------------------- =C2=A0Seq Scan on foo=C2=A0 (cost=3D0.00..64414.79 rows=3D2326379 width=3D8= ) (actual time=3D0.025..277.096 rows=3D2344671 loops=3D1 =C2=A0Planning Time: 0.516 ms =C2=A0Execution Time: 356.993 ms (3 rows) =C2=A0 postgres=3D# explain analyze select * from foo where b =3D (select c from b= ar where c =3D 1); =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ---------------------------------------------------------------------------= ------------------------------------------------- =C2=A0Seq Scan on foo=C2=A0 (cost=3D8094.37..78325.11 rows=3D2326379 width= =3D8) (actual time=3D72.352..519.159 rows=3D2344671 loops=3D1 =C2=A0=C2=A0 Filter: (b =3D $1) =C2=A0=C2=A0 InitPlan 1 (returns $1) =C2=A0=C2=A0 =C2=A0 ->=C2=A0 Gather=C2=A0 (cost=3D1000.00..8094.37 rows= =3D1 width=3D4) (actual time=3D0.872..72.434 rows=3D1 loops=3D1 =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Workers Planned: 2 =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Workers Launched: 2 =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Parallel Seq Scan on b= ar=C2=A0 (cost=3D0.00..7094.27 rows=3D1 width=3D4) (actual time=3D41.931..6= 5.382 rows=3D0.33 loops=3D3) =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: (c = =3D 1) =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Rows Removed = by Filter: 245457 =C2=A0Planning Time: 0.277 ms =C2=A0Execution Time: 597.795 ms (11 rows)


--
Ibrar Ahmed
--000000000000ebddf605e20e1b7b-- --000000000000ebddf805e20e1b7d Content-Type: application/octet-stream; name="explain_float_row.patch" Content-Disposition: attachment; filename="explain_float_row.patch" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_l4pz0m0o0 ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL2V4cGxhaW4uYyBiL3NyYy9iYWNrZW5k L2NvbW1hbmRzL2V4cGxhaW4uYwppbmRleCA1ZDFmNzA4OWRhLi45NTRmNjRiODAyIDEwMDY0NAot LS0gYS9zcmMvYmFja2VuZC9jb21tYW5kcy9leHBsYWluLmMKKysrIGIvc3JjL2JhY2tlbmQvY29t bWFuZHMvZXhwbGFpbi5jCkBAIC0xNjI0LDEzICsxNjI0LDIxIEBAIEV4cGxhaW5Ob2RlKFBsYW5T dGF0ZSAqcGxhbnN0YXRlLCBMaXN0ICphbmNlc3RvcnMsCiAJCWlmIChlcy0+Zm9ybWF0ID09IEVY UExBSU5fRk9STUFUX1RFWFQpCiAJCXsKIAkJCWlmIChlcy0+dGltaW5nKQorCQkJeworCQkJCWFw cGVuZFN0cmluZ0luZm8oZXMtPnN0ciwgIiAoYWN0dWFsIHRpbWU9JS4zZi4uJS4zZiIsCisJCQkJ CQkJCQlzdGFydHVwX21zLCB0b3RhbF9tcyk7CiAJCQkJYXBwZW5kU3RyaW5nSW5mbyhlcy0+c3Ry LAotCQkJCQkJCQkgIiAoYWN0dWFsIHRpbWU9JS4zZi4uJS4zZiByb3dzPSUuMGYgbG9vcHM9JS4w ZikiLAotCQkJCQkJCQkgc3RhcnR1cF9tcywgdG90YWxfbXMsIHJvd3MsIG5sb29wcyk7CisJCQkJ CQkJCQkJbmxvb3BzID09IDEgPyAiIHJvd3M9JS4wZiBsb29wcz0lLjBmIiA6CisJCQkJCQkJCQkJ IiByb3dzPSUuMmYgbG9vcHM9JS4wZikiLAorCQkJCQkJCQkJCXJvd3MsIG5sb29wcyk7CisJCQl9 CiAJCQllbHNlCisJCQl7CiAJCQkJYXBwZW5kU3RyaW5nSW5mbyhlcy0+c3RyLAotCQkJCQkJCQkg IiAoYWN0dWFsIHJvd3M9JS4wZiBsb29wcz0lLjBmKSIsCi0JCQkJCQkJCSByb3dzLCBubG9vcHMp OworCQkJCQkJCQlubG9vcHMgPT0gMSA/CisJCQkJCQkJCSIgKGFjdHVhbCByb3dzPSUuMGYgbG9v cHM9JS4wZiI6ICIgcm93cz0lLjJmIGxvb3BzPSUuMGYpIiwKKwkJCQkJCQkJcm93cywgbmxvb3Bz KTsKKwkJCX0KIAkJfQogCQllbHNlCiAJCXsKQEAgLTE2NDEsNyArMTY0OSw3IEBAIEV4cGxhaW5O b2RlKFBsYW5TdGF0ZSAqcGxhbnN0YXRlLCBMaXN0ICphbmNlc3RvcnMsCiAJCQkJRXhwbGFpblBy b3BlcnR5RmxvYXQoIkFjdHVhbCBUb3RhbCBUaW1lIiwgIm1zIiwgdG90YWxfbXMsCiAJCQkJCQkJ CQkgMywgZXMpOwogCQkJfQotCQkJRXhwbGFpblByb3BlcnR5RmxvYXQoIkFjdHVhbCBSb3dzIiwg TlVMTCwgcm93cywgMCwgZXMpOworCQkJRXhwbGFpblByb3BlcnR5RmxvYXQoIkFjdHVhbCBSb3dz IiwgTlVMTCwgcm93cywgbmxvb3BzID09IDEgPyAwIDogMiwgZXMpOwogCQkJRXhwbGFpblByb3Bl cnR5RmxvYXQoIkFjdHVhbCBMb29wcyIsIE5VTEwsIG5sb29wcywgMCwgZXMpOwogCQl9CiAJfQpA QCAtMTY5MCwxMyArMTY5OCwyMCBAQCBFeHBsYWluTm9kZShQbGFuU3RhdGUgKnBsYW5zdGF0ZSwg TGlzdCAqYW5jZXN0b3JzLAogCQkJewogCQkJCUV4cGxhaW5JbmRlbnRUZXh0KGVzKTsKIAkJCQlp ZiAoZXMtPnRpbWluZykKLQkJCQkJYXBwZW5kU3RyaW5nSW5mbyhlcy0+c3RyLAotCQkJCQkJCQkJ ICJhY3R1YWwgdGltZT0lLjNmLi4lLjNmIHJvd3M9JS4wZiBsb29wcz0lLjBmXG4iLAotCQkJCQkJ CQkJIHN0YXJ0dXBfbXMsIHRvdGFsX21zLCByb3dzLCBubG9vcHMpOworCQkJCXsKKwkJCQkJYXBw ZW5kU3RyaW5nSW5mbyhlcy0+c3RyLCAiYWN0dWFsIHRpbWU9JS4zZi4uJS4zZiIsCisJCQkJCQkJ CQkJc3RhcnR1cF9tcywgdG90YWxfbXMpOworCQkJCQkJCQkJCWFwcGVuZFN0cmluZ0luZm8oZXMt PnN0ciwKKwkJCQkJCQkJCQlubG9vcHMgPT0gMSA/ICIgcm93cz0lLjBmIGxvb3BzPSUuMGYiIDoK KwkJCQkJCQkJCQkiIHJvd3M9JS4yZiBsb29wcz0lLjBmIiwKKwkJCQkJCQkJCQlyb3dzLCBubG9v cHMpOworCQkJCX0KIAkJCQllbHNlCi0JCQkJCWFwcGVuZFN0cmluZ0luZm8oZXMtPnN0ciwKLQkJ CQkJCQkJCSAiYWN0dWFsIHJvd3M9JS4wZiBsb29wcz0lLjBmXG4iLAotCQkJCQkJCQkJIHJvd3Ms IG5sb29wcyk7CisJCQkJeworCQkJCQlhcHBlbmRTdHJpbmdJbmZvKGVzLT5zdHIsIG5sb29wcyA9 PSAxID8KKwkJCQkJCQkJCQkiYWN0dWFsIHJvd3M9JS4wZiBsb29wcz0lLjBmIjogIiByb3dzPSUu MmYgbG9vcHM9JS4wZiIsCisJCQkJCQkJCQkJcm93cywgbmxvb3BzKTsKKwkJCQl9CiAJCQl9CiAJ CQllbHNlCiAJCQl7CkBAIC0xNzA3LDcgKzE3MjIsNyBAQCBFeHBsYWluTm9kZShQbGFuU3RhdGUg KnBsYW5zdGF0ZSwgTGlzdCAqYW5jZXN0b3JzLAogCQkJCQlFeHBsYWluUHJvcGVydHlGbG9hdCgi QWN0dWFsIFRvdGFsIFRpbWUiLCAibXMiLAogCQkJCQkJCQkJCSB0b3RhbF9tcywgMywgZXMpOwog CQkJCX0KLQkJCQlFeHBsYWluUHJvcGVydHlGbG9hdCgiQWN0dWFsIFJvd3MiLCBOVUxMLCByb3dz LCAwLCBlcyk7CisJCQkJRXhwbGFpblByb3BlcnR5RmxvYXQoIkFjdHVhbCBSb3dzIiwgTlVMTCwg cm93cywgIG5sb29wcyA9PSAxID8gMCA6IDIsIGVzKTsKIAkJCQlFeHBsYWluUHJvcGVydHlGbG9h dCgiQWN0dWFsIExvb3BzIiwgTlVMTCwgbmxvb3BzLCAwLCBlcyk7CiAJCQl9CiAKZGlmZiAtLWdp dCBhL3NyYy9iYWNrZW5kL25vZGVzL291dGZ1bmNzLmMgYi9zcmMvYmFja2VuZC9ub2Rlcy9vdXRm dW5jcy5jCmluZGV4IGNlMTI5MTU1OTIuLmJhZWNjNDBlMGMgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNr ZW5kL25vZGVzL291dGZ1bmNzLmMKKysrIGIvc3JjL2JhY2tlbmQvbm9kZXMvb3V0ZnVuY3MuYwpA QCAtMTkzNyw3ICsxOTM3LDcgQEAgX291dFBhdGhJbmZvKFN0cmluZ0luZm8gc3RyLCBjb25zdCBQ YXRoICpub2RlKQogCVdSSVRFX0JPT0xfRklFTEQocGFyYWxsZWxfYXdhcmUpOwogCVdSSVRFX0JP T0xfRklFTEQocGFyYWxsZWxfc2FmZSk7CiAJV1JJVEVfSU5UX0ZJRUxEKHBhcmFsbGVsX3dvcmtl cnMpOwotCVdSSVRFX0ZMT0FUX0ZJRUxEKHJvd3MsICIlLjBmIik7CisJV1JJVEVfRkxPQVRfRklF TEQocm93cywgIiUuMmYiKTsKIAlXUklURV9GTE9BVF9GSUVMRChzdGFydHVwX2Nvc3QsICIlLjJm Iik7CiAJV1JJVEVfRkxPQVRfRklFTEQodG90YWxfY29zdCwgIiUuMmYiKTsKIAlXUklURV9OT0RF X0ZJRUxEKHBhdGhrZXlzKTsK --000000000000ebddf805e20e1b7d--